サブクエリについて
サブクエリは、別のクエリ ステートメント内に表示されるクエリです。サブクエリは、サブ SELECT またはネストされた SELECT とも呼ばれます。完全な SELECT 構文は、サブクエリで有効です。
式のサブクエリ
式サブクエリは、式が有効なときはいつでもクエリで使用されます。列またはテーブルと対照的に、単一の値を返します。式サブクエリは、相互に関連付けることができます。
スカラー サブクエリ
( subquery )
説明
式内のサブクエリは、スカラー サブクエリと解釈されます。スカラー サブクエリは、多くの場合 SELECT リストまたは WHERE 句で使用されます。
スカラー サブクエリでは 1 つの列を選択する必要があります。複数の列を選択しようとすると、分析エラーが発生します。1 つの式を含む SELECT リストは、1 つの列を選択する最も簡単な方法です。スカラー サブクエリの結果タイプは、その式のタイプです。
また、SELECT AS STRUCT を使用して、フィールドが 1 つ以上の式で定義されている単一の STRUCT 型の値を選択するサブクエリを定義することもできます。
サブクエリによって正確に 1 行が返される場合、その単一の値がスカラー サブクエリの結果になります。サブクエリがゼロ行を返す場合、結果は NULL になります。サブクエリが複数の行を返す場合、クエリはランタイム エラーで失敗します。
例
この例では、相関スカラー サブクエリは、Players テーブルと Guilds テーブルを使用して、プレーヤーのリストのマスコットを返します。
SELECT account, (SELECT mascot FROM Guilds WHERE Players.guild = id) AS player_mascot
FROM Players;
+---------------------------+
| account | player_mascot |
+---------------------------+
| gorbie | cardinal |
| junelyn | finch |
| corba | parrot |
+---------------------------+
この例では、集計スカラー サブクエリが avg_level（Players テーブル内のユーザー アカウントの平均値）を計算します。
SELECT account, level, (SELECT AVG(level) FROM Players) AS avg_level
FROM Players;
+---------------------------------------+
| account | 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 Guild に割り当てられたアカウントの配列を返します。
SELECT ARRAY(SELECT account FROM NPCs WHERE guild = 'red') as red
FROM NPCs LIMIT 1;
+-----------------+
| 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 account FROM Players) as result;
+--------+
| result |
+--------+
| TRUE |
+--------+
EXISTS サブクエリ
EXISTS ( subquery )
説明
サブクエリによって 1 つ以上の行が生成される場合、TRUE が返されます。サブクエリによってゼロ行が生成される場合、FALSE が返されます。NULL が返されることはありません。その他のすべての式サブクエリとは異なり、列リストに関する規則はありません。任意の数の列を選択できます。また、クエリの結果に影響は及ぼされません。
例
この例では、EXISTS 演算子が Players テーブルを使用して行が生成されたかどうかを確認します。
SELECT EXISTS(SELECT account FROM Players WHERE guild="yellow") as result;
+--------+
| result |
+--------+
| FALSE |
+--------+
テーブル サブクエリ
FROM ( subquery ) [ [ AS ] alias ]
説明
テーブル サブクエリでは、外部クエリはサブクエリの結果をテーブルとして扱います。これらは FROM 句でのみ使用できます。
例
この例では、サブクエリは Players テーブルからアカウントのテーブルを返します。
SELECT results.account
FROM (SELECT * FROM Players) AS results;
+-----------+
| account |
+-----------+
| gorbie |
| junelyn |
| corba |
+-----------+
この例では、Red Guild に割り当てられた NPCs のリストが返されます。
SELECT account FROM (
WITH red_guild AS (SELECT * FROM NPCs WHERE guild='red')
SELECT * FROM red_guild);
+-----------+
| account |
+-----------+
| niles |
| jujul |
+-----------+
相関サブクエリ
相関サブクエリは、そのサブクエリの外部から列を参照するサブクエリです。相関関係では、サブクエリの結果を再利用できません。詳しくは、こちらをご覧ください。
例
この例では、プレーヤーが割り当てられていないマスコットのリストが返されます。Guilds テーブルと Players テーブルが参照されています。
SELECT mascot
FROM Guilds
WHERE NOT EXISTS (SELECT account
FROM Players
WHERE Guilds.id = Players.guild)
+----------+
| mascot |
+----------+
| sparrow |
+----------+
この例では、相関スカラー サブクエリは、Players テーブルと Guilds テーブルを使用して、プレーヤーのリストのマスコットを返します。
SELECT account, (SELECT mascot FROM Guilds WHERE Players.guild = id) AS player_mascot
FROM Players;
+---------------------------+
| account | player_mascot |
+---------------------------+
| gorbie | cardinal |
| junelyn | finch |
| corba | parrot |
+---------------------------+
Volatile サブクエリ
Volatile サブクエリは、同じ入力に対して同じ結果が生成されるとは限りません。たとえば、乱数を返す関数を含むサブクエリの結果は常に同じではないため、永続性はありません。
例
この例では、Players テーブルからランダムにアカウントが返されます。
SELECT results.account
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:
+---------+
| account |
+---------+
| gorbie |
| junelyn |
+---------+
サブクエリの評価ルール
サブクエリの中には、1 回だけ評価されるものもあります。
- クエリプランによっては、相関性、永続性のないサブクエリは各行で 1 回再評価される可能性があります。
- パラメータ値のあらゆる組み合わせについて、相関サブクエリを再評価する必要があります。クエリプランによっては、複数の行が同じパラメータ値を持つ場合でも、各行で 1 回相関サブクエリが再評価される可能性があります。
例で使用される一般的なテーブル
いくつかの例では、Players というテーブルが参照されます。
+-----------------------------+
| account | level | guild |
+-----------------------------+
| gorbie | 29 | red |
| junelyn | 2 | blue |
| corba | 43 | green |
+-----------------------------+
いくつかの例では、NPCs というテーブルが参照されます。
+-------------------+
| account | guild |
+-------------------+
| niles | red |
| jujul | red |
| effren | blue |
+-------------------+
いくつかの例では、Guilds というテーブルが参照されます。
+-------------------+
| mascot | id |
+-------------------+
| cardinal | red |
| parrot | green |
| finch | blue |
| sparrow | yellow |
+-------------------+
この WITH 句を使用して、WITH 句をサポートするサブクエリで Players と NPCs の一時テーブル名をエミュレートできます。
WITH
Players AS (
SELECT 'gorbie' AS account, 29 AS level, 'red' AS guild UNION ALL
SELECT 'junelyn', 2 , 'blue' UNION ALL
SELECT 'corba', 43, 'green'),
NPCs AS (
SELECT 'niles' AS account, 'red' AS guild UNION ALL
SELECT 'jujul', 'red' UNION ALL
SELECT 'effren', 'blue'),
Guilds AS (
SELECT 'cardinal' AS mascot , 'red' AS id UNION ALL
SELECT 'parrot', 'green' UNION ALL
SELECT 'finch', 'blue' UNION ALL
SELECT 'sparrow', 'yellow')
SELECT * FROM (
SELECT account, guild FROM Players UNION ALL
SELECT account, guild FROM NPCs)