サブクエリについて
サブクエリは、別のクエリ ステートメント内に表示されるクエリです。サブクエリは、サブ SELECT
またはネストされた SELECT
とも呼ばれます。完全な SELECT
構文は、サブクエリで有効です。
式のサブクエリ
式サブクエリは、式が有効なときはいつでもクエリで使用されます。列またはテーブルと対照的に、単一の値を返します。式サブクエリは、相互に関連付けることができます。
スカラー サブクエリ
( subquery )
説明
式内のサブクエリは、スカラー サブクエリと解釈されます。スカラー サブクエリは、多くの場合 SELECT
リストまたは WHERE
句で使用されます。
スカラー サブクエリでは 1 つの列を選択する必要があります。複数の列を選択しようとすると、分析エラーが発生します。1 つの式を含む SELECT
リストは、1 つの列を選択する最も簡単な方法です。スカラー サブクエリの結果タイプは、その式のタイプです。
また、SELECT AS STRUCT
を使用して、フィールドが 1 つ以上の式で定義されている単一の STRUCT
型の値を選択するサブクエリを定義することもできます。
サブクエリによって正確に 1 行が返される場合、その単一の値がスカラー サブクエリの結果になります。サブクエリがゼロ行を返す場合、結果は NULL
になります。サブクエリが複数の行を返す場合、クエリはランタイム エラーで失敗します。
例
この例では、相関スカラー サブクエリは、Players
テーブルと Mascots
テーブルを使用して、プレーヤーのリストのマスコットを返します。
SELECT
username,
(SELECT mascot FROM Mascots WHERE Players.team = Mascots.team) AS player_mascot
FROM
Players;
+---------------------------+
| username | player_mascot |
+---------------------------+
| gorbie | cardinal |
| junelyn | finch |
| corba | parrot |
+---------------------------+
この例では、集計スカラー サブクエリが avg_level
(Players
テーブル内のユーザーの平均値)を計算します。
SELECT
username,
level,
(SELECT AVG(level) FROM Players) AS avg_level
FROM
Players;
+---------------------------------------+
| username | level | avg_level |
+---------------------------------------+
| gorbie | 29 | 24.66 |
| junelyn | 2 | 24.66 |
| corba | 43 | 24.66 |
+---------------------------------------+
ARRAY サブクエリ
ARRAY ( subquery )
説明
ARRAY サブクエリは、ARRAY を返すという点で、式サブクエリの特殊なケースです。サブクエリがゼロ行を返す場合は、空の ARRAY を返します。NULL
ARRAY が返されることはありません。
配列サブクエリ内の SELECT
リストには、ARRAY サブクエリによって返される配列の要素タイプを定義する型の 1 つの列だけが必要です。それ以外の場合は、エラーが返されます。サブクエリが SELECT AS STRUCT
で作成される場合、SELECT
リストに複数の列を含めることができ、配列サブクエリからの戻り値は、構造化された STRUCT の ARRAY になります。SELECT AS
を使用せずに複数の列を選択すると、エラーが発生します。
ARRAY サブクエリは、SELECT AS STRUCT
を使用して構造体の配列を作成できます。
完全なセマンティクスについては、配列関数をご覧ください。
例
この例では、ARRAY サブクエリは、NPCs
テーブルの Red Team に割り当てられたユーザー名の配列を返します。
SELECT
ARRAY(SELECT username FROM NPCs WHERE team = 'red') AS red;
+-----------------+
| red |
+-----------------+
| [niles,jujul] |
+-----------------+
IN サブクエリ
value [ NOT ] IN ( subquery )
説明
value
が、サブクエリによって返された一連の行の中にある場合、TRUE を返します。サブクエリによってゼロ行が返される場合、FALSE が返されます。
サブクエリの SELECT リストには、任意の型の列が 1 つ必要であり、その型は value
の型と同等である必要があります。それ以外の場合は、エラーが返されます。NULL
の処理を含む完全なセマンティクスについては、IN
演算子をご覧ください。
配列付きの IN
サブクエリを使用する必要がある場合は、次のような式になります。
value [ NOT ] IN ( subquery )
value [ NOT ] IN UNNEST( ARRAY( subquery ) )
例
この例では、corba
というユーザー名が Players
テーブル内に存在するかどうかを確認する IN
演算子。
SELECT
'corba' IN (SELECT username FROM Players) AS result;
+--------+
| result |
+--------+
| TRUE |
+--------+
EXISTS サブクエリ
EXISTS( subquery )
説明
サブクエリによって 1 つ以上の行が生成される場合、TRUE が返されます。サブクエリによってゼロ行が生成される場合、FALSE が返されます。NULL
が返されることはありません。その他のすべての式サブクエリとは異なり、列リストに関する規則はありません。任意の数の列を選択できます。また、クエリの結果に影響は及ぼされません。
例
この例では、EXISTS
演算子が Players
テーブルを使用して行が生成されたかどうかを確認します。
SELECT
EXISTS(SELECT username FROM Players WHERE team = 'yellow') AS result;
+--------+
| result |
+--------+
| FALSE |
+--------+
テーブル サブクエリ
FROM ( subquery ) [ [ AS ] alias ]
説明
テーブル サブクエリでは、外部クエリはサブクエリの結果をテーブルとして扱います。これらは FROM
句でのみ使用できます。
例
この例では、サブクエリは Players
テーブルからユーザー名のテーブルを返します。
SELECT results.username
FROM (SELECT * FROM Players) AS results;
+-----------+
| username |
+-----------+
| gorbie |
| junelyn |
| corba |
+-----------+
この例では、Red Team に割り当てられた NPCs
のリストが返されます。
SELECT
username
FROM (
WITH red_team AS (SELECT * FROM NPCs WHERE team = 'red')
SELECT * FROM red_team
);
+-----------+
| username |
+-----------+
| niles |
| jujul |
+-----------+
相関サブクエリ
相関サブクエリは、そのサブクエリの外部から列を参照するサブクエリです。相関関係では、サブクエリの結果を再利用できません。詳しくは、こちらをご覧ください。
例
この例では、プレーヤーが割り当てられていないマスコットのリストが返されます。Mascots
テーブルと Players
テーブルが参照されています。
SELECT mascot
FROM Mascots
WHERE
NOT EXISTS(SELECT username FROM Players WHERE Mascots.team = Players.team);
+----------+
| mascot |
+----------+
| sparrow |
+----------+
この例では、相関スカラー サブクエリは、Players
テーブルと Mascots
テーブルを使用して、プレーヤーのリストのマスコットを返します。
SELECT
username,
(SELECT mascot FROM Mascots WHERE Players.team = Mascots.team) AS player_mascot
FROM Players;
+---------------------------+
| username | player_mascot |
+---------------------------+
| gorbie | cardinal |
| junelyn | finch |
| corba | parrot |
+---------------------------+
Volatile サブクエリ
Volatile サブクエリは、同じ入力に対して同じ結果が生成されるとは限りません。たとえば、乱数を返す関数を含むサブクエリの結果は常に同じではないため、永続性はありません。
例
この例では、Players
テーブルからランダムにユーザー名が返されます。
SELECT
results.username
FROM
(SELECT * FROM Players WHERE RAND() < 0.5) AS results;
-- The results are not always the same when you execute
-- the preceding query, but will look similar to this:
+----------+
| username |
+----------+
| gorbie |
| junelyn |
+----------+
サブクエリの評価ルール
サブクエリの中には、1 回だけ評価されるものもあります。
- クエリプランによっては、相関性、永続性のないサブクエリは各行で 1 回再評価される可能性があります。
- パラメータ値のあらゆる組み合わせについて、相関サブクエリを再評価する必要があります。クエリプランによっては、複数の行が同じパラメータ値を持つ場合でも、各行で 1 回相関サブクエリが再評価される可能性があります。
例で使用される一般的なテーブル
いくつかの例では、Players
というテーブルが参照されます。
+-----------------------------+
| username | level | team |
+-----------------------------+
| gorbie | 29 | red |
| junelyn | 2 | blue |
| corba | 43 | green |
+-----------------------------+
いくつかの例では、NPCs
というテーブルが参照されます。
+-------------------+
| username | team |
+-------------------+
| niles | red |
| jujul | red |
| effren | blue |
+-------------------+
いくつかの例では、Mascots
というテーブルが参照されます。
+-------------------+
| mascot | team |
+-------------------+
| cardinal | red |
| parrot | green |
| finch | blue |
| sparrow | yellow |
+-------------------+
この WITH
句を使用して、WITH
句をサポートするサブクエリで Players
と NPCs
の一時テーブル名をエミュレートできます。
WITH
Players AS (
SELECT 'gorbie' AS username, 29 AS level, 'red' AS team UNION ALL
SELECT 'junelyn', 2 , 'blue' UNION ALL
SELECT 'corba', 43, 'green'),
NPCs AS (
SELECT 'niles' AS username, 'red' AS team UNION ALL
SELECT 'jujul', 'red' UNION ALL
SELECT 'effren', 'blue'),
Mascots AS (
SELECT 'cardinal' AS mascot , 'red' AS team UNION ALL
SELECT 'parrot', 'green' UNION ALL
SELECT 'finch', 'blue' UNION ALL
SELECT 'sparrow', 'yellow')
SELECT * FROM (
SELECT username, team FROM Players UNION ALL
SELECT username, team FROM NPCs);