クエリ構文

クエリ ステートメントは、1 つ以上のテーブルまたは式をスキャンし、計算結果の行を返します。このトピックでは、Cloud Spanner SQL での SQL クエリの構文について説明します。

SQL 構文

query_statement:
    [ statement_hint_expr ][ table_hint_expr ][ join_hint_expr ]
query_expr statement_hint_expr: '@{' statement_hint_key = statement_hint_value [, ...] '}' statement_hint_key: { USE_ADDITIONAL_PARALLELISM| OPTIMIZER_VERSION } query_expr: [ WITH with_query_name AS ( query_expr ) [, ...] ] { select | ( query_expr ) | query_expr set_op query_expr } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [{ ALL | DISTINCT }] { [ expression. ]* | expression [ [ AS ] alias ] } [, ...] [ FROM from_item [ tablesample_type ] [, ...] ] [ WHERE bool_expression ] [ GROUP BY expression [, ...] ] [ HAVING bool_expression ] set_op: UNION { ALL | DISTINCT } | INTERSECT { ALL | DISTINCT } | EXCEPT { ALL | DISTINCT } from_item: { table_name [ table_hint_expr ] [ [ AS ] alias ] | join | ( query_expr ) [ table_hint_expr ] [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ table_hint_expr ] [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ table_hint_expr ] [ [ AS ] alias ] } table_hint_expr: '@{' table_hint_key = table_hint_value '}' table_hint_key: { FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION } join: from_item [ join_type ] [ join_method ] JOIN [ join_hint_expr ] from_item [ { ON bool_expression | USING ( join_column [, ...] ) } ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

join_method:
{ HASH }
join_hint_expr: '@{' join_hint_key = join_hint_value [, ...] '}' join_hint_key: { FORCE_JOIN_ORDER | JOIN_METHOD } tablesample_type: TABLESAMPLE sample_method (sample_size percent_or_rows ) sample_method: { BERNOULLI | RESERVOIR } sample_size: numeric_value_expression percent_or_rows: { PERCENT | ROWS }

表記ルール

  • 角括弧「[ ]」はオプションの句です。
  • 丸括弧「( )」はリテラルの括弧を表します。
  • 縦線「|」は論理 OR を表します。
  • 波括弧「{ }」ではオプションのセットを囲みます。
  • 角括弧内の省略記号に先立つカンマ「[, ... ]」は、前の項目をカンマで区切ったリストに繰り返すことができることを示しています。

ステートメントのヒント

クエリ ステートメントで使用できるヒントは次のとおりです。

ヒントキー 指定できる値 説明
USE_ADDITIONAL_PARALLELISM TRUE
FALSE(デフォルト)
TRUE の場合、可能であれば実行エンジンでより多くの並列処理を使用します。これにより、他のオペレーションで使用できるリソースが減るため、同じインスタンスでレイテンシの影響を受けやすいオペレーションを実行する場合は、このヒントを避けます。
OPTIMIZER_VERSION 1 から N|latest 指定したオプティマイザーのバージョンを使用してクエリを実行します。使用できる値は 1N(最新のオプティマイザーのバージョン)または latest です。ヒントが設定されていない場合、オプティマイザはデータベースオ プションで設定されたバージョンまたはクライアント API で指定されたバージョンに対して実行されます。どちらも設定されていない場合、オプティマイザーはデフォルトで最新バージョンに設定されます。

バージョン設定の優先順位の点では、クライアント API で設定された値の方がデータベース オプションの値よりも優先され、この基準によって設定された値が最優先されます。

詳細については、クエリ オプティマイザーをご覧ください。

サンプル テーブル

次のテーブルは、このリファレンスのさまざまなクエリ句の動作を説明しているものです。

Roster テーブル

Roster テーブルには、プレーヤー名(LastName)のリストと、プレーヤーの学校に割り当てられた一意の ID(SchoolID)が含まれています。次のようなものです。

+-----------------------+
| LastName   | SchoolID |
+-----------------------+
| Adams      | 50       |
| Buchanan   | 52       |
| Coolidge   | 52       |
| Davis      | 51       |
| Eisenhower | 77       |
+-----------------------+

このリファレンスの例では、この WITH 句を使用して一時テーブル名をエミュレートできます。

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77)
SELECT * FROM Roster

PlayerStats テーブル

PlayerStats テーブルには、プレーヤー名(LastName)と、特定のゲームの対戦相手に割り当てられた一意の ID(OpponentID)、そのゲームで選手が獲得した点数(PointsScored)が含まれています。

+----------------------------------------+
| LastName   | OpponentID | PointsScored |
+----------------------------------------+
| Adams      | 51         | 3            |
| Buchanan   | 77         | 0            |
| Coolidge   | 77         | 1            |
| Davis      | 52         | 4            |
| Eisenhower | 50         | 13           |
+----------------------------------------+

このリファレンスの例では、この WITH 句を使用して一時テーブル名をエミュレートできます。

WITH PlayerStats AS
 (SELECT 'Adams' as LastName, 51 as OpponentID, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 77, 0 UNION ALL
  SELECT 'Coolidge', 77, 1 UNION ALL
  SELECT 'Adams', 52, 4 UNION ALL
  SELECT 'Buchanan', 50, 13)
SELECT * FROM PlayerStats

TeamMascot テーブル

TeamMascot テーブルには、一意の学校 ID(SchoolID)とその学校のマスコット(Mascot)のリストが含まれています。

+---------------------+
| SchoolID | Mascot   |
+---------------------+
| 50       | Jaguars  |
| 51       | Knights  |
| 52       | Lakers   |
| 53       | Mustangs |
+---------------------+

このリファレンスの例では、この WITH 句を使用して一時テーブル名をエミュレートできます。

WITH TeamMascot AS
 (SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
  SELECT 51, 'Knights' UNION ALL
  SELECT 52, 'Lakers' UNION ALL
  SELECT 53, 'Mustangs')
SELECT * FROM TeamMascot

SELECT リスト

構文:

SELECT  [{ ALL | DISTINCT }]
    { [ expression. ]* | expression [ [ AS ] alias ] } [, ...]

SELECT リストは、クエリが返す列を定義します。SELECT リスト内の式は、対応する FROM 句の from_item の列を参照します。

SELECT リストの各項目は、次のうちの 1 つです。

  • *
  • expression
  • expression.*

SELECT *

SELECT * は、select star とも呼ばれ、クエリ全体を実行した後に表示される各列に対して 1 つの出力列を生成します。

SELECT * FROM (SELECT "apple" AS fruit, "carrot" AS vegetable);

+-------+-----------+
| fruit | vegetable |
+-------+-----------+
| apple | carrot    |
+-------+-----------+

SELECT expression

SELECT リストの項目には、式を指定できます。これらの式から、1 つの値が求められ、オプションの明示的 alias を持つ 1 つの出力列が生成されます。

式が明示的なエイリアスを持たない場合、可能な場合は暗黙のエイリアスのルールに従って、暗黙のエイリアスを受け取ります。受け取れない場合、列は匿名となり、クエリの他の場所の名前でそれを参照できなくなります。

SELECT expression.*

SELECT リスト内の項目は expression.* という形式で指定することもできます。この場合、expression の各列または先頭フィールドに 1 つの出力列が生成されます。式は、テーブル エイリアスか、STRUCT のような単一値フィールド付きのデータ型になります。

次のクエリは、テーブル groceries の各列に対して 1 つの出力列を生成し、エイリアスには g が設定されます。

WITH groceries AS
  (SELECT "milk" AS dairy,
   "eggs" AS protein,
   "bread" AS grain)
SELECT g.*
FROM groceries AS g;

+-------+---------+-------+
| dairy | protein | grain |
+-------+---------+-------+
| milk  | eggs    | bread |
+-------+---------+-------+

その他の例:

WITH locations AS
  (SELECT STRUCT("Seattle" AS city, "Washington" AS state) AS location
  UNION ALL
  SELECT STRUCT("Phoenix" AS city, "Arizona" AS state) AS location)
SELECT l.location.*
FROM locations l;

+---------+------------+
| city    | state      |
+---------+------------+
| Seattle | Washington |
| Phoenix | Arizona    |
+---------+------------+
WITH locations AS
  (SELECT ARRAY<STRUCT<city STRING, state STRING>>[("Seattle", "Washington"),
    ("Phoenix", "Arizona")] AS location)
SELECT l.LOCATION[offset(0)].*
FROM locations l;

+---------+------------+
| city    | state      |
+---------+------------+
| Seattle | Washington |
+---------+------------+

SELECT 修飾子

次のとおり、SELECT クエリから返された結果を修正できます。

SELECT DISTINCT

SELECT DISTINCT ステートメントは、重複した行を破棄し、残りの行のみを返します。SELECT DISTINCT は、次の型の列を返すことはできません。

  • STRUCT
  • ARRAY

SELECT ALL

SELECT ALL ステートメントは、重複した行も含めて、すべての行を返します。SELECT ALLSELECT のデフォルト動作です。

SELECT での STRUCT の使用

  • 戻り値のデータ型のルートで STRUCT を返すクエリはサポートされていません。たとえば、次のクエリはサポートされていません

    SELECT STRUCT(1, 2) FROM Users;
    
  • 戻り値のデータ型のルートで構造体の配列を返すことがサポートされています。たとえば、次のクエリがサポートされます

    SELECT ARRAY(SELECT STRUCT(1, 2)) FROM Users;
    
  • しかし、クエリの結果で NULL 構造体を返すことができるクエリ形状がサポートされていないため、以下のクエリはサポートされていません

    SELECT ARRAY(SELECT IF(TRUE, STRUCT(1, 2), NULL)) FROM Users;
    

ARRAY 内で STRUCTs をクエリする方法のその他の例については、ARRAY 内の STRUCT 要素のクエリをご覧ください。

サブクエリで STRUCTs を使用する方法については、注もご覧ください。

エイリアス

SELECT リストのエイリアスの構文と可視性については、エイリアスの使用をご覧ください。

FROM 句

FROM 句は行を取得する 1 つまたは複数のテーブルを示し、これらの行を結合して、残りのクエリの処理のための単一ストリームの行を生成する方法を指定します。

構文

from_item: {
    table_name [ table_hint_expr ] [ [ AS ] alias ] |
    join |
    ( query_expr ) [ table_hint_expr ] [ [ AS ] alias ] |
    field_path |
    { UNNEST( array_expression ) | UNNEST( array_path ) | array_path }
        [ table_hint_expr ] [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] |
    with_query_name [ table_hint_expr ] [ [ AS ] alias ]
}
table_hint_expr: '@{' table_hint_key = table_hint_value '}' table_hint_key: { FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION }

table_name

既存のテーブルの名前。

SELECT * FROM Roster;
テーブルのヒント

テーブルで使用できるヒントは次のとおりです。

ヒントキー 指定できる値 説明
FORCE_INDEX 文字列。データベースまたは _BASE_TABLE の既存のインデックスの名前。インデックスではなく、ベーステーブルを使用します。
  • インデックスの名前に設定する場合、ベーステーブルではなくインデックスを使用します。インデックスで必要な列がすべて提供されない場合には、ベーステーブルとバック結合を行います。
  • 文字列 _BASE_TABLE に設定する場合、インデックスではなくインデックス戦略にベーステーブルを使用します。これは、文のヒント式で FORCE_INDEX を使用する場合にのみ有効な値です。

注: FORCE_INDEX は実際にはヒントではなく、ディレクティブです。インデックスが存在しない場合、エラーが発生します。

GROUPBY_SCAN_OPTIMIZATION TRUE
FALSE

GROUP BY または SELECT DISTINCT を使用している場合、GROUPBY_SCAN_OPTIMIZATION を使用すると、クエリの処理速度を速くすることができます。グループキーが基盤となるテーブルまたはインデックス キーの接頭辞を形成している場合、あるいはクエリで各グループの最初の行のみを必要としている場合に、処理が高速化されます。

この最適化は、オプティマイザーがクエリをより効率的にすると推定した場合に行われます。ヒントは、この決定を無効にします。ヒントが FALSE に設定されている場合、最適化は検討されません。ヒントが TRUE に設定されている場合、法的に問題がない限り、最適化が行われます。

次の例では、@{FORCE_INDEX=index_name} の形式でテーブル名にインデックス ディレクティブを追加して、テーブルからの読み取り時にセカンダリ インデックスを使用する方法を示しています。

SELECT s.SingerId, s.FirstName, s.LastName, s.SingerInfo
FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s
WHERE s.FirstName = "Catalina" AND s.LastName > "M";

明示的な各テーブル参照に対して単一のインデックスのみがサポートされていますが、クエリには、複数のインデックスを含めることができます。例:

SELECT s.SingerId, s.FirstName, s.LastName, s.SingerInfo, c.ConcertDate
FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s JOIN
     Concerts@{FORCE_INDEX=ConcertsBySingerId} AS c ON s.SingerId = c.SingerId
WHERE s.FirstName = "Catalina" AND s.LastName > "M";

セカンダリ インデックスでインデックス ディレクティブに関する詳細をお読みください。

join

以下の JOIN 型をご覧ください。

select

( select ) [ [ AS ] alias ] は、テーブルのサブクエリです。

field_path

FROM 句内で、field_path は特定のデータ型内のフィールドに解決する任意のパスです。field_path は、ネストされたデータ構造の中に任意で深く入り込むことができます。

有効な field_path の値の例を以下に示します。

SELECT * FROM T1 t1, t1.array_column;

SELECT * FROM T1 t1, t1.struct_column.array_field;

SELECT (SELECT ARRAY_AGG(c) FROM t1.array_column c) FROM T1 t1;

SELECT a.struct_field1 FROM T1 t1, t1.array_of_structs a;

SELECT (SELECT STRING_AGG(a.struct_field1) FROM t1.array_of_structs a) FROM T1 t1;

FROM 句のフィールドパスは、配列フィールドで終了しなければなりません。さらに、フィールドパスでは、パスの末尾の前に配列を含めることができません。たとえば、パス array_column.some_array.some_array_field は、パスの末尾の前に配列が含まれているため無効です。

注: パスに 1 つの名前のみがある場合、テーブルとして解釈されます。この回避策として、UNNEST を使用してパスをラップするか、完全修飾パスを使用します。

注: パスに複数の名前があり、フィールド名と一致する場合はフィールド名として解釈されます。パスをテーブル名として強制的に解釈するには、` を使用してパスをラップします。

UNNEST

UNNEST 演算子は ARRAY を受け取り、ARRAY 内の各要素を 1 行にしてテーブルを返します。UNNESTFROM 句の外で IN 演算子とともに使用することもできます。

ほとんどの要素型の入力 ARRAY について、UNNEST の出力には通常 1 つの列が含まれます。この単一の列にはオプションの alias があり、これを使用するとクエリの別の場所で列を参照できます。こうした要素型を持つ ARRAYS は、以下のような複数の列を返します。

  • STRUCT

UNNEST は入力 ARRAY 内の要素の順序を破壊します。配列要素のインデックスを含む 2 番目の列を返すには、オプションの WITH OFFSET 句を使用します(以下をご覧ください)。

STRUCT の入力 ARRAY について、UNNESTSTRUCT ごとに 1 行を返し、STRUCT 内の各フィールドについてそれぞれ別個の列が生成されます。各列のエイリアスは、対応する STRUCT フィールドの名前になります。

SELECT *
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')]);

+---+-----+
| x | y   |
+---+-----+
| 3 | bar |
| 1 | foo |
+---+-----+

ARRAY のネスト解除は、明示的にも、暗黙的にもできます。明示的なネスト解除の場合、array_expression は ARRAY 値を返す必要がありますが、ARRAY に解決される必要はなく、UNNEST キーワードが必要になります。

例:

SELECT * FROM UNNEST ([1, 2, 3]);

暗黙のネスト解除の場合、array_path は ARRAY に解決される必要があり、UNNEST キーワードは任意になります。

例:

SELECT x
FROM mytable AS t,
  t.struct_typed_column.array_typed_field1 AS x;

このシナリオでは、array_path はデータ構造に任意に深く入ることができますが、末尾のフィールドは ARRAY データ型でなければなりません。式の前のフィールドは、ARRAY から名前付きのフィールドを抽出できないため、いずれも ARRAY データ型にすることはできません。

UNNEST は、NULL を次のとおり扱います。

  • NULL と空の ARRAY はゼロ行を生成します。
  • NULL を含む ARRAY は、NULL 値を含む行を生成します。

オプションの WITH OFFSET 句は、UNNEST 演算で生成される行ごとに「オフセット」値(カウントはゼロから始まります)を含む別の列を返します。この列にはオプションの alias があり、デフォルトのエイリアスはオフセットです。

例:

SELECT * FROM UNNEST ( ) WITH OFFSET AS num;

UNNEST のその他の使用方法(作成、フラット化、フィルタリングなど)については、Arrays topic をご覧ください。

with_query_name

WITH 句内のクエリ名は(WITH 句を参照)、FROM 句の任意の場所から参照できる一時テーブルの名前のように機能します。次の例では、subQ1subQ2with_query_names になります。

例:

WITH
  subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52),
  subQ2 AS (SELECT SchoolID FROM subQ1)
SELECT DISTINCT * FROM subQ2;

WITH 句では、db.Roster などのようにテーブル名を修飾している場合を除き、クエリの実施中は同名の永続テーブルを非表示にします。

TABLESAMPLE 演算子

TABLESAMPLE 演算子を使用して、データセットのランダム サンプルを選択できます。この演算子は、大量のデータがあり、正確な答えが必要とされないテーブルを操作するときに便利です。

構文:

tablesample_type:
    TABLESAMPLE sample_method (sample_size percent_or_rows)

sample_method:
    { BERNOULLI | RESERVOIR }

sample_size:
    numeric_value_expression

percent_or_rows:
    { PERCENT | ROWS }

TABLESAMPLE 演算子を使用する場合、使用するサンプリング アルゴリズムを指定する必要があります。

  • BERNOULLI - 各行は percent 句で指定された確率で、独立して選択されます。結果として、約 N * percent/100 行が取得されます。

  • RESERVOIR - 実際のサンプルサイズ K(行数で表される)をパラメータとして使用します。入力が K より小さい場合、入力関係全体が出力されます。入力が K より大きい場合、Reservoir サンプリングでは正確に K のサイズのサンプルが出力されます。

TABLESAMPLE 演算子では、ROWS または PERCENT を選択する必要があります。PERCENT を選択した場合、値は 0~100 でなければなりません。ROWS を選択した場合、値は 0 以上でなければなりません。

次の例は、TABLESAMPLE 演算子の使用を説明します。

RESERVOIR サンプリング方法を使用して、テーブルから選択します。

SELECT MessageId
FROM Messages TABLESAMPLE RESERVOIR (100 ROWS);

BERNOULLI サンプリング方法を使用して、テーブルから選択します。

SELECT MessageId
FROM Messages TABLESAMPLE BERNOULLI (0.1 PERCENT);

サブクエリで TABLESAMPLE を使用します。

SELECT Subject FROM
(SELECT MessageId, Subject FROM Messages WHERE ServerId="test")
TABLESAMPLE BERNOULLI(50 PERCENT)
WHERE MessageId > 3;

別のテーブルと結合する際には、TABLESAMPLE オペレーションを使用します。

SELECT S.Subject
FROM
(SELECT MessageId, ThreadId FROM Messages WHERE ServerId="test") AS R
TABLESAMPLE RESERVOIR(5 ROWS),
Threads AS S
WHERE S.ServerId="test" AND R.ThreadId = S.ThreadId;

エイリアス

FROM 句のエイリアスの構文と可視性については、エイリアスの使用をご覧ください。

JOIN のタイプ

構文

join:
    from_item [ join_type ] [ join_method ] JOIN  [ join_hint_expr ] from_item
    [ ON bool_expression | USING ( join_column [, ...] ) ]

join_type:
    { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

join_method:
{ HASH }
join_hint_expr: '@{' join_hint_key = join_hint_value [, ...] '}' join_hint_key: { FORCE_JOIN_ORDER | JOIN_METHOD }

JOIN 句は 2 つの from_item を結合して、SELECT 句でそれらを 1 つのソースとしてクエリできるようにします。join_typeON または USING 句(「結合条件」)によって、2 つの from_item で行の結合と廃棄を行い、1 つのソースを形成する方法が指定されます。

JOIN 句には、join_type が必須です。

JOIN 句は、次の条件の 1 つが true でない限り、結合条件を必要とします。

  • join_typeCROSS です。
  • from_item の 1 つまたは両方がテーブルではない(array_pathfield_path など)。

JOIN のヒント

JOIN で使用できるヒントは次のとおりです。

ヒントキー 指定できる値 説明
FORCE_JOIN_ORDER TRUE
FALSE(デフォルト)
true に設定されている場合は、クエリに指定された結合順序を使用します。
JOIN_METHOD HASH_JOIN
APPLY_JOIN
論理結合を実装する場合には、基になる結合メソッドに使用する特定の選択肢を選択します。詳細については、結合メソッドをご覧ください。
ハッシュ結合を使用するには、HASH JOIN または JOIN@{JOIN_METHOD=HASH_JOIN} を使用します。両方は使用できません。
HASH_JOIN_BUILD_SIDE BUILD_LEFT
BUILD_RIGHT
ハッシュ結合のどちら側をビルド側として使用するかを指定します。JOIN_METHOD=HASH_JOIN でのみ使用できます。
BATCH_MODE TRUE (default)
FALSE
バッチ適用結合を無効にして一度に 1 行ずつの適用結合を使用する場合に使用します。JOIN_METHOD=APPLY_JOIN でのみ使用できます。

結合メソッド

結合メソッドとは、さまざまな論理結合タイプの特定の実装です。一部の結合メソッドは、特定の結合タイプでのみ使用できます。使用する結合メソッドは、クエリと、クエリで照会するデータによって決まります。特定の結合メソッドでクエリのパフォーマンスが向上するかどうかを確認するには、結合メソッドを試行してクエリ実行プランを確認することをおすすめします。詳しくは、クエリ実行演算子をご覧ください。特に、適用演算子とハッシュ結合演算子の説明をご確認ください。

結合メソッド 説明 オペランド
HASH_JOIN ハッシュ結合演算子は、一方の側(ビルド側)からハッシュ テーブルを作成し、もう一方の側(プローブ側)のすべての要素についてハッシュ テーブルを調べます。 さまざまな結合タイプに対して異なるバリアントが使用されます。クエリのクエリ実行プランを表示して、使用されているバリアントを確認します。詳細については、ハッシュ結合演算子をご覧ください。
APPLY_JOIN 適用結合演算子は、一方の側(入力側)から各項目を取得し、入力側からの項目の値を使用してもう一方の側(マップ側)でサブクエリを評価します。 さまざまな結合タイプに対して異なるバリアントが使用されます。クロス適用は内部結合に使用され、外部適用は左結合に使用されます。詳細については、クロス適用演算子と外部適用演算子をご覧ください。

[INNER] JOIN

INNER JOIN または単に JOIN は、2 つの from_item のデカルト積を効率的に計算し、結合条件を満たさないすべての行を廃棄します。「効率的に」とは、実際にデカルト積を計算せずに、INNER JOIN を実行することができることを意味します。

FROM A INNER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | d |     | 2 | b | 2 | d |
| 2 | b |     | 3 | e |     | 3 | c | 3 | e |
| 3 | c |     | 4 | f |     +---------------+
+-------+     +-------+
FROM A INNER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +-----------+
| x | y |  *  | x | z |  =  | x | y | z |
+-------+     +-------+     +-----------+
| 1 | a |     | 2 | d |     | 2 | b | d |
| 2 | b |     | 3 | e |     | 3 | c | e |
| 3 | c |     | 4 | f |     +-----------+
+-------+     +-------+

このクエリは、Roster テーブルと TeamMascot テーブルに対して INNER JOIN を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
+---------------------------+

CROSS JOIN

CROSS JOIN は、2 つの from_item のデカルト積を返します。言い換えれば、最初の from_item の各行を 2 番目の from_item の各行と結合します。

2 つの from_item の行が独立している場合、結果は M * N 行となります(1 つの from_item で M 行、他方で N 行を指定)。これは、いずれかの from_item がゼロ行の場合にも当てはまります。

FROM A CROSS JOIN B

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | c |     | 1 | a | 2 | c |
| 2 | b |     | 3 | d |     | 1 | a | 3 | d |
+-------+     +-------+     | 2 | b | 2 | c |
                            | 2 | b | 3 | d |
                            +---------------+

相関 CROSS JOIN を使用すると、ARRAY 列を平坦化できます。この場合、2番目の from_item の各行が最初の from_item の各行で異なります。

FROM A CROSS JOIN A.y

Table A                    Result
+-------------------+      +-----------+
| w | x | y         |  ->  | w | x | y |
+-------------------+      +-----------+
| 1 | a | [P, Q]    |      | 1 | a | P |
| 2 | b | [R, S, T] |      | 1 | a | Q |
+-------------------+      | 2 | b | R |
                           | 2 | b | S |
                           | 2 | b | T |
                           +-----------+

CROSS JOIN は、次のように明示的に記述できます。

FROM a CROSS JOIN b

または、次のように暗黙的にカンマクロス結合として記述できます。

FROM a, b

丸括弧内に、コンマクロス結合を記述できません。

FROM a CROSS JOIN (b, c)  // INVALID

コンマクロス結合が JOIN の順序でどのように動作するかについては、JOIN の順序をご覧ください。

このクエリは、Roster テーブルと TeamMascot テーブルに対して、明示的に CROSS JOIN を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Adams      | Knights      |
| Adams      | Lakers       |
| Adams      | Mustangs     |
| Buchanan   | Jaguars      |
| Buchanan   | Knights      |
| Buchanan   | Lakers       |
| Buchanan   | Mustangs     |
| ...                       |
+---------------------------+

このクエリは、上記の明示的な CROSS JOIN と同じ結果を生成するコンマクロス結合を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster, TeamMascot;

FULL [OUTER] JOIN

FULL OUTER JOIN(または単に FULL JOIN)は、結合条件を満たす両方の from_item 内のすべての行のすべてのフィールドを返します。

FULL は、結合条件を満たさない場合でも、両方の from_item からすべての行が返されるように指定します。

OUTER は 1 つの from_item の指定された行が他の from_item の任意の行に結合しない場合、行が他の from_item からのすべての列に対して NULL を返すことを示します。

FROM A FULL OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | d |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | e |     | 2    | b    | 2    | d    |
| 3 | c |     | 4 | f |     | 3    | c    | 3    | e    |
+-------+     +-------+     | NULL | NULL | 4    | f    |
                            +---------------------------+
FROM A FULL OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | d |     | 1    | a    | NULL |
| 2 | b |     | 3 | e |     | 2    | b    | d    |
| 3 | c |     | 4 | f |     | 3    | c    | e    |
+-------+     +-------+     | 4    | NULL | f    |
                            +--------------------+

このクエリは、Roster テーブルと TeamMascot テーブルに対して FULL JOIN を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster FULL JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| Eisenhower | NULL         |
| NULL       | Mustangs     |
+---------------------------+

LEFT [OUTER] JOIN

2 つの from_item に対する LEFT OUTER JOIN(または単に LEFT JOIN)の結果は、右側の from_item に結合の述語を満たす行がない場合でも、常に JOIN 句の左側の from_item のすべての行を保持します。

LEFT は、左側の from_item からすべての行が返されることを示します。左側の from_item のいずれかの行が右側の from_item のどの行とも結合しない場合、行は右側の from_item のすべての列に対して NULL を返します。左側の from_item のどの行にも結合されない右側の from_item の行は破棄されます。

FROM A LEFT OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | d |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | e |     | 2    | b    | 2    | d    |
| 3 | c |     | 4 | f |     | 3    | c    | 3    | e    |
+-------+     +-------+     +---------------------------+
FROM A LEFT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | d |     | 1    | a    | NULL |
| 2 | b |     | 3 | e |     | 2    | b    | d    |
| 3 | c |     | 4 | f |     | 3    | c    | e    |
+-------+     +-------+     +--------------------+

このクエリは、Roster テーブルと TeamMascot テーブルに対して LEFT JOIN を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster LEFT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| Eisenhower | NULL         |
+---------------------------+

RIGHT [OUTER] JOIN

RIGHT OUTER JOIN(または単に RIGHT JOIN)の結果は、LEFT OUTER JOIN の結果と同様になり、対称的になります。

FROM A RIGHT OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | d |     | 2    | b    | 2    | d    |
| 2 | b |     | 3 | e |     | 3    | c    | 3    | e    |
| 3 | c |     | 4 | f |     | NULL | NULL | 4    | f    |
+-------+     +-------+     +---------------------------+
FROM A RIGHT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | d |     | 2    | b    | d    |
| 2 | b |     | 3 | e |     | 3    | c    | e    |
| 3 | c |     | 4 | f |     | 4    | NULL | f    |
+-------+     +-------+     +--------------------+

このクエリは、Roster テーブルと TeamMascot テーブルに対して RIGHT JOIN を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster RIGHT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| NULL       | Mustangs     |
+---------------------------+

ON 句

ON 句には bool_expression が含まれます。bool_expression が TRUE を返した場合、結合された行(2 つの行を結合した結果)は、結合条件を満たします。

FROM A JOIN B ON A.x = B.x

Table A   Table B   Result (A.x, B.x)
+---+     +---+     +-------+
| x |  *  | x |  =  | x | x |
+---+     +---+     +-------+
| 1 |     | 2 |     | 2 | 2 |
| 2 |     | 3 |     | 3 | 3 |
| 3 |     | 4 |     +-------+
+---+     +---+

このクエリは、Roster テーブルと TeamMascot テーブルに対して INNER JOIN を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
+---------------------------+

USING 句

USING 句には、両方の入力テーブルに出現する 1 つ以上の列からなる column_list が必要です。その列で等価比較を実行し、等価比較で TRUE が返された場合、その行は結合条件を満たします。

FROM A JOIN B USING (x)

Table A   Table B   Result
+---+     +---+     +---+
| x |  *  | x |  =  | x |
+---+     +---+     +---+
| 1 |     | 2 |     | 2 |
| 2 |     | 3 |     | 3 |
| 3 |     | 4 |     +---+
+---+     +---+

このクエリは、Roster テーブルと TeamMascot テーブルに対して INNER JOIN を実行します。

このステートメントは、Roster.SchooldIDTeamMascot.SchooldID と同じ場合に RosterTeamMascot から行を返します。結果には、単一の SchooldID 列が含まれます。

SELECT * FROM Roster INNER JOIN TeamMascot USING (SchoolID);

+----------------------------------------+
| SchoolID   | LastName   | Mascot       |
+----------------------------------------+
| 50         | Adams      | Jaguars      |
| 52         | Buchanan   | Lakers       |
| 52         | Coolidge   | Lakers       |
| 51         | Davis      | Knights      |
+----------------------------------------+

ON と USING の同等性

キーワードの ONUSING は、同等ではありませんが、類似しています。ON は複数の列が返され、USING は 1 列が返されます。

FROM A JOIN B ON A.x = B.x
FROM A JOIN B USING (x)

Table A   Table B   Result ON     Result USING
+---+     +---+     +-------+     +---+
| x |  *  | x |  =  | x | x |     | x |
+---+     +---+     +-------+     +---+
| 1 |     | 2 |     | 2 | 2 |     | 2 |
| 2 |     | 3 |     | 3 | 3 |     | 3 |
| 3 |     | 4 |     +-------+     +---+
+---+     +---+

ONUSING は同等ではありませんが、返す列を指定すると同じ結果が返されます。

SELECT x FROM A JOIN B USING (x);
SELECT A.x FROM A JOIN B ON A.x = B.x;

Table A   Table B   Result
+---+     +---+     +---+
| x |  *  | x |  =  | x |
+---+     +---+     +---+
| 1 |     | 2 |     | 2 |
| 2 |     | 3 |     | 3 |
| 3 |     | 4 |     +---+
+---+     +---+

JOIN の順序

FROM 句には、複数の JOIN 句を続けて含めることができます。JOIN は左から右にバインドされます。例:

FROM A JOIN B USING (x) JOIN C USING (x)

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

丸括弧を挿入して JOIN をグループ化することもできます。

FROM ( (A JOIN B USING (x)) JOIN C USING (x) )

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

丸括弧を使用すると、異なる順序でバインドされるように JOIN をグループ化できます。

FROM ( A JOIN (B JOIN C USING (x)) USING (x) )

-- B JOIN C USING (x)       = result_1
-- A JOIN result_1          = result_2
-- result_2                 = return value

コンマクロス結合が JOIN の順序でクエリにある場合、それらは他の JOIN データ型と同様に、左から右にグループ化されます。

FROM A JOIN B USING (x) JOIN C USING (x), D

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 CROSS JOIN D     = return value

コンマ結合の後に RIGHT JOIN または FULL JOIN があってはなりません。

FROM A, B RIGHT JOIN C ON TRUE // INVALID
FROM A, B FULL JOIN C ON TRUE  // INVALID
FROM A, B JOIN C ON TRUE       // VALID

WHERE 句

構文

WHERE bool_expression

WHERE 句は各行を bool_expression に照らして評価することで、行をフィルタで除外して、TRUE を返さないすべての行(FALSE または NULL を返す行)を破棄します。

例:

SELECT * FROM Roster
WHERE SchoolID = 52;

bool_expression には、複数の従属条件を含めることができます。

例:

SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");

WHERE 句の SELECT リストから列のエイリアスを参照できません。

INNER JOIN の式には、WHERE 句に同等の式があります。たとえば、INNER JOINON を使用するクエリには、CROSS JOINWHERE を使用する同等の式があります。

次のクエリがあるとします。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

これは、次のステートメントと同等になります。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;

GROUP BY 句

構文

GROUP BY expression [, ...]

GROUP BY 句は、GROUP BY 句にある expression の値が同じ行をグループ化し、1 つのテーブルにまとめます。ソーステーブルのうち、expression の値が同じ複数の行に対して、GROUP BY 句は 1 つの結合行を生成します。一般に GROUP BY は、集計関数が SELECT リストに存在する場合に、または出力の冗長性を排除する場合に使用されます。expression のデータ型は Groupable である必要があります。

例:

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName;

GROUP BY 句は SELECT リストで式の名前を参照できます。また GROUP BY 句では、整数値を使用して SELECT リスト内の式をその順序で参照できます。1SELECT リストの最初の式、2 は 2 番目の式というように参照します。式リストでは、順序数と式名を組み合わせることができます。

例:

SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY LastName, FirstName;

上記のクエリは、次のものと同等です。

SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY 2, FirstName;

GROUP BY 句もまた、エイリアスを参照する場合があります。クエリの SELECT 句にエイリアスが含まれる場合、これらのエイリアスは、対応する FROM 句の名前をオーバーライドします。

例:

SELECT SUM(PointsScored), LastName as last_name
FROM PlayerStats
GROUP BY last_name;

HAVING 句

構文

HAVING bool_expression

HAVING 句は、WHERE と似ています。bool_expression に照らした評価の際に TRUE を返さない行をフィルタで除外します。

WHERE 句の場合と同様、bool_expression はブール値を返す任意の式で、複数の従属条件を含めることができます。

HAVING 句は次の点で、WHERE 句と異なります。

  • HAVING 句では、クエリに GROUP BY または集計が必要です。
  • HAVING 句は、GROUP BY と集計の後、ORDER BY の前に置かれます。つまり、結果セットの集計行ごとに HAVING 句が 1 回評価されます。この点は、GROUP BY と集計の前に評価される WHERE 句とは異なります。

HAVING 句は FROM 句のみならず SELECT リスト エイリアス経由でも使用できる列を参照できます。HAVING で参照される式は、GROUP BY 句に出現するか、または集計関数の結果である必要があります。

SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

クエリに SELECT 句のエイリアスが含まれる場合、これらのエイリアスは FROM 句の名前をオーバーライドします。

SELECT LastName, SUM(PointsScored) AS ps
FROM Roster
GROUP BY LastName
HAVING ps > 0;

必須の集計

集計は HAVING 句自体に存在する必要はありませんが、集計は以下の形式の少なくとも 1 つで存在しなければなりません。

SELECT リストの集計関数

SELECT LastName, SUM(PointsScored) AS total
FROM PlayerStats
GROUP BY LastName
HAVING total > 15;

HAVING 句の集計関数。

SELECT LastName
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

SELECT リストと HAVING 句の両方の集計

集計関数が SELECT リストと HAVING 句の両方に存在する場合、集計関数とそれらが参照する列は同じである必要がありません。以下の例では、2 つの集計関数 COUNT()SUM() は異なり、列も異なるものを使用します。

SELECT LastName, COUNT(*)
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

ORDER BY 句

構文

ORDER BY expression
  [{ ASC | DESC }]
  [, ...]

ORDER BY 句は結果セットの並べ替え基準として列または式を指定します。ORDER BY 句が存在しない場合、クエリ結果の順序は定義されません。FROM 句からの列のエイリアスまたは SELECT リストを使用できます。クエリの SELECT 句にエイリアスが含まれる場合、これらのエイリアスは、対応する FROM 句の名前をオーバーライドします。

省略可能な句

  • ASC | DESC: 結果を、expression の値の昇順または降順に並べ替えます。ASC がデフォルト値です。

デフォルトの並べ替え順序(昇順)を使用します。

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true)
ORDER BY x;
+------+-------+
| x    | y     |
+------+-------+
| 1    | true  |
| 9    | true  |
+------+-------+

降順で並べ替えます。

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true)
ORDER BY x DESC;
+------+-------+
| x    | y     |
+------+-------+
| 9    | true  |
| 1    | true  |
+------+-------+

複数の列を基準にした並べ替えを行うことができます。下の例では、結果セットは最初に SchoolID で、次に LastName で並べ替えられます。

SELECT LastName, PointsScored, OpponentID
FROM PlayerStats
ORDER BY SchoolID, LastName;

以下のルールは、値を順番に並べるときに適用されます。

  • NULL: ORDER BY 句のコンテキストでは、NULL が最小可能値になります。つまり、ASC での並べ替えでは NULL が最初になり、DESC による並べ替えでは最後になります。
  • 浮動小数点データ型の場合の順序とグループ化については、浮動小数点のセマンティクスをご覧ください。

集合演算子と組み合わせて使用すると、ORDER BY 句はクエリ全体の結果セットに適用されます。最も近くにある SELECT ステートメントだけに適用されるわけではありません。このため、括弧を使用して ORDER BY の範囲を示すと便利です(ただし、必須ではありません)。

括弧なしの次のクエリがあるとします。

SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot
ORDER BY SchoolID;

これは、括弧ありのこのクエリと同等です。

( SELECT * FROM Roster
  UNION ALL
  SELECT * FROM TeamMascot )
ORDER BY SchoolID;

ただし、ORDER BY 句が 2 番目の SELECT ステートメントにのみ適用される次のクエリとは同等ではありません。

SELECT * FROM Roster
UNION ALL
( SELECT * FROM TeamMascot
  ORDER BY SchoolID );

ORDER BY 句では、整数リテラルを列参照として使用することもできます。整数リテラルは SELECT リストの順序となります(例: カウントは 1 から開始)。

例 - 次の 2 つのクエリは同等です。

SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY LastName;
SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY 2;

COLLATE

COLLATE 句を使用すると、ORDER BY 句からのデータの順序付け方法を絞り込むことが可能です。Collation とは、特定の言語、リージョン、国の規則と標準に従って、文字列の比較方法を指定する一連のルールのことです。たとえば、大文字と小文字を区別しないという指定のオプションにより、正しい文字の順序を定義するルールです。

注: COLLATE は文字列型の列にのみ使用できます。

次のように、collation をステートメントに追加します。

SELECT ...
FROM ...
ORDER BY value COLLATE collation_string

collation_string には collation_name が含まれ、さらにオプションとして collation_attribute をコロン区切りの接尾辞として付けることができます。collation_string にはリテラルかパラメータを指定します。通常は、言語を表す 2 文字に続いて、オプションでアンダースコアとリージョンを表す 2 文字を指定します(たとえば、en_US)。これらの名前は、Common Locale Data Repository(CLDR)で定義されています。また、ステートメントの unicode には collation_name を指定することもできます。この値は、Unicode のデフォルトの照合でデータを返すことを意味します。

collation_name に加えて、collation_string にはオプションとして collation_attribute をコロン区切りの接尾辞として付けることができます。属性により、データ比較で大文字と小文字を区別するかどうかを指定します。使用できる値は、cs(大文字と小文字を区別する)と ci(大文字と小文字を区別しない)です。collation_attribute が指定されない場合、CLDR のデフォルト値が使用されます。

COLLATE の例

次の例では、English - Canada を使用して結果を照合しています。

SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_CA"

次の例では、パラメータを使用して結果を照合しています。

#@collate_param = "arg_EG"
SELECT Place
FROM Locations
ORDER BY Place COLLATE @collate_param

次の例では、ステートメント内で複数の COLLATE 句を使用しています。

SELECT APlace, BPlace, CPlace
FROM Locations
ORDER BY APlace COLLATE "en_US" ASC,
         BPlace COLLATE "ar_EG" DESC,
         CPlace COLLATE "en" DESC

次の例は、大文字と小文字を区別しない照合です。

SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_US:ci"

次の例は、Unicode の大文字と小文字を区別しないデフォルトの照合です。

SELECT Place
FROM Locations
ORDER BY Place COLLATE "unicode:ci"

集合演算子

構文

UNION { ALL | DISTINCT } | INTERSECT { ALL | DISTINCT } | EXCEPT { ALL | DISTINCT }

集合演算子は、2 つ以上の入力クエリの結果を 1 つの結果セットに結合します。ALL または DISTINCT を指定する必要があります。ALL を指定する場合、すべての行が保留されます。DISTINCT が指定された場合、重複する行は廃棄されます。

指定された行 R が最初の入力クエリで正確に m 回表示され、2 番目の入力クエリで n 回表示される場合(m >= 0、n >= 0)は次のようになります。

  • UNION ALL の場合、R は結果にちょうど m + n 回だけ表示されます。
  • INTERSECT ALL の場合、R は結果で正確に「MIN(m, n)」回表示されます。
  • EXCEPT ALL の場合、R は結果で正確に「MAX(m - n, 0)」回表示されます。
  • UNION DISTINCT の場合、DISTINCTUNION が計算された後に計算されるため、R は 1 回だけ表示されます。
  • INTERSECT DISTINCT の場合、上記の結果が計算された後に DISTINCT が計算されます。
  • EXCEPT DISTINCT の場合、m> 0 かつ n = 0 の場合に行 R が出力に 1 回表示されます。
  • 2 つ以上の入力クエリがある場合、上記のオペレーションは概算され、出力は入力が左から右へ結合されて漸増する場合と同じになります。

以下のルールが適用されます。

  • UNION ALL 以外の set オペレーションの場合、すべての列の型で等価比較がサポートされている必要があります。
  • 演算子の左右の入力クエリが、同じ数の列を返さなければなりません。
  • 演算子は、該当する SELECT リストの列の位置に従って、各入力クエリにより返される列をペアリングします。つまり、最初の入力クエリの最初の列は、2 番目の入力クエリの最初の列とペアリングされます。
  • 結果セットは常に、最初の入力クエリの列名を使用します。
  • 結果セットは常に対応する列の入力データ型のスーパーデータ型を使用するため、ペアリングした列もまた同じデータ型であるか、共通のスーパーデータ型でなければなりません。
  • UNION ALLUNION DISTINCT などの異なる set オペレーションの処理を行う場合は、括弧を使用してそれらを区切る必要があります。ステートメントが同じ set オペレーションを繰り返すだけであれば、括弧は必要ありません。

例:

query1 UNION ALL (query2 UNION DISTINCT query3)
query1 UNION ALL query2 UNION ALL query3

無効:

query1 UNION ALL query2 UNION DISTINCT query3
query1 UNION ALL query2 INTERSECT ALL query3;  // INVALID.

UNION

UNION 演算子は各クエリの結果セットの列を組み合わせて、縦方向に連結させることにより、2 つ以上の入力クエリの結果セットを結合します。

INTERSECT

INTERSECT 演算子は、左右の入力クエリの結果セットにある行を戻します。EXCEPT とは異なり、入力クエリの配置(INTERSECT 演算子の左辺と右辺)は重要ではありません。

EXCEPT

EXCEPT 演算子は、左側の入力クエリに存在し、右側の入力クエリには存在しない行を返します。

例:

SELECT * FROM UNNEST(ARRAY<int64>[1, 2, 3]) AS number
EXCEPT DISTINCT SELECT 1;

+--------+
| number |
+--------+
| 2      |
| 3      |
+--------+

LIMIT 句と OFFSET 句

構文

LIMIT count [ OFFSET skip_rows ]

LIMIT はデータ型 INT64 の負ではない count を指定し、count を超えない行数が返されます。LIMIT 0 で返される行数は 0 です。

set オペレーションが存在する場合、set オペレーションを評価してから LIMIT が適用されます。

OFFSET は、LIMIT を適用する前にスキップする行数を示す 0 以上の数を指定します。skip_rows のデータ型は INT64 です。

これらの句はリテラルまたはパラメータの値のみを受け付けます。LIMITOFFSET により返される行は、これらの演算子が ORDER BY の後に使用されない限り、未指定になります。

例:

SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 2

+---------+
| letter  |
+---------+
| a       |
| b       |
+---------+
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 3 OFFSET 1

+---------+
| letter  |
+---------+
| b       |
| c       |
| d       |
+---------+

WITH 句

WITH 句は 1 つ以上の名前付きサブクエリの結果を一時テーブル名にバインドします。導入された各テーブル名は、同じクエリ式内の後続の SELECT 式で表示されます。これには次の種類の SELECT 式が含まれます。

  • 後続の WITH バインディングの SELECT
  • UNION などの集合演算子の両側にあるクエリ式内のトップレベルの SELECT
  • 同じクエリ式内のサブクエリ内の SELECT

例:

WITH subQ1 AS (SELECT SchoolID FROM Roster),
     subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2;

WITH は、サブクエリではサポートされていません。以下の構文では、エラーが返されます。

SELECT account
FROM (
  WITH result AS (SELECT * FROM NPCs)
  SELECT *
  FROM result);

WITH RECURSIVE はサポートされていません。

WITH 句は DML ステートメントではサポートされていません。

WITH 句で定義された一時テーブルは、メモリに格納されます。 Cloud Spanner SQL は、クエリによって作成されるすべての一時テーブルのメモリを動的に割り当てます。使用可能なリソースが十分でない場合、クエリは失敗します。

エイリアスの使用

エイリアスはクエリで示されるテーブル、列、または式に対して与えられる一時的な名前です。SELECT リストまたは FROM 句で明示的なエイリアスを導入できます。あるいは、Cloud Spanner SQL が一部の式に対して暗黙のエイリアスを推測します。 明示的なエイリアスも暗黙のエイリアスもない式は匿名であり、クエリではこれらを名前で参照できません。

明示的なエイリアスの構文

FROM 句または SELECT リストのいずれかで、明示的なエイリアスを導入できます。

FROM 句では、[AS] alias を使用してテーブル、配列、サブクエリ、UNNEST 句を含む項目の明示的なエイリアスを使用できます。AS キーワードは任意です。

例:

SELECT s.FirstName, s2.SongName
FROM Singers AS s, (SELECT * FROM Songs) AS s2;

[AS] alias を使用して SELECT リストの式に対して明示的なエイリアスを使用できます。AS キーワードは任意です。

例:

SELECT s.FirstName AS name, LOWER(s.FirstName) AS lname
FROM Singers s;

明示的なエイリアスの可視性

クエリに明示的なエイリアスを導入した後で、そのエイリアスを参照できるクエリの他の場所について制限があります。エイリアスの可視性に対するこれらの制限は、Cloud Spanner SQL の名前範囲のルールの結果です。

FROM 句エイリアス

Cloud Spanner SQL は、FROM 句のエイリアスを左から右の順で処理します。エイリアスは、FROM 句でその後に続くパス式に対してのみ可視です。

例:

Singers テーブルに ARRAY データ型の Concerts 列があることを想定してください。

SELECT FirstName
FROM Singers AS s, s.Concerts;

無効:

SELECT FirstName
FROM s.Concerts, Singers AS s;  // INVALID.

FROM 句のエイリアスは、同じ FROM 句内のサブクエリでは認識されませんFROM 句のサブクエリには、同じ FROM 句内のその他のテーブルへの相関参照を含めることはできません。

無効:

SELECT FirstName
FROM Singers AS s, (SELECT (2020 - ReleaseDate) FROM s)  // INVALID.

テーブル名による修飾の有無に関係なく、FROM のテーブルの列名を、クエリの任意の場所でエイリアスとして使用できます。

例:

SELECT FirstName, s.ReleaseDate
FROM Singers s WHERE ReleaseDate = 1975;

FROM 句に明示的なエイリアスが含まれている場合、そのクエリの他の部分では暗黙のエイリアスではなく、明示的なエイリアスを使用する必要があります(暗黙のエイリアスを参照)。テーブル エイリアスは、クエリ処理中に同じテーブルが複数回スキャンされる自己結合の場合などに、簡潔さの実現や曖昧さの排除に役立ちます。

例:

SELECT * FROM Singers as s, Songs as s2
ORDER BY s.LastName

無効 - ORDER BY では、テーブル エイリアスは使用されません。

SELECT * FROM Singers as s, Songs as s2
ORDER BY Singers.LastName;  // INVALID.

SELECT リストのエイリアス

SELECT リスト内のエイリアスは、次の句でのみ認識されます。

  • GROUP BY
  • ORDER BY
  • HAVING

例:

SELECT LastName AS last, SingerID
FROM Singers
ORDER BY last;

GROUP BY、ORDER BY、HAVING 句の明示的なエイリアス

GROUP BYORDER BYHAVING の 3 つの句は、以下の値のみを参照できます。

  • FROM 句のテーブルとその列。
  • SELECT リストのエイリアス。

GROUP BYORDER BY は、3 番目のグループも参照できます。

  • 整数リテラル。これは、SELECT リストの項目を参照します。整数 1 は、SELECT リストの最初の項目、2 は 2 番目の項目を参照、などとなります。

例:

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY 1
ORDER BY 2 DESC;

上記のクエリは、次のものと同等です。

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY sid
ORDER BY s2id DESC;

曖昧なエイリアス

Cloud Spanner SQL は、名前が曖昧である場合、つまり複数の固有のオブジェクトに解決できる場合にエラーとなります。

例:

このクエリには、SingersSongs の両方に SingerID という名前の列があるため、テーブル間で競合する列名が含まれます。

SELECT SingerID
FROM Singers, Songs;

このクエリでは、SELECT リストでエイリアスが重複しているため、GROUP BY 句のエイリアスが曖昧になります。

SELECT FirstName AS name, LastName AS name,
FROM Singers
GROUP BY name;

このクエリでは、名前が重複しているため、SELECT リストと FROM 句のエイリアスが曖昧になります。table に列 xyz があるとします。z は STRUCT 型で、フィールドは vwx です。

例:

SELECT x, z AS T
FROM table AS T
GROUP BY T.x;

エイリアス T が曖昧なためエラーを生成します。これは、GROUP BY 句の T.xtable.xtable.z.x のどちらも参照する可能性があるためです。

基礎となる同じオブジェクトでその名前を解決できる場合、列名と SELECT リストのエイリアスの両方で特定の名前が使用されていても、GROUP BYORDER BYHAVING でその名前は曖昧とはなりません

例:

SELECT LastName, BirthYear AS BirthYear
FROM Singers
GROUP BY BirthYear;

エイリアス BirthYear は、同じ基礎となる列 Singers.BirthYear に解決されるため曖昧ではありません。

暗黙のエイリアス

明示的エイリアスが指定されていない式が SELECT リストにある場合、Cloud Spanner SQL は次のルールに従って暗黙のエイリアスを割り当てます。このルールによって、SELECT リストで複数の列に同じエイリアスが割り当てられる場合があります。

  • 識別子の場合、エイリアスは識別子です。たとえば、SELECT abcAS abc を含意します。
  • パス式の場合、エイリアスはパスの最後の識別子です。たとえば、SELECT abc.def.ghiAS ghi を含意します。
  • 「ドット」メンバー フィールド アクセス演算子を使用するフィールド アクセスの場合、エイリアスはフィールド名になります。たとえば、SELECT (struct_function()).fnameAS fname を含意します。

他のすべての事例では暗黙のエイリアスはないので、列は匿名で、名前では参照できません。その列からのデータは引き続き返され、表示されるクエリの結果にはその列に対して生成されたラベルがある場合がありますが、ラベルはエイリアスのようには使用できません。

FROM 句では、from_item はエイリアスが必要なわけではありません。以下のルールが適用されます。

  • 明示的エイリアスが指定されていない式では、Cloud Spanner SQL は以下の場合に暗黙のエイリアスを割り当てます。
    • 識別子の場合、エイリアスは識別子です。たとえば、FROM abcAS abc を含意します。
    • パス式の場合、エイリアスはパスの最後の識別子です。たとえば、FROM abc.def.ghiAS ghi を含意します。
    • WITH OFFSET を使用して生成された列には、暗黙のエイリアス offset があります。

  • テーブルのサブクエリに、暗黙のエイリアスはありません。
  • FROM UNNEST(x) に暗黙のエイリアスはありません。

付録 A: 例とサンプルデータ

以下の例には、Roster テーブルと TeamMascot テーブル、PlayerStats テーブルに対してクエリを実行するステートメントが含まれています。

GROUP BY 句

例:

SELECT LastName, SUM(PointsScored)
FROM PlayerStats
GROUP BY LastName;
LastName SUM
Adams 7
Buchanan 13
Coolidge 1

集合演算子

UNION

UNION 演算子は各 SELECT ステートメントの結果セットの列をペアリングし、縦方向に連結させることにより、2 つ以上の SELECT ステートメントの結果セットを結合します。

例:

SELECT Mascot AS X, SchoolID AS Y
FROM TeamMascot
UNION ALL
SELECT LastName, PointsScored
FROM PlayerStats;

結果:

X Y
Jaguars 50
Knights 51
Lakers 52
Mustangs 53
Adams 3
Buchanan 0
Coolidge 1
Adams 4
Buchanan 13

INTERSECT

このクエリは、Roster と PlayerStats の両方に存在する姓を返します。

SELECT LastName
FROM Roster
INTERSECT ALL
SELECT LastName
FROM PlayerStats;

結果:

LastName
Adams
Coolidge
Buchanan

EXCEPT

次のクエリは、PlayerStats に存在していない Roster の姓を返します。

SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;

結果:

LastName
Eisenhower
Davis

SELECT ステートメントの順序を逆にすると、Roster に存在していない PlayerStats の姓が返されます。

SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;

結果:

(empty)