標準 SQL のサブクエリ

サブクエリについて

サブクエリは、別のクエリ ステートメント内に表示されるクエリです。サブクエリは、サブ 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_levelPlayers テーブル内のユーザー アカウントの平均値)を計算します。

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 句をサポートするサブクエリで PlayersNPCs の一時テーブル名をエミュレートできます。

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)