サブクエリ

サブクエリについて

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