サブクエリ

サブクエリについて

サブクエリは、別のクエリステートメント内に表示されるクエリです。サブクエリは、サブ 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 |
+---------+