クエリ ステートメントは、1 つ以上のテーブルまたは式をスキャンし、計算結果の行を返します。このトピックでは、BigQuery での SQL クエリの構文について説明します。
SQL 構文
query_statement: query_expr 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 [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...] [ FROM from_item [, ...] ] [ WHERE bool_expression ] [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ] [ HAVING bool_expression ] [ WINDOW window_name AS ( window_definition ) [, ...] ] set_op: UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT from_item: { table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ] | join | ( query_expr ) [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ [ AS ] alias ] } join: from_item [ join_type ] JOIN from_item [ { ON bool_expression | USING ( join_column [, ...] ) } ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
表記:
- 角括弧「[ ]」はオプションの句です。
- 丸括弧「( )」はリテラルの括弧を表します。
- 縦線「|」は論理 OR を表します。
- 波括弧「{ }」ではオプションのセットを囲みます。
- 角括弧内の省略記号に先立つカンマ「[, ... ]」は、前の項目をカンマで区切ったリストに繰り返すことができることを示しています。
SELECT リスト
構文:
SELECT [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| 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 * EXCEPT
SELECT * EXCEPT
ステートメントは、結果から除外する 1 つ以上の列の名前を指定します。指定された名前と一致する列はすべて出力から除外されます。
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * EXCEPT (order_id)
FROM orders;
+-----------+----------+
| item_name | quantity |
+-----------+----------+
| sprocket | 200 |
+-----------+----------+
注: SELECT * EXCEPT
では、名前を持たない列は除外されません。
SELECT * REPLACE
SELECT * REPLACE
ステートメントでは、expression AS identifier
句を 1 つ以上指定します。個々の ID が SELECT *
ステートメントの列名と一致している必要があります。出力列のリストで、REPLACE
句の ID と一致する列が REPLACE
句の表現で置き換えられます。
SELECT * REPLACE
ステートメントでは、列の名前や順序は変更されません。ただし、値と値の型は変更できます。
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * REPLACE ("widget" AS item_name)
FROM orders;
+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5 | widget | 200 |
+----------+-----------+----------+
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;
+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5 | sprocket | 100 |
+----------+-----------+----------+
注: SELECT * REPLACE
では、名前を持たない列は置き換えられません。
SELECT ALL
SELECT ALL
ステートメントは、重複した行も含めて、すべての行を返します。SELECT ALL
が SELECT
のデフォルト動作です。
値テーブル
BigQuery の値テーブルは、行の型が単一値であるテーブルです。通常のテーブルでは各行が複数の列で構成され、各列には名前と型があります。値テーブルでは行の型が単一値のみであり、列名はありません。
注: BigQuery のクエリでは、型が STRUCT
の値テーブルだけを返すことができます。
1 列だけが返されるクエリを使用する場合、代わりに値テーブルが返されるクエリを使用できます。たとえば、スカラー サブクエリと配列サブクエリ(サブクエリを参照)では、通常は 1 列が返されるクエリを使用する必要がありますが、BigQuery では値テーブルが返されるクエリも使用できます。
クエリで SELECT AS
が使用される場合、次のいずれかの構文を使用すると、値テーブルが生成されます。
SELECT AS STRUCT
構文:
SELECT AS STRUCT expr1 [struct_field_name1] [,... ]
上記の構文を使用すると、行の型が STRUCT である値テーブルが生成されます。この場合、STRUCT フィールドの名前と型は、SELECT
リストで生成される列の名前と型と一致します。匿名の列や重複する列は許容されます。
例:
SELECT AS STRUCT 1 x, 2, 3 x
上のクエリは、STRUCT<int64 x, int64, int64
x>.
型の STRUCT 値を生成します。第 1 フィールドと第 3 フィールドは x
で同じ名前、2 番目のフィールドは匿名になります。
上記の例では、struct コンストラクタを使用した次のクエリと同じ結果が生成されます。
SELECT AS VALUE STRUCT(1 AS x, 2, 3 AS x)
例:
SELECT
ARRAY(SELECT AS STRUCT t.f1, t.f2 WHERE t.f3=true)
FROM
Table t
SELECT AS STRUCT
をスカラー サブクエリや配列サブクエリで使用すると、複数の値をまとめた単一の STRUCT 型を生成できます。通常、スカラー サブクエリと配列サブクエリ(サブクエリを参照)で複数の列を返すことはできません。
SELECT AS VALUE
SELECT AS VALUE
では、1 列だけを生成する SELECT
リストから値テーブルが生成されます。1 列の出力テーブル(名前付きの場合もある)が生成される代わりに、出力として値テーブルが生成されます。この値テーブルの行の型は 1 つの SELECT
列で生成された値の型になります。その列にエイリアスがあっても、値テーブルでは破棄されます。
例:
SELECT AS VALUE STRUCT(1 a, 2 b) xyz FROM Table;
上記のクエリを使用すると、行の型が STRUCT<a int64, b int64>
であるテーブルが生成されます。
エイリアス
SELECT
リストのエイリアスの構文と可視性については、エイリアスをご覧ください。
分析関数
分析関数とそれに関連する OVER
、PARTITION
BY
、WINDOW
などの句については、分析関数のコンセプトをご覧ください。
FROM 句
FROM
句は行を取得する 1 つまたは複数のテーブルを示し、これらの行を結合して、残りのクエリの処理のための単一ストリームの行を生成する方法を指定します。
構文
from_item: { table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ] | join | ( query_expr ) [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ [ AS ] alias ] }
table_name
既存のテーブルの名前(修飾可)。
SELECT * FROM Roster; SELECT * FROM dataset.Roster; SELECT * FROM project.dataset.Roster;
FOR SYSTEM_TIME AS OF
FOR SYSTEM_TIME AS OF
は、timestamp_expression
の時点で最新であったテーブル定義と行の履歴バージョンを参照します。
制限事項:
FOR SYSTEM_TIME AS OF
を含む FROM
句のソーステーブルには、次のいずれも指定できません。
ARRAY
スキャン。フラット化配列、UNNEST
演算子の出力などを含む。WITH
句で定義された共通テーブル式。
timestamp_expression
は、定数式である必要があります。以下を含めることはできません。
- サブクエリ。
- 相関参照(
SELECT
リストなど、より高いレベルのクエリ ステートメントに表示されるテーブルの列への参照)。 - ユーザー定義関数(UDF)。
次の範囲に該当する timestamp_expression
の値は指定できません。
- 現在のタイムスタンプより後(将来)。
- 現在のタイムスタンプより 7 日以上前。
1 つのクエリ ステートメントで、現在の時刻を含めた複数の時点における 1 つのテーブルを参照することはできません。つまり、クエリで同じタイムスタンプで複数回テーブルを参照することはできますが、現在のバージョンと履歴バージョン、または異なる 2 つの履歴バージョンを参照することはできません。
例:
次のクエリは、1 時間前のテーブルの履歴バージョンを返します。
SELECT *
FROM t
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
次のクエリは、絶対的な時刻でのテーブルの履歴バージョンを戻します。
SELECT *
FROM t
FOR SYSTEM_TIME AS OF '2017-01-01 10:00:00-07:00';
次のクエリでは、timestamp_expression
が、同じクエリの別の列に対する相関参照を行っているため、エラーになります。
SELECT *
FROM t1
WHERE t1.a IN (SELECT t2.a
FROM t2 FOR SYSTEM_TIME AS OF t1.timestamp_column);
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 行にしてテーブルを返します。 を FROM
句の外で IN
演算子とともに使用することもできます。
ほとんどの要素型の入力 ARRAY
について、UNNEST
の出力には通常 1 つの列が含まれます。この単一の列にはオプションの alias
があり、これを使用するとクエリの別の場所で列を参照できます。こうした要素型を持つ ARRAYS
は、以下のような複数の列を返します。
- STRUCT
UNNEST
は入力 ARRAY
内の要素の順序を破壊します。配列要素のインデックスを含む 2 番目の列を返すには、オプションの WITH OFFSET
句を使用します(以下をご覧ください)。
STRUCT
の入力 ARRAY
について、UNNEST
は ごとに 1 行を返し、 内の各フィールドについてそれぞれ別個の列が生成されます。各列のエイリアスは、対応する フィールドの名前になります。
例
SELECT *
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')]);
+---+-----+
| x | y |
+---+-----+
| 3 | bar |
| 1 | foo |
+---+-----+
UNNEST
演算子は値テーブルを返すため、 のエイリアスを作成して範囲変数を定義し、それをクエリの他の場所で参照できます。その範囲変数を SELECT
リスト内で参照すると、クエリの結果として、入力テーブルの元の STRUCT
の全フィールドを含む STRUCT
が返されます。
例
SELECT *, struct_value
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')])
AS struct_value;
+---+-----+--------------+
| x | y | struct_value |
+---+-----+--------------+
| 3 | bar | {3, bar} |
| 1 | foo | {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
句の任意の場所から参照できる一時テーブルの名前のように機能します。次の例では、subQ1
と subQ2
は with_query_names
になります。
例:
WITH subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52), subQ2 AS (SELECT SchoolID FROM subQ1) SELECT DISTINCT * FROM subQ2;
WITH
句では、dataset.Roster
や project.dataset.Roster
などのようにテーブル名を修飾している場合を除き、クエリの実施中は同名の永続テーブルを非表示にします。
サブクエリ
サブクエリは別のステートメント内に表示されるクエリで、括弧で囲んで記述します。これらは、「サブ SELECT」または「ネストされた SELECT」とも呼ばれます。完全な SELECT
構文は、サブクエリで有効です。
サブクエリには次の 2 種類があります。
- 式サブクエリ: 式が有効なときにいつでもクエリで使用できます。式サブクエリは、単一の値を返します。
- テーブル サブクエリ:
FROM
句でのみ使用できます。外部クエリは、サブクエリの結果をテーブルとして扱います。
両種のサブクエリはいずれも囲む括弧が必要であることに注意します。
例:
SELECT AVG ( PointsScored )
FROM
( SELECT PointsScored
FROM Stats
WHERE SchoolID = 77 )
オプションで、テーブル サブクエリはエイリアスを持つことができます。
例:
SELECT r.LastName
FROM
( SELECT * FROM Roster) AS r;
エイリアス
FROM
句のエイリアスの構文と可視性については、エイリアスをご覧ください。
JOIN のタイプ
構文
join: from_item [ join_type ] JOIN from_item [ ON bool_expression | USING ( join_column [, ...] ) ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
JOIN
句は 2 つの from_item
を結合して、SELECT
句でそれらを 1 つのソースとしてクエリできるようにします。join_type
と ON
または USING
句(「結合条件」)によって、2 つの from_item
で行の結合と廃棄を行い、1 つのソースを形成する方法が指定されます。
JOIN
句には、join_type
が必須です。
JOIN
句は、次の条件の 1 つが true でない限り、結合条件を必要とします。
join_type
がCROSS
である。from_item
の 1 つまたは両方がテーブルではない(array_path
やfield_path
など)。
[INNER] JOIN
INNER JOIN
または単に JOIN
は、2 つの from_item
のデカルト積を効率的に計算し、結合条件を満たさないすべての行を廃棄します。「効率的に」とは、実際にデカルト積を計算せずに、INNER JOIN
を実現できることを意味します。
CROSS JOIN
CROSS JOIN
は、2 つの from_item
のデカルト積を返します。言い換えると、両方の from_item
のすべての行を維持し、最初の from_item
の各行を 2 番目の from_item
の各行と結合します。
コンマ cross join
CROSS JOIN
は明示的に記述する(すぐ上を参照)ことも、from_item
を区切るためにコンマを使用して暗黙に記述することもできます。
暗黙の「コンマ cross join」の例は、次のとおりです。
SELECT * FROM Roster, TeamMascot;
同等の明示的な cross join は次のとおりです。
SELECT * FROM Roster CROSS JOIN TeamMascot;
括弧内に、コンマ cross join を書くことはできません。
無効 - 括弧内のコンマ cross join。
SELECT * FROM t CROSS JOIN (Roster, TeamMascot); // INVALID.
コンマ cross join が JOIN の順序でどのように動作するかについては、JOIN の順序をご覧ください。
FULL [OUTER] JOIN
FULL OUTER JOIN
(または単に FULL JOIN
)は、結合条件を満たす両方の from_item
内のすべての行のすべてのフィールドを返します。
FULL
は、結合条件を満たさない場合でも、両方の from_item
からすべての行が返されるように指定します。
OUTER
は 1 つの from_item
の指定された行が他の from_item
の任意の行に結合しない場合、行が他の from_item
からのすべての列に対して NULL を返すことを示します。
LEFT [OUTER] JOIN
2 つの from_item
に対する LEFT OUTER JOIN
(または単に LEFT JOIN
)の結果は、右側の に結合の述語を満たす行がない場合でも、常に JOIN
句の左側の のすべての行を保持します。
LEFT
は、左側の from_item
からすべての行が返されることを示します。左側の from_item
のいずれかの行が右側の from_item
のどの行とも結合しない場合、行は右側の from_item
のすべての列に対して NULL を返します。左側の from_item
のどの行にも結合されない右側の from_item
の行は破棄されます。
RIGHT [OUTER] JOIN
RIGHT OUTER JOIN
(または単に RIGHT JOIN
)の結果は、LEFT OUTER JOIN
の結果と同様になり、対称的になります。
ON 句
ON
句には bool_expression
が含まれます。bool_expression
が TRUE を返した場合、結合された行(2 つの行を結合した結果)は、結合条件を満たします。
例:
SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;
USING 句
USING
句には、両方の入力テーブルに出現する 1 つ以上の列からなる column_list
が必要です。その列で等価比較を実行し、等価比較で TRUE が返された場合、その行は結合条件を満たします。
ほとんどの場合、USING
キーワードを持つステートメントは ON
キーワードを使用することと同じ意味を持ちます。たとえば、次のステートメントがあるとします。
SELECT FirstName
FROM Roster INNER JOIN PlayerStats
USING (LastName);
これは次と同等です。
SELECT FirstName
FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;
USING
を使ったクエリの結果は、SELECT *
を使用する際に ON
を使用するクエリとは異なります。これを説明するために、次のクエリを考えてみます。
SELECT * FROM Roster INNER JOIN PlayerStats
USING (LastName);
このステートメントは、Roster.LastName
が PlayerStats.LastName
と同じ場合に Roster
と PlayerStats
から行を返します。結果には、単一の LastName
列が含まれます。
対照的に、次のクエリについて考えてみてください。
SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;
このステートメントは、Roster.LastName
が PlayerStats.LastName
と同じ場合に Roster
と PlayerStats
から行を返します。結果には、Roster
から 1 つと PlayerStats
から 1 つの合計 2 つの LastName
列が含まれます。
JOIN の順序
FROM
句は、順番に複数の JOIN
句を含むことができます。
例:
SELECT * FROM a LEFT JOIN b ON TRUE LEFT JOIN c ON TRUE;
ここで、a
、b
、c
は任意の from_item
です。JOIN は左から右にバインドされますが、括弧を挿入することでそれらを異なる順番でグループ化できます。
次のクエリを考えてみます。A(括弧なし)と B(括弧あり)は互いに同等ですが、C とは異なります。太字の FULL JOIN
が最初にバインドされます。
A.
SELECT * FROM Roster FULL JOIN TeamMascot USING (SchoolID) FULL JOIN PlayerStats USING (LastName);
B.
SELECT * FROM ( (Roster FULL JOIN TeamMascot USING (SchoolID)) FULL JOIN PlayerStats USING (LastName));
C.
SELECT * FROM (Roster FULL JOIN (TeamMascot FULL JOIN PlayerStats USING (LastName)) USING (SchoolID)) ;
コンマ cross join が JOIN の順序でクエリにある場合、それらは他の JOIN
データ型と同様に、左から右にグループ化されます。
例:
SELECT * FROM a JOIN b ON TRUE, b JOIN c ON TRUE;
上記のクエリは、次のものと同様です。
SELECT * FROM ((a JOIN b ON TRUE) CROSS JOIN b) JOIN c ON TRUE);
コンマ JOIN の後に RIGHT JOIN
または FULL JOIN
があってはなりません。
無効 - コンマ CROSS JOIN の後の RIGHT JOIN
SELECT * FROM Roster, TeamMascot RIGHT JOIN PlayerStats ON TRUE; // INVALID.
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
JOIN
と ON
を使用するクエリには、CROSS JOIN
と WHERE
を使用する同等の式があります。
次のクエリがあるとします。
SELECT * FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
これは次と同等です。
SELECT * FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;
GROUP BY 句
構文
GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) }
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
リスト内の式をその順序で参照できます。1
は SELECT
リストの最初の式、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;
GROUP BY ROLLUP
は、ROLLUP
リスト内の式の接頭辞に対する GROUP BY
の結果を返します。各接頭辞はグループ化セットと呼ばれます。ROLLUP
リスト (a, b, c)
の場合、グループ化セットは (a, b, c)
、(a, b)
、(a)
、()
です。特定のグループ化セットの GROUP BY
の結果を評価する場合、GROUP BY ROLLUP
はグループ化セットに含まれない式を NULL
値があるものとして処理します。次のような SELECT
文では、
SELECT a, b, SUM(c) FROM Input GROUP BY ROLLUP(a, b);
ロールアップ リスト (a, b)
が使用されています。結果には、すべての行が含まれるグループ化セット (a, b)
、(a)
、()
の GROUP BY
の結果が含まれます。これによって、次と同じ行が返されます。
SELECT NULL, NULL, SUM(c) FROM Input UNION ALL
SELECT a, NULL, SUM(c) FROM Input GROUP BY a UNION ALL
SELECT a, b, SUM(c) FROM Input GROUP BY a, b;
これにより、ROLLUP
リスト内の式とそのリストの接頭辞によって定義されたグループ化セットの集計の計算が可能になります。
例:
WITH Sales AS (
SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
SELECT 123, 1, 8.99 UNION ALL
SELECT 456, 1, 4.56 UNION ALL
SELECT 123, 2, 9.99 UNION ALL
SELECT 789, 3, 1.00 UNION ALL
SELECT 456, 3, 4.25 UNION ALL
SELECT 789, 3, 0.99
)
SELECT
day,
SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(day);
上記のクエリは、すべての日のロールアップされた合計に加えて、NULL
日と示されている各日に対する行を出力します。
+------+-------+
| day | total |
+------+-------+
| NULL | 39.77 |
| 1 | 23.54 |
| 2 | 9.99 |
| 3 | 6.24 |
+------+-------+
例:
WITH Sales AS (
SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
SELECT 123, 1, 8.99 UNION ALL
SELECT 456, 1, 4.56 UNION ALL
SELECT 123, 2, 9.99 UNION ALL
SELECT 789, 3, 1.00 UNION ALL
SELECT 456, 3, 4.25 UNION ALL
SELECT 789, 3, 0.99
)
SELECT
sku,
day,
SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(sku, day)
ORDER BY sku, day;
上記のクエリは、次のグループ化セットによってグループ化された行を返します。
- sku と day
- sku(day は
NULL
) - 空のグループ化セット(day と sku が
NULL
)
これらのグループ化セットの合計は、sku と day の組み合わせごとの合計、すべての日の各 sku の合計、および総計に対応しています。
+------+------+-------+
| sku | day | total |
+------+------+-------+
| NULL | NULL | 39.77 |
| 123 | NULL | 28.97 |
| 123 | 1 | 18.98 |
| 123 | 2 | 9.99 |
| 456 | NULL | 8.81 |
| 456 | 1 | 4.56 |
| 456 | 3 | 4.25 |
| 789 | 3 | 1.99 |
| 789 | NULL | 1.99 |
+------+------+-------+
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;
集合演算子
構文
UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT
集合演算子は、2 つ以上の入力クエリの結果を 1 つの結果セットに結合します。ALL
または DISTINCT
を指定します。ALL
を指定する場合、すべての行が保留されます。DISTINCT
が指定された場合、重複する行は廃棄されます。
指定された行 R が最初の入力クエリで正確に m 回表示され、2 番目の入力クエリで n 回表示される場合(m >= 0, n >= 0):
UNION ALL
の場合、R は結果にちょうど m + n 回だけ表示されます。UNION DISTINCT
の場合、DISTINCT
はUNION
が計算された後に計算されるため、R は 1 回だけ表示されます。INTERSECT DISTINCT
の場合、上記の結果が計算された後にDISTINCT
が計算されます。EXCEPT DISTINCT
の場合、m> 0 かつ n = 0 の場合に行 R が出力に 1 回表示されます。- 2 つ以上の入力クエリがある場合、上記のオペレーションは概算され、出力は入力が左から右へ結合されて漸増する場合と同じになります。
以下のルールが適用されます。
UNION ALL
以外の集合演算子の場合、すべての列の型で等価比較がサポートされている必要があります。- 演算子の左右の入力クエリが、同じ数の列を返さなければなりません。
- 演算子は、該当する
SELECT
リストの列の位置に従って、各入力クエリにより返される列をペアリングします。つまり、最初の入力クエリの最初の列は、2 番目の入力クエリの最初の列とペアリングされます。 - 結果セットは常に、最初の入力クエリの列名を使用します。
- 結果セットは常に対応する列の入力データ型のスーパーデータ型を使用するため、ペアリングした列もまた同じデータ型であるか、共通のスーパーデータ型でなければなりません。
UNION ALL
やUNION DISTINCT
などの集合演算の処理はそれぞれ異なりますが、それらを区切るには、括弧を使用する必要があります。ステートメントが同じ集合演算を繰り返すだけであれば、括弧は必要ありません。
例:
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
演算子は、左側の入力クエリに存在し、右側の入力クエリには存在しない行を返します。
LIMIT 句と OFFSET 句
構文
LIMIT count [ OFFSET skip_rows ]
LIMIT
はデータ型 INT64 の負ではない count
を指定し、count
を超えない行数が返されます。LIMIT
0
で返される行数は 0 です。set 演算が存在する場合、set 演算を評価してから LIMIT
が適用されます。
OFFSET
はデータ型 INT64 の負ではない skip_rows
を指定し、テーブルのオフセット以降の行のみを考慮します。
これらの句はリテラルまたはパラメータの値のみを受け付けます。
LIMIT
と OFFSET
により返される行は、これらの演算子が ORDER BY
の後に使用されない限り、未指定になります。
WITH 句
WITH
句には 1 つ以上の名前付きサブクエリが含まれます。このサブクエリは、後続の SELECT
ステートメントから参照されるたびに実行されます。WITH
句で定義するサブクエリは、任意の句またはサブクエリから参照できます。UNION
などの集合演算子のいずれかの側にある任意の SELECT
ステートメントが含まれます。
BigQuery は WITH
句内のクエリの結果を実体化しないため、読みやすくするために 句は便利です。1 つのクエリが複数の WITH
句に含まれている場合、そのクエリは各句でそれぞれ実行されます。
例:
WITH subQ1 AS (SELECT SchoolID FROM Roster),
subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2;
WITH
句には、ネストテーブルのサブクエリの記述が望ましい代替方法ではない場合に、より複雑なクエリを SELECT
ステートメントと 句に分割するという有用な役割もあります。WITH
句に複数のサブクエリが含まれている場合、サブクエリ名を繰り返すことはできません。
BigQuery は、テーブルのサブクエリ、式のサブクエリなどのサブクエリで WITH
句をサポートします。
WITH q1 AS (my_query)
SELECT *
FROM
(WITH q2 AS (SELECT * FROM q1) SELECT * FROM q2)
WITH
句のスコープルールは以下のとおりです。
- エイリアスは、
WITH
句に導入されたエイリアスが、同じ 句の後のサブクエリと、 句の下のクエリにのみ表示されるように範囲を指定します。 - 同じ
WITH
句に導入されたエイリアスは一意にする必要がありますが、同じクエリの複数の 句で同じエイリアスを使用できます。ローカル エイリアスは、このエイリアスが表示される外側のエイリアスをすべてオーバーライドします。 WITH
句でエイリアスを設定したサブクエリに相関関係はありません。クエリ外部からの列は表示されません。外部からのもので表示される名前は、同じWITH
句で先に導入された他のWITH
エイリアスのみです。
以下に、WITH
サブクエリでエイリアスを使用するステートメントの例を示します。
WITH q1 AS (my_query)
SELECT *
FROM
(WITH q2 AS (SELECT * FROM q1), # q1 resolves to my_query
q3 AS (SELECT * FROM q1), # q1 resolves to my_query
q1 AS (SELECT * FROM q1), # q1 (in the query) resolves to my_query
q4 AS (SELECT * FROM q1) # q1 resolves to the WITH subquery
# on the previous line.
SELECT * FROM q1) # q1 resolves to the third inner WITH subquery.
BigQuery は WITH RECURSIVE
をサポートしません。
エイリアス
エイリアスはクエリで示されるテーブル、列、または式に対して与えられる一時的な名前です。SELECT
リストまたは FROM
句のいずれかで、明示的エイリアスを導入できます。あるいは BigQuery は一部の式に対する暗黙的なエイリアスを推定します。明示的なエイリアスも、暗黙のエイリアスもない式は匿名であり、クエリではこれらを名前で参照できません。
明示的なエイリアスの構文
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;
明示的なエイリアスの可視性
クエリに明示的エイリアスを導入した場合、そのエイリアスを参照できるクエリの他の場所について制限があります。エイリアスの可視性に対するこれらの制限は、BigQuery の名前範囲のルールの結果です。
FROM 句エイリアス
BigQuery は FROM
句のエイリアスを左から右に処理し、すべてのエイリアスは 句内の後続のパス式のみに表示されます。
例:
Singers
テーブルに ARRAY
データ型の Concerts
列があることを想定してください。
SELECT FirstName
FROM Singers AS s, s.Concerts;
無効:
SELECT FirstName
FROM s.Concerts, Singers AS s; // INVALID.
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 BY
、ORDER BY
、HAVING
の 3 つの句は、以下の値のみを参照できます。
FROM
句のテーブルとその列。SELECT
リストのエイリアス。
GROUP BY
と ORDER 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;
曖昧なエイリアス
BigQuery は、名前が曖昧である場合(複数の固有のオブジェクトに解決できる場合)にエラーとなります。
例:
このクエリには、Singers
と Songs
の両方に SingerID
という名前の列があるため、テーブル間で競合する列名が含まれます。
SELECT SingerID
FROM Singers, Songs;
このクエリでは、SELECT
リストでエイリアスが重複しているため、GROUP BY
句のエイリアスが曖昧になります。
SELECT FirstName AS name, LastName AS name,
FROM Singers
GROUP BY name;
次のステートメントでは、FROM
句の列名と GROUP BY
内の SELECT
リストのエイリアスが曖昧です。
SELECT UPPER(LastName) AS LastName
FROM Singers
GROUP BY LastName;
上のクエリでは、GROUP BY
句の LastName
が Singers
内の元の LastName
列とエイリアス AS LastName
(値は UPPER(LastName)
)のいずれも参照できるため、曖昧になりエラーが発生します。
曖昧さに対する同じルールがパス式に適用されます。次のクエリを考えてみます。table
に列 x
と y
があり、列 z
はデータ型 STRUCT で、フィールド v
、w
、x
が含まれます。
例:
SELECT x, z AS T
FROM table T
GROUP BY T.x;
エイリアス T
が曖昧なためエラーを生成します。これは、GROUP
BY
句の T.x
が table.x
と table.z.x
のどちらも参照する可能性があるためです。
基礎となる同じオブジェクトでその名前を解決できる場合、列名と SELECT
リストのエイリアスの両方で特定の名前が使用されていても、GROUP BY
、ORDER BY
、HAVING
でその名前は曖昧とはなりません。
例:
SELECT LastName, BirthYear AS BirthYear
FROM Singers
GROUP BY BirthYear;
エイリアス BirthYear
は、同じ基礎となる列 Singers.BirthYear
に解決されるため曖昧ではありません。
暗黙のエイリアス
SELECT
リストに明示的エイリアスが指定されていない式がある場合、BigQuery は次のルールに従って暗黙的エイリアスを割り当てます。このルールによって、SELECT
リストで複数の列に同じエイリアスが割り当てられる場合があります。
- 識別子の場合、エイリアスは識別子です。たとえば、
SELECT abc
はAS abc
を含意します。 - パス式の場合、エイリアスはパスの最後の識別子です。たとえば、
SELECT abc.def.ghi
はAS ghi
を含意します。 - 「ドット」メンバー フィールド アクセス演算子を使用するフィールド アクセスの場合、エイリアスはフィールド名になります。たとえば、
SELECT (struct_function()).fname
はAS fname
を含意します。
他のすべての事例では暗黙のエイリアスはないので、列は匿名で、名前では参照できません。その列からのデータは引き続き返され、表示されるクエリの結果にはその列に対して生成されたラベルがある場合がありますが、ラベルはエイリアスのようには使用できません。
FROM
句では、from_item
はエイリアスが必要なわけではありません。以下のルールが適用されます。
- 明示的エイリアスが指定されていない式では、BigQuery によって暗黙的エイリアスが以下のように割り当てられます。
- 識別子の場合、エイリアスは識別子です。たとえば、
FROM abc
はAS abc
を含意します。 - パス式の場合、エイリアスはパスの最後の識別子です。たとえば、
FROM abc.def.ghi
はAS ghi
を含意します。 WITH OFFSET
を使用して生成された列には、暗黙のエイリアスoffset
があります。- テーブルのサブクエリに、暗黙のエイリアスはありません。
FROM UNNEST(x)
に暗黙のエイリアスはありません。
付録 A: 例とサンプルデータ
サンプル テーブル
次の 3 つのテーブルは、運動選手、その学校、シーズン中の獲得点数に関するサンプルデータを含みます。これらのテーブルは、異なるクエリ句の動作を説明するために使用されます。
テーブル Roster:
LastName | SchoolID |
---|---|
Adams | 50 |
Buchanan | 52 |
Coolidge | 52 |
Davis | 51 |
Eisenhower | 77 |
Roster テーブルには、プレイヤーの名前(LastName)と学校に割り当てられた固有の ID(SchoolID)が含まれます。
テーブル PlayerStats:
LastName | OpponentID | PointsScored |
---|---|---|
Adams | 51 | 3 |
Buchanan | 77 | 0 |
Coolidge | 77 | 1 |
Adams | 52 | 4 |
Buchanan | 50 | 13 |
PlayerStats テーブルには、指定した試合(OpponentID)でプレイした敵側のチームに割り当てられたプレイヤーの名前(LastName)と固有の ID、その試合で選手がスコアしたポイント数(PointsScored)のリストが含まれます。
テーブル TeamMascot:
SchoolId | Mascot |
---|---|
50 | Jaguars |
51 | Knights |
52 | Lakers |
53 | Mustangs |
TeamMascot テーブルには一意の学校 ID(SchoolID)とその学校のマスコット(Mascot)のリストが格納されます。
JOIN のタイプ
1)[INNER] JOIN
例:
SELECT * FROM Roster JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
結果:
LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
---|---|---|---|
Adams | 50 | 50 | Jaguars |
Buchanan | 52 | 52 | Lakers |
Coolidge | 52 | 52 | Lakers |
Davis | 51 | 51 | Knights |
2)CROSS JOIN
例:
SELECT * FROM Roster CROSS JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
結果:
LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
---|---|---|---|
Adams | 50 | 50 | Jaguars |
Adams | 50 | 51 | Knights |
Adams | 50 | 52 | Lakers |
Adams | 50 | 53 | Mustangs |
Buchanan | 52 | 50 | Jaguars |
Buchanan | 52 | 51 | Knights |
Buchanan | 52 | 52 | Lakers |
Buchanan | 52 | 53 | Mustangs |
Coolidge | 52 | 50 | Jaguars |
Coolidge | 52 | 51 | Knights |
Coolidge | 52 | 52 | Lakers |
Coolidge | 52 | 53 | Mustangs |
Davis | 51 | 50 | Jaguars |
Davis | 51 | 51 | Knights |
Davis | 51 | 52 | Lakers |
Davis | 51 | 53 | Mustangs |
Eisenhower | 77 | 50 | Jaguars |
Eisenhower | 77 | 51 | Knights |
Eisenhower | 77 | 52 | Lakers |
Eisenhower | 77 | 53 | Mustangs |
3)FULL [OUTER] JOIN
例:
SELECT * FROM Roster FULL JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
---|---|---|---|
Adams | 50 | 50 | Jaguars |
Buchanan | 52 | 52 | Lakers |
Coolidge | 52 | 52 | Lakers |
Davis | 51 | 51 | Knights |
Eisenhower | 77 | NULL | NULL |
NULL | NULL | 53 | Mustangs |
4)LEFT [OUTER] JOIN
例:
SELECT * FROM Roster LEFT JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
結果:
LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
---|---|---|---|
Adams | 50 | 50 | Jaguars |
Buchanan | 52 | 52 | Lakers |
Coolidge | 52 | 52 | Lakers |
Davis | 51 | 51 | Knights |
Eisenhower | 77 | NULL | NULL |
5)RIGHT [OUTER] JOIN
例:
SELECT * FROM Roster RIGHT JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
結果:
LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
---|---|---|---|
Adams | 50 | 50 | Jaguars |
Davis | 51 | 51 | Knights |
Coolidge | 52 | 52 | Lakers |
Buchanan | 52 | 52 | Lakers |
NULL | NULL | 53 | Mustangs |
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 | ○ |
---|---|
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 DISTINCT
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)