查詢語法

查詢陳述式可掃描一或多個資料表或運算式,然後傳回經過計算的結果資料列。本主題說明 Cloud Spanner SQL 中的 SQL 查詢語法。

SQL 語法

query_statement:
    [ table_hint_expr ][ join_hint_expr ]
query_expr 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 ] ] } table_hint_expr: '@{' table_hint_key = table_hint_value '}' table_hint_key: { FORCE_INDEX } 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_TYPE } tablesample_type: TABLESAMPLE sample_method (sample_size percent_or_rows ) sample_method: { BERNOULLI | RESERVOIR } sample_size: numeric_value_expression percent_or_rows: { PERCENT | ROWS }




標記法

  • 角括弧 "[ ]" 表示選擇性子句。
  • 括號 "( )" 表示文字括號。
  • 分隔號 "|" 表示邏輯上的「或」。
  • 大括號 "{ }" 用於括住一組選項。
  • 角括弧內的逗號後面加上刪節號 "[, ... ]" 表示前述項目可能會在逗號分隔清單中重複。

SELECT 清單

語法

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

SELECT 清單定義查詢將傳回的資料欄。SELECT 清單中的運算式可參照其對應的 FROM 子句中任一 from_item 中的資料欄。

SELECT 清單中的每個項目會是以下其中一項:

  • *
  • expression
  • expression.*

SELECT *

SELECT * 通常指「select 星號萬用字元」,會在執行完整查詢後,針對每個顯示的資料欄,產生一個輸出資料欄。

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

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

SELECT expression

SELECT 清單中的項目可以是運算式。這些運算式會評估為單一值並產生一個輸出資料欄,同時具有選擇性的明確 alias

如果運算式沒有明確別名,可能的話,它會根據隱含別名的規則接收隱含別名。否則,資料欄會是匿名狀態,而且無法在查詢中的其他位置以名稱參照它。

SELECT expression.*

SELECT 清單項目的形式也可以是 expression.*。這會為 expression 的每個資料欄或頂層欄位產生一個輸出資料欄。運算式需為資料表別名,或評估為包含欄位的資料類型的一個值,例如 STRUCT。

下列查詢會為內嵌檢視 groceries 的資料表中的每個資料欄產生一個輸出資料欄。

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

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

更多範例:

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

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

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

SELECT 修飾符

您可以修改從 SELECT 查詢傳回的結果,如下所示:

SELECT DISTINCT

SELECT DISTINCT 陳述式會捨棄重複的資料列,只傳回剩餘的資料列。SELECT DISTINCT 不會傳回以下類型的資料欄:

  • STRUCT
  • ARRAY

SELECT ALL

SELECT ALL 陳述式會傳回所有資料列,包括重複的資料列。 SELECT ALLSELECT 的預設行為。

使用 STRUCT 搭配 SELECT

  • 系統不支援在傳回類型根目錄傳回 STRUCT 的查詢。例如,系統不支援下列查詢。

    SELECT STRUCT(1, 2) FROM Users;
    
  • 系統支援在傳回類型根目錄傳回 STRUCT 陣列。例如,系統支援下列查詢:

    SELECT ARRAY(SELECT STRUCT(1, 2)) FROM Users;
    
  • 不過,系統不支援在查詢結果中傳回 NULL STRUCT 的查詢形狀,因此不支援下列查詢:

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

另請參閱在子查詢中使用 STRUCTs 的注意事項。

別名

如要瞭解 SELECT 清單別名的語法和可見度,請參閱別名一節。

FROM 子句

FROM 子句可用於指定要從中擷取資料列的一或多個資料表,並可指定如何彙整這些資料列來產生單一資料列串流,以在查詢的其他部分進行處理。

語法

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 ] ]
}
table_hint_expr: '@{' table_hint_key = table_hint_value '}' table_hint_key: { FORCE_INDEX }

table_name

現有資料表的名稱 (選擇性限定)。

SELECT * FROM Roster;
SELECT * FROM db.Roster;
資料表提示

系統支援下列資料表提示:

提示鍵 可能的值 說明
FORCE_INDEX 字串。資料庫中現有索引的名稱,或 _BASE_TABLE 以使用主資料表,而不使用索引。
  • 如果設定為索引名稱,請使用該索引,不要使用主資料表。如果索引無法提供所有需要的資料欄,請執行與主資料表的後端彙整作業。
  • 如果設定為 _BASE_TABLE 字串,請在索引策略中使用主資料表,而不要使用索引。請注意,只有在陳述式提示運算式中使用 FORCE_INDEX 時,這才是有效的值。

附註:FORCE_INDEX 其實是指令而不是提示,也就是說,如果索引不存在,就會產生錯誤。

下列範例說明如何將 @{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 類型

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 是無效的路徑,因為該路徑的路徑結尾之前包含陣列。

注意事項:如果路徑只有一個名稱,它會解譯為資料表。如要解決這個問題,請使用 UNNEST 包裝路徑,或使用完整路徑。

UNNEST

UNNEST 運算子使用 ARRAY 傳回一個資料表,其中 ARRAY 中的每個元素均會在資料表中顯示成一列。您也可以在 FROM 子句外使用 UNNEST 搭配 IN 運算子

對於大多數元素類型的輸入 ARRAYUNNEST 的輸出通常含有一個資料欄。這個單一資料欄有一個選擇性的 alias,用於在查詢中的其他位置參照該資料欄。這些元素類型的 ARRAYS 會傳回多個資料欄:

  • STRUCT

UNNEST 會刪除輸入 ARRAY 中的元素順序。使用選擇性的 WITH OFFSET 子句可傳回含有陣列元素索引的第二個資料欄 (如下所示)。

對於 STRUCTARRAY 輸入值,UNNEST 會針對每個 STRUCT 傳回一列,STRUCT 中的每個欄位以獨立一欄顯示。每個資料欄的別名是對應的 STRUCT 欄位的名稱。

範例

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

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

因為 UNNEST 運算子會傳回值資料表,您可以設定 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 對於空值的處理方式如下:

  • 空值與空白 ARRAY 不會產生任何資料列。
  • 包含空值的 ARRAY 會產生包含空值的資料列。

選擇性的 WITH OFFSET 子句會為 UNNEST 作業產生的每個資料列傳回包含「偏移」值 (亦即從零開始計算) 的獨立資料欄。這個資料欄有選擇性的 alias;預設別名為 offset。

範例:

SELECT * FROM UNNEST ( ) WITH OFFSET AS num;

如需更多使用 UNNEST 的方法,包括建構、整併與篩選,請參閱 Arrays topic

子查詢

子查詢是顯示在其他陳述式內且使用括號括住的查詢。子查詢也稱為「子 SELECT」或「巢狀 SELECT」。子查詢適用完整的 SELECT 語法。

子查詢共有兩種類型:

  • 運算式子查詢,可用於包含有效運算式的查詢。運算式子查詢會傳回單一值。
  • 資料表子查詢,只能用在 FROM 子句。外部查詢會將子查詢的結果視為資料表。

請注意,兩種子查詢都需以括號括住。

範例:

SELECT AVG ( PointsScored )
FROM
( SELECT PointsScored
  FROM Stats
  WHERE SchoolID = 77 )

資料表子查詢也可以指定別名。

範例:

SELECT r.LastName
FROM
( SELECT * FROM Roster) AS r;

子查詢相關注意事項

使用子查詢時:

  • 如果運算式子查詢傳回多個值,則必須使用 ARRAY 關鍵字在陣列中傳回該子查詢的結果。如果 ARRAY 不存在,則子查詢語法是合法的,但在執行查詢時,不允許該子查詢在每次叫用時傳回多個值。如果傳回多個值,則查詢會在執行期間失敗。
  • 選取多個資料欄的運算式子查詢必須使用 AS STRUCT 傳回 STRUCT 形式的資料欄值。

以下是這類查詢的範例:

SELECT r.LastName, r.SchoolId,
       ARRAY(SELECT AS STRUCT p.OpponentID, p.PointsScored
             FROM PlayerStats AS p
             WHERE p.LastName = r.LastName) AS PlayerStats
FROM Roster AS r;

從 Cloud Spanner 資料庫中讀取資料時,使用子選取來擷取交錯資料表的資料列是常見的模式。請務必注意,交錯關係並非 SQL 資料模型的一部分,在這種情況下,子選取必須彙整到相關資料列。

以下是從交錯資料表選取相關資料列,並包含明確彙整條件的子查詢範例:

SELECT ...
  ARRAY(
      SELECT AS STRUCT ...
      FROM ChildTable ct
      WHERE ct.parent_key = pt.parent_key
            -- The second part of the condition applies when the key is nullable
            OR (ct.parent_key IS NULL AND pt.parent_key IS NULL)) as child_rows
FROM ParentTable pt
WHERE ...;

另請參閱在 SELECT 清單中使用 STRUCT 的注意事項。

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 的樣本,其中任何大小為 K 的樣本可能性均相等。

TABLESAMPLE 運算子會要求您選取 ROWSPERCENT。 如果您選取 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 UserId="test")
TABLESAMPLE BERNOULLI(50 PERCENT)
WHERE MessageId > 3;

使用 TABLESAMPLE 運算搭配另一個資料表的彙整。

SELECT S.Subject
FROM
(SELECT MessageId, ThreadId FROM Messages WHERE UserId="test") AS R
TABLESAMPLE RESERVOIR(5 ROWS),
Threads AS S
WHERE S.UserId="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_TYPE }



JOIN 子句可合併兩個 from_item,使 SELECT 子句可以從一個來源查詢。join_typeONUSING 子句 (「彙整條件」) 用於指定如何合併與刪除兩個 from_item 中的資料列,以組成單一來源。

所有 JOIN 子句均需要 join_type

JOIN 子句需要彙整條件,除非下列其中一項條件為 true:

  • join_typeCROSS
  • from_item 中至少有一項不是資料表,例如 array_pathfield_path

彙整提示

系統支援下列 JOIN 提示:

提示鍵 可能的值 說明
FORCE_JOIN_ORDER TRUE
FALSE (預設)
如果設為 true,請使用查詢中指定的彙整順序。
JOIN_TYPE HASH_JOIN
APPLY_JOIN
LOOP_JOIN

實作邏輯彙整時,請選擇要用於基礎彙整方法的特定替代項目。進一步瞭解彙整方法
如要使用雜湊彙整,請使用 HASH JOINJOIN@{JOIN_TYPE=HASH_JOIN},而不是兩者皆使用。

彙整方法

彙整方法是各種邏輯彙整類型的特定實作。某些彙整方法僅適用於特定彙整類型。要選擇使用哪個彙整方法取決於查詢的具體細節以及要查詢的資料。如要瞭解特定彙整方法是否有助於提升查詢效能,最好嘗試該方法,然後查看產生的查詢執行計劃。詳情請參閱查詢執行運算子,尤其是套用、雜湊彙整和迴圈彙整運算子的相關章節。

彙整方法 說明 運算元
HASH_JOIN 雜湊彙整運算子會從某一端 (建構端) 建構雜湊資料表,並在另一端 (探測端) 探測雜湊資料表中的所有元素。 系統會針對各種彙整類型使用不同的子類。請查看查詢的查詢執行計劃,確認使用的是哪一個子類。進一步瞭解雜湊彙整運算子
APPLY_JOIN 套用彙整運算子會從某一端 (輸入端) 取得每個項目,並使用輸入端項目的值在另一端 (對應端) 評估子查詢。 系統會針對各種彙整類型使用不同的子類。如果是內部彙整,會使用交叉套用;如果是左彙整,則會使用外部套用。進一步瞭解交叉套用外部套用運算子。
LOOP_JOIN 迴圈彙整運算子會處理某一端的每個項目,然後搜尋另一端的每個項目。 系統會針對各種彙整類型使用不同的子類。請查看查詢的查詢執行計劃,確認使用的是哪一個子類。進一步瞭解迴圈彙整運算子

[INNER] JOIN

INNER JOIN (或簡稱為 JOIN) 能有效計算兩個 from_item 的笛卡爾乘積,並捨棄不符合彙整條件的所有資料列。「有效」意味著能夠實作 INNER JOIN,不需實際計算笛卡爾乘積。

CROSS JOIN

CROSS JOIN 會傳回兩個 from_item 的笛卡兒乘積。換句話說,它會保留兩個 from_item 中的所有資料列,然後將第一個 from_item 中的每個資料列與第二個 from_item 中的每個資料列合併。

逗號交叉聯結

CROSS JOIN 可明確地編寫 (如上所述),或可使用逗號分隔 from_item 以隱含的方式編寫。

隱含的「逗號交叉聯結」範例如下:

SELECT * FROM Roster, TeamMascot;

以下是對等的明確交叉聯結:

SELECT * FROM Roster CROSS JOIN TeamMascot;

您不能以括號括住逗號交叉聯結。

無效 - 逗號交叉聯結在括號內:

SELECT * FROM t CROSS JOIN (Roster, TeamMascot);  // INVALID.

如要瞭解逗號交叉聯結在 JSON 序列中的行為,請參閱 JOIN 序列

FULL [OUTER] JOIN

FULL OUTER JOIN (或簡稱 FULL JOIN) 會傳回兩個 from_item 中符合彙整條件的所有資料列的所有欄位。

FULL 表示即使不符合彙整條件,仍會傳回兩個 from_item 中的「所有資料列」

OUTER 表示如果某個 from_item 中的特定資料列未與另一個 from_item 中的任何資料列彙整,該資料列會針對另一個 from_item 中的所有資料欄傳回空值。

LEFT [OUTER] JOIN

兩個 from_itemLEFT OUTER JOIN (或簡稱為 LEFT JOIN) 的結果一律在 JOIN 子句中保留左側 from_item 的所有資料列,即便右側 from_item 中沒有資料列可滿足彙整述詞。

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,表示合併的資料列 (彙整兩個資料列的結果) 符合彙整條件。

範例:

SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;

USING 子句

USING 子句需要在兩個輸入資料表出現的一或多個資料欄的 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;

當您使用 SELECT * 時,使用 USING 的查詢結果與使用 ON 的查詢結果並不相同。為說明這個情況,請思考以下查詢:

SELECT * FROM Roster INNER JOIN PlayerStats
USING (LastName);

這個陳述式會傳回 RosterPlayerStats 中的資料列,其中 Roster.LastNamePlayerStats.LastName 相同。結果包含單一 LastName 資料欄。

另一方面,請思考以下查詢:

SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;

這個陳述式會傳回 RosterPlayerStats 中的資料列,其中 Roster.LastNamePlayerStats.LastName 相同。結果包含兩個 LastName 資料欄,一欄來自 Roster,另一欄來自 PlayerStats

JOIN 序列

FROM 可依次包含多個 JOIN 子句。

範例:

SELECT * FROM a LEFT JOIN b ON TRUE LEFT JOIN c ON TRUE;

其中的 abc 是任一 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)) ;

若查詢中存在逗號交叉聯結並包含一個 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);

逗號交叉聯結後面不可以有 RIGHT JOINFULL 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 JOINON 的查詢,其運算式相當於使用 CROSS JOINWHERE 的運算式。

範例 - 以下查詢:

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 [, ...]

GROUP BY 子句可將資料表中 expression 的值無明顯不同的資料列群組在 GROUP BY 子句中。針對來源資料表中 expression 的值無明顯不同的多個資料列,GROUP BY 子句會產生單一合併資料列。通常若 SELECT 清單中存在匯總函式,或為了排除輸出中的多餘項目,就會使用 GROUP BYexpression 的資料類型必須可分組

範例:

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

GROUP BY 子句可參照 SELECT 清單中的運算式名稱。GROUP BY 子句也可利用整數值來對 SELECT 中的運算式做序數性的參照。1 會參照 SELECT 清單中的第一個運算式,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 子句的差異在於:

  • 查詢中必須存在 GROUP BY 或匯總才能使用 HAVING 子句。
  • HAVING 子句的位置在 GROUP BY 與匯總之後、ORDER BY 之前。這意味著結果集中每個匯總的資料列都會進行一次 HAVING 字句的評估。這與在 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 子句本身,但匯總必須至少以下列其中一種形式存在:

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 子句時,匯總函式與它們參照的資料欄不需相同。在下列範例中,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 子句不存在,則不會定義查詢結果的順序。預設的排序方向為 ASC,即以 expression 值的遞增順序排序結果。DESC 會以遞減順序排序結果。可使用 FROM 子句或 SELECT 清單中的資料欄別名。如果查詢的 SELECT 子句包含別名,這些別名會覆寫對應的 FROM 子句中的名稱。

您可依多個資料欄排序。在下列範例中,結果集會先依 SchoolID 排序,再依 LastName 排序:

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

排序值的規則如下:

  • 空值:在 ORDER BY 子句的內容中,空值是最小的可能值;也就是說,以 ASC 順序排序時,空值會最先顯示,以 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 子句只會套用至第二個 SELECT 陳述式:

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

您也可以在 ORDER BY 子句中使用整數文字做為資料欄參照。整數文字會成為 SELECT 清單的序數 (例如,從 1 開始計算)。

範例 - 以下兩個查詢相等:

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

集合運算子

語法

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

集合運算子會將兩個或兩個以上之輸入查詢的結果合併為單一結果集。您必須指定 ALLDISTINCT;指定 ALL 會保留所有資料列。指定 DISTINCT 時,會捨棄重複的資料列。

如果指定的資料列 R 在第一個輸入查詢中明確出現 m 次,在第二個輸入查詢中出現 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,會先計算 UNION 再計算 DISTINCT,因此 R 只會出現一次。
  • 如為 INTERSECT DISTINCT,會先計算上述結果再計算 DISTINCT
  • 如為 EXCEPT DISTINCT,m > 0 且 n = 0 時,資料列 R 會在輸出中出現一次。
  • 如果有兩個以上的輸入查詢,可以歸納為上述運算,而輸出會與從左到右以增量方式合併的輸入相同。

規則如下:

  • 如為 UNION ALL 以外的集合運算,所有資料欄類型都必須支援相等性比較。
  • 運算子兩端的輸入查詢必須傳回相同數目的資料欄。
  • 運算子會根據資料欄在其個別的 SELECT 清單中的位置,將每個輸入查詢傳回的資料欄配對。也就是說,第一個輸入查詢中的第一個資料欄會與第二個輸入查詢中的第一個資料欄配對。
  • 結果集一律會使用第一個輸入查詢中的資料欄名稱。
  • 結果集一律會使用對應資料欄中的輸入類型的超類型,因此,配對的資料欄也必須擁有相同的資料類型或共通的超類型。
  • 您必須使用括號分隔不同的集合運算;就此而言,UNION ALLUNION 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 運算子可將每個查詢結果集中的資料欄配對,並將它們垂直串連,以合併兩個或兩個以上的輸入查詢結果集。

INTERSECT

INTERSECT 運算子會傳回在左側和右側輸入查詢的結果集中找到的資料列。有別於 EXCEPT,輸入查詢的位置 (在 INTERSECT 運算子的左側或右側) 沒有影響。

EXCEPT

EXCEPT 運算子會傳回在左側輸入查詢而不在右側輸入查詢中的資料列。

LIMIT 子句與 OFFSET 子句

語法

LIMIT count [ OFFSET skip_rows ]

LIMIT 用於指定 INT64 類型的正整數 count,而且不會傳回超過 count 個資料列。LIMIT 0 會傳回 0 個資料列。如果有集合運算,則會在評估集合運算後再套用 LIMIT

OFFSET 用於指定 INT64 類型的正整數 skip_rows,而且只會考慮資料表中來自該偏移的資料列。

這些子句只接受文字或參數值。

除非 LIMITOFFSET 使用於 ORDER BY 之後,否則這些運算子傳回的資料列並不明確。

別名

別名是為查詢中的資料表、資料欄或運算式所指定的臨時名稱。您可以在 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;

您可以在 SELECT 子句中使用 [AS] alias 引入任何運算式的明確別名。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 這三個子句只能參照下列值:

  • FROM 子句中的資料表和任一個資料欄。
  • SELECT 清單中的別名。

GROUP BYORDER BY 也可參照第三個群組:

  • 整數文字,即參照 SELECT 清單中的項目。整數 1 參照 SELECT 清單中的第一個項目,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;

這個查詢的 GROUP BY 子句包含模棱兩可的別名,因為它們重複出現在 SELECT 清單中:

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 含有資料欄 xy,而資料欄 z 是 STRUCT 類型,並且含有 vwx 欄位。

範例:

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

別名 T 因模稜兩可而將產生錯誤,因為 GROUP BY 子句中的 T.x 可能參照 table.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 abc 意味著 AS abc
  • 如為路徑運算式,別名即為路徑中最後一個識別碼。例如,SELECT abc.def.ghi 意味著 AS ghi
  • 如為使用成員欄位存取運算子「點」的欄位存取,別名即為欄位名稱。例如,SELECT (struct_function()).fname 意味著 AS fname

其他情況下則沒有隱含別名,因此資料欄是匿名狀態,而無法依名稱參照。該資料欄中的資料仍會傳回,顯示的查詢結果可能會為該資料欄產生一個標籤,但標籤無法當做別名使用。

FROM 子句中,from_item 不需要別名。規則如下:

  • 如果運算式沒有明確別名,Cloud Spanner SQL 會在下列情況指派隱含別名:
    • 如為識別碼,別名即為識別碼。例如,FROM abc 意味著 AS abc
    • 如為路徑運算式,別名即為路徑中最後一個識別碼。例如,FROM abc.def.ghi 意味著 AS ghi
    • 使用 WITH OFFSET 產生的資料欄含有隱含別名 offset

  • 資料表子查詢沒有隱含別名。
  • FROM UNNEST(x) 沒有隱含別名。

附錄 A:含有範例資料的範例

範例資料表

以下三個資料表包含有關運動員、其就讀學校及賽季得分的範例資料。這些資料表將用來說明不同查詢子句的行為。

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 資料表包含運動員名稱 (LastName)、指定比賽中對手的專屬 ID (OpponentID) 及該運動員在該比賽中的得分 (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 陳述式結果集中的資料欄配對,並將它們垂直串連,以合併兩個或兩個以上的 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

以下查詢會傳回在 Roster 而不在 PlayerStats 中的姓氏。

SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;

結果:

LastName
Eisenhower
Davis

若反轉 SELECT 陳述式的順序,將傳回在 PlayerStats 中但不在 Roster 中的姓氏:

SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;

結果:

(empty)

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁
Cloud Spanner 說明文件