サブクエリは、クエリ言語内の別のクエリ内で入れ子になったクエリです。 サブクエリは、 内部クエリ または 内部 SELECTとも呼ばれます。 サブクエリを含むステートメントは、通常、 外部クエリと呼ばれます。
サブクエリの種類
サブクエリには、主に次の 2 種類があります。
- 関連付け済み: 外部クエリの値を参照するサブクエリ。 サブクエリは、外部クエリが処理する行ごとに 1 回評価されます。
- 非相関: 外部クエリに依存しないサブクエリ。 外部クエリに依存することなく、単独で実行できます。
サブクエリは、返される行と列の数に基づいてさらに分類できます。 次の 3 つのタイプがあります:
- テーブル: 複数の行と複数の列を返します。
- 複数値: 複数の行と 1 つの列を返します。
- スカラー: 1 つの行と 1 つの列を返します。
クエリ言語のクエリでは、常に 1 つの列 (単純な値または複雑な項目) が返されます。 そのため、複数値サブクエリとスカラー サブクエリのみが適用されます。 複数値サブクエリは、リレーショナル式として FROM 句でのみ使用できます。 スカラー サブクエリは、 SELECT 句または WHERE 句のスカラー式として、または FROM 句のリレーショナル式として使用できます。
複数値サブクエリ
複数値サブクエリは一連の項目を返し、常に FROM 句内で使用されます。 これらは次の目的で使用されます。
-
JOIN(自己結合) 式の最適化。 - コストの高い式を 1 回評価し、複数回参照する。
自己結合式を最適化する
複数値サブクエリでは、WHERE 句内のすべてのクロス結合の後ではなく、各 select-many 式の後に述語をプッシュすることで、JOIN式を最適化できます。
次のクエリがあるとします。
SELECT VALUE
COUNT(1)
FROM
products p
JOIN
t in p.tags
JOIN
s in p.sizes
JOIN
c in p.colors
WHERE
t.key IN ("fabric", "material") AND
s["order"] >= 3 AND
c LIKE "%gray%"
このクエリでは、インデックスは、fabricまたはmaterialのいずれかのkeyを持つタグを持つ項目、*3 より大きいorder値を持つ少なくとも 1 つのサイズ、およびサブ文字列としてgrayを持つ少なくとも 1 つの色を持つ項目と一致します。 ここでのJOIN式は、フィルターが適用される前に、一致する各項目のtags、sizes、およびcolors配列のすべての項目のクロス積を実行します。
次に、 WHERE 句は、各 $<c、t、n、s>$ タプルにフィルター述語を適用します。 たとえば、一致する項目に 3 つの配列のそれぞれに 10 個の項目がある場合、次の数式を使用して 1,000 タプルに拡張されます。
$$1 x 10 x 10 x 10$$
ここでサブクエリを使用すると、次の式と結合する前に、結合された配列項目を除外するのに役立ちます。
このクエリは前のクエリと同じですが、サブクエリを使用します。
SELECT VALUE
COUNT(1)
FROM
products p
JOIN
(SELECT VALUE t FROM t IN p.tags WHERE t.key IN ("fabric", "material"))
JOIN
(SELECT VALUE s FROM s IN p.sizes WHERE s["order"] >= 3)
JOIN
(SELECT VALUE c FROM c in p.colors WHERE c LIKE "%gray%")
タグ配列内の 1 つの項目のみがフィルターに一致し、quantity 配列と stock 配列の両方に 5 つの項目があるとします。 その後、JOIN式は、最初のクエリの 1,000 項目ではなく、この数式を使用して 25 タプルに拡張されます。
$$1 x 1 x 5 x 5$$
1 回評価して何度も参照する
サブクエリは、ユーザー定義関数 (UDF)、複雑な文字列、算術式などの高価な式を使用してクエリを最適化するのに役立ちます。 サブクエリを JOIN 式と共に使用すると、式を 1 回評価できますが、何度も参照できます。
このサンプル クエリでは、クエリで 25% 複数回の補足値を使用して価格を計算します。
SELECT VALUE {
subtotal: p.price,
total: (p.price * 1.25)
}
FROM
products p
WHERE
(p.price * 1.25) < 22.25
計算を 1 回だけ実行する同等のクエリを次に示します。
SELECT VALUE {
subtotal: p.price,
total: totalPrice
}
FROM
products p
JOIN
(SELECT VALUE p.price * 1.25) totalPrice
WHERE
totalPrice < 22.25
[
{
"subtotal": 15,
"total": 18.75
},
{
"subtotal": 10,
"total": 12.5
},
...
]
ヒント
JOIN式のクロス積動作に注意してください。 式が undefinedに評価される場合は、 JOIN 式が値ではなくサブクエリからオブジェクトを直接返すことによって常に 1 つの行を生成するようにする必要があります。
外部参照データを使用してリレーショナル結合を模倣する
多くの場合、 測定単位など、ほとんど変化しない静的データを参照する必要があります。 クエリ内の各項目の静的データを重複しないようにするのが理想的です。 この重複を回避すると、個々の項目のサイズを小さくすることで、ストレージが節約され、書き込みパフォーマンスが向上します。 サブクエリを使用して、静的参照データのコレクションで内部結合セマンティクスを模倣できます。
たとえば、衣服の長さを表す次の一連の測定値を考えてみましょう。
| サイズ | Length | 単位 |
|---|---|---|
xs |
63.5 |
cm |
s |
64.5 |
cm |
m |
66.0 |
cm |
l |
67.5 |
cm |
xl |
69.0 |
cm |
xxl |
70.5 |
cm |
次のクエリは、ユニットの名前を出力に追加するために、このデータとの結合を模倣します。
SELECT
p.name,
p.subCategory,
s.description AS size,
m.length,
m.unit
FROM
products p
JOIN
s IN p.sizes
JOIN m IN (
SELECT VALUE [
{size: 'xs', length: 63.5, unit: 'cm'},
{size: 's', length: 64.5, unit: 'cm'},
{size: 'm', length: 66, unit: 'cm'},
{size: 'l', length: 67.5, unit: 'cm'},
{size: 'xl', length: 69, unit: 'cm'},
{size: 'xxl', length: 70.5, unit: 'cm'}
]
)
WHERE
s.key = m.size
スカラー サブクエリ
スカラー サブクエリ式は、1 つの値に評価されるサブクエリです。 スカラー サブクエリ式の値は、サブクエリのプロジェクション (SELECT 句) の値です。 スカラーサブクエリ式は、スカラー式が有効な多くの場所で使用できます。 たとえば、 SELECT 句と WHERE 句の両方の式でスカラー サブクエリを使用できます。
スカラー サブクエリを使用すると、クエリの最適化に必ずしも役立つとは限りません。 たとえば、スカラー サブクエリを引数としてシステム関数またはユーザー定義関数に渡しても、リソース ユニット (RU) の消費や待機時間を短縮する利点はありません。
スカラー サブクエリは、さらに次のように分類できます。
- 単純式スカラー サブクエリ
- 集約スカラー サブクエリ
単純式スカラー サブクエリ
単純式スカラー サブクエリは、集計式を含まない SELECT 句を持つ相関サブクエリです。 これらのサブクエリは、コンパイラによって 1 つの大きな単純な式に変換されるため、最適化の利点はありません。 内部クエリと外部クエリの間に関連するコンテキストはありません。
最初の例として、この単純なクエリを考えてみましょう。
SELECT
1 AS a,
2 AS b
単純式スカラー サブクエリを使用して、このクエリを書き直すことができます。
SELECT
(SELECT VALUE 1) AS a,
(SELECT VALUE 2) AS b
どちらのクエリも同じ出力を生成します。
[
{
"a": 1,
"b": 2
}
]
次のクエリ例では、一意識別子を単純式スカラー サブクエリとしてプレフィックスと連結します。
SELECT
(SELECT VALUE CONCAT('ID-', p.id)) AS internalId
FROM
products p
この例では、単純式スカラー サブクエリを使用して、各項目の関連フィールドのみを返します。 クエリはアイテムごとに何かを出力しますが、サブクエリ内のフィルターを満たしている場合にのみ投影フィールドが含まれます。
SELECT
p.id,
(SELECT p.name WHERE CONTAINS(p.name, "Shoes")).name
FROM
products p
[
{
"id": "00000000-0000-0000-0000-000000004041",
"name": "Remdriel Shoes"
},
{
"id": "00000000-0000-0000-0000-000000004322"
},
{
"id": "00000000-0000-0000-0000-000000004055"
}
]
集約スカラー サブクエリ
集約スカラー サブクエリは、1 つの値に評価されるプロジェクションまたはフィルターに集計関数を持つサブクエリです。
最初の例として、次のフィールドを持つ項目を考えてみましょう。
[
{
"name": "Blators Snowboard Boots",
"colors": [
"turquoise",
"cobalt",
"jam",
"galliano",
"violet"
],
"sizes": [ ... ],
"tags": [ ... ]
}
]
プロジェクションで単一の集計関数式を持つサブクエリを次に示します。 このクエリでは、各項目のすべてのタグがカウントされます。
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount
FROM
products p
WHERE
p.id = "00000000-0000-0000-0000-000000004389"
[
{
"name": "Blators Snowboard Boots",
"colorsCount": 5
}
]
フィルターと同じサブクエリを次に示します。
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount,
(SELECT VALUE COUNT(1) FROM c IN p.colors WHERE c LIKE "%t") AS colorsEndsWithTCount
FROM
products p
[
{
"name": "Blators Snowboard Boots",
"colorsCount": 5,
"colorsEndsWithTCount": 2
}
]
複数の集計関数式を持つ別のサブクエリを次に示します。
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount,
(SELECT VALUE COUNT(1) FROM s in p.sizes) AS sizesCount,
(SELECT VALUE COUNT(1) FROM t IN p.tags) AS tagsCount
FROM
products p
[
{
"name": "Blators Snowboard Boots",
"colorsCount": 5,
"sizesCount": 7,
"tagsCount": 2
}
]
最後に、プロジェクションとフィルターの両方で集計サブクエリを含むクエリを次に示します。
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Small") AS smallSizesCount,
(SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Large") AS largeSizesCount
FROM
products p
WHERE
(SELECT VALUE COUNT(1) FROM c IN p.colors) >= 5
このクエリを記述するより最適な方法は、サブクエリで結合し、SELECT 句と WHERE 句の両方でサブクエリエイリアスを参照することです。 このクエリは、プロジェクションとフィルターの両方ではなく、join ステートメント内でのみサブクエリを実行する必要があるため、より効率的です。
SELECT
p.name,
colorCount,
smallSizesCount,
largeSizesCount
FROM
products p
JOIN
(SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorCount
JOIN
(SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Small") AS smallSizesCount
JOIN
(SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Large") AS largeSizesCount
WHERE
colorCount >= 5 AND
largeSizesCount > 0 AND
smallSizesCount > 0
EXISTS 式
クエリ言語では、 EXISTS 式がサポートされています。 この式は、クエリ言語に組み込まれている集計スカラー サブクエリです。
EXISTS はサブクエリ式を受け取り、サブクエリが行を返す場合は true を返します。 それ以外の場合は、falseを返します。
クエリ エンジンはブール式とその他のスカラー式を区別しないため、SELECT句と WHERE 句の両方でEXISTSを使用できます。 この動作は T-SQL とは異なり、ブール式はフィルターのみに制限されます。
EXISTSサブクエリがundefinedされた単一の値を返す場合、EXISTSは false に評価されます。 たとえば、何も返されない次のクエリを考えてみましょう。
SELECT VALUE
undefined
EXISTS式と前のクエリをサブクエリとして使用すると、式はfalseを返します。
SELECT VALUE
EXISTS (SELECT VALUE undefined)
[
false
]
前のサブクエリの VALUE キーワードを省略すると、サブクエリは 1 つの空のオブジェクトを持つ配列に評価されます。
SELECT
undefined
[
{}
]
その時点で、オブジェクト ({}) が技術的に終了するため、EXISTS式はtrueに評価されます。
SELECT VALUE
EXISTS (SELECT undefined)
[
true
]
ARRAY_CONTAINSの一般的なユース ケースは、配列内の項目の存在によって項目をフィルター処理します。 この場合、tags配列に "outerwear" という名前の項目が含まれているかどうかを確認します。
SELECT
p.name,
p.colors
FROM
products p
WHERE
ARRAY_CONTAINS(p.colors, "cobalt")
同じクエリで、別のオプションとして EXISTS を使用できます。
SELECT
p.name,
p.colors
FROM
products p
WHERE
EXISTS (SELECT VALUE c FROM c IN p.colors WHERE c = "cobalt")
また、 ARRAY_CONTAINS は、値が配列内の任意の要素と等しいかどうかを確認することしかできません。 配列プロパティに対してより複雑なフィルターが必要な場合は、代わりに JOIN を使用します。
複数の項目がそれぞれ accessories 配列を含むセット内の項目の例を考えてみます。
[
{
"name": "Cosmoxy Pack",
"tags": [
{
"key": "fabric",
"value": "leather",
"description": "Leather"
},
{
"key": "volume",
"value": "68-gal",
"description": "6.8 Gal"
}
]
}
]
次に、各項目内の配列の type プロパティと quantityOnHand プロパティに基づいてフィルター処理する次のクエリについて考えてみましょう。
SELECT
p.name,
t.description AS tag
FROM
products p
JOIN
t in p.tags
WHERE
t.key = "fabric" AND
t["value"] = "leather"
[
{
"name": "Cosmoxy Pack",
"tag": "Leather"
}
]
コレクション内の項目ごとに、配列要素を使用してクロス積が実行されます。 この JOIN 操作により、配列内のプロパティをフィルター処理できます。 ただし、このクエリの RU 消費量は大きくなります。 たとえば、 1,000 個の項目が各配列に 100 個の項目を含む場合、次の数式を使用して 100,000 タプルに拡張されます。
$$1,000 x 100$$
EXISTSを使用すると、この高価なクロスプロダクトを回避するのに役立ちます。 この次の例では、クエリは EXISTS サブクエリ内の配列要素をフィルター処理します。 配列要素がフィルターと一致する場合は、それを投影し、 EXISTS は true に評価されます。
SELECT VALUE
p.name
FROM
products p
WHERE
EXISTS (
SELECT VALUE
t
FROM
t IN p.tags
WHERE
t.key = "fabric" AND
t["value"] = "leather"
)
[
"Cosmoxy Pack"
]
クエリでは、 EXISTS エイリアスを指定し、プロジェクションでエイリアスを参照することもできます。
SELECT
p.name,
EXISTS (
SELECT VALUE
t
FROM
t IN p.tags
WHERE
t.key = "fabric" AND
t["value"] = "leather"
) AS containsFabricLeatherTag
FROM
products p
[
{
"name": "Cosmoxy Pack",
"containsFabricLeatherTag": true
}
]
ARRAY 式
ARRAY式を使用して、クエリの結果を配列として投影できます。 この式は、クエリの SELECT 句内でのみ使用できます。
これらの例では、少なくともこの項目を含むコンテナーがあるとします。
[
{
"name": "Menti Sandals",
"sizes": [
{
"key": "5"
},
{
"key": "6"
},
{
"key": "7"
},
{
"key": "8"
},
{
"key": "9"
}
]
}
]
この最初の例では、式は SELECT 句内で使用されます。
SELECT
p.name,
ARRAY (
SELECT VALUE
s.key
FROM
s IN p.sizes
) AS sizes
FROM
products p
WHERE
p.name = "Menti Sandals"
[
{
"name": "Menti Sandals",
"sizes": [
"5",
"6",
"7",
"8",
"9"
]
}
]
他のサブクエリと同様に、 ARRAY 式を使用したフィルターも可能です。
SELECT
p.name,
ARRAY (
SELECT VALUE
s.key
FROM
s IN p.sizes
WHERE
STRINGTONUMBER(s.key) <= 6
) AS smallSizes,
ARRAY (
SELECT VALUE
s.key
FROM
s IN p.sizes
WHERE
STRINGTONUMBER(s.key) >= 9
) AS largeSizes
FROM
products p
WHERE
p.name = "Menti Sandals"
[
{
"name": "Menti Sandals",
"smallSizes": [
"5",
"6"
],
"largeSizes": [
"9"
]
}
]
配列式は、サブクエリの FROM 句の後に配置することもできます。
SELECT
p.name,
z.s.key AS sizes
FROM
products p
JOIN
z IN (
SELECT VALUE
ARRAY (
SELECT
s
FROM
s IN p.sizes
WHERE
STRINGTONUMBER(s.key) <= 8
)
)
[
{
"name": "Menti Sandals",
"sizes": "5"
},
{
"name": "Menti Sandals",
"sizes": "6"
},
{
"name": "Menti Sandals",
"sizes": "7"
},
{
"name": "Menti Sandals",
"sizes": "8"
}
]