쿼리 구문

쿼리 문은 테이블 또는 표현식을 한 개 이상 검색하고 계산된 결과 행을 반환합니다. 이 주제는 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 | OPTIMIZER_STATISTICS_PACKAGE | ALLOW_DISTRIBUTED_MERGE | LOCK_SCANNED_RANGES } query_expr: [ WITH cte[, ...] ] { select | ( query_expr ) | set_operation } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [ AS { STRUCT | VALUE } ] [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...] [ FROM from_clause[, ...] ] [ WHERE bool_expression ] [ GROUP BY expression [, ...] ] [ HAVING bool_expression ] table_hint_expr: '@{' table_hint_key = table_hint_value '}' table_hint_key: { FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION }

표기법 규칙

  • 대괄호 '[ ]'는 절(선택사항)을 의미합니다.
  • 괄호 '( )'는 리터럴 괄호를 의미합니다.
  • 세로 막대 '|'는 논리 OR을 의미합니다.
  • 중괄호 '{ }'는 옵션 조합을 묶는 데 사용됩니다.
  • 꺾쇠 괄호 '[, ...]' 안에 있는 쉼표 다음에 오는 줄임표는 앞의 항목이 쉼표로 구분된 목록으로 반복될 수 있음을 의미합니다.

문 힌트

쿼리 문에는 다음과 같은 힌트가 지원됩니다.

힌트 키 가능한 값 설명
USE_ADDITIONAL_PARALLELISM TRUE
FALSE(기본값)
TRUE이면 실행 엔진은 가능한 한 더 많은 동시 로드를 사용합니다. 이로 인해 다른 작업에 사용할 수 있는 리소스가 줄어들 수 있으므로 동일한 인스턴스에서 지연 시간에 민감한 작업을 실행하는 경우에는 이 힌트를 사용하지 않는 것이 좋습니다.
OPTIMIZER_VERSION 1~N|latest_version|default_version 지정된 옵티마이저 버전을 사용하여 쿼리를 실행합니다. 가능한 값은 1~N(최신 옵티마이저 버전), default_version 또는 latest_version입니다. 힌트가 설정되지 않은 경우 옵티마이저는 데이터베이스 옵션에 설정되거나 클라이언트 API를 통해 지정된 패키지에 대해 실행됩니다. 둘 다 설정되지 않으면 옵티마이저는 기본 버전을 사용합니다.

버전 설정 우선 순위는 클라이언트 API에 의해 설정된 값이 데이터베이스 옵션의 값보다 우선하며 이 힌트에 의해 설정된 값이 다른 모든 값보다 우선합니다.

자세한 내용은 쿼리 옵티마이저를 참조하세요.
OPTIMIZER_STATISTICS_PACKAGE package_name|latest 지정된 옵티마이저 통계 패키지를 사용하여 쿼리를 실행합니다. package_name의 가능한 값은 다음 쿼리를 실행하여 찾을 수 있습니다.


SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS

힌트가 설정되지 않은 경우 옵티마이저는 데이터베이스 옵션에 설정되거나 클라이언트 API를 통해 지정된 패키지에 대해 실행됩니다. 둘 다 설정되지 않은 경우 옵티마이저는 기본적으로 최신 패키지로 설정됩니다.

클라이언트 API에 의해 설정된 값이 데이터베이스 옵션의 값보다 우선하며 이 힌트에 의해 설정된 값이 다른 모든 값보다 우선합니다.

지정된 패키지를 데이터베이스 옵션으로 고정하거나 가비지 컬렉션 방지를 위해 allow_gc=false가 있어야 합니다.

자세한 내용은 쿼리 옵티마이저 통계 패키지를 참조하세요.
ALLOW_DISTRIBUTED_MERGE TRUE(기본값)
FALSE
TRUE(기본값)이면 엔진이 특정 ORDER BY 쿼리에 대해 분산된 병합 정렬 알고리즘을 사용합니다. 해당하는 경우 전역 정렬이 로컬 정렬로 변경됩니다. 이렇게 하면 데이터가 저장된 위치와 가까운 병렬 정렬의 이점을 얻을 수 있습니다. 그런 후 로컬로 정렬된 데이터가 병합되어 전역 정렬 데이터를 제공합니다. 따라서 전체 전역 정렬을 없애고 지연 시간을 향상시킬 수 있습니다. 이 기능은 특정 ORDER BY 쿼리의 동시 로드를 향상시킬 수 있습니다. 이 힌트는 사용자가 필요에 따라 분산 병합 알고리즘을 해제한 상태로 실험을 진행할 수 있도록 제공되었습니다.
LOCK_SCANNED_RANGES exclusive
shared(기본값)
이 힌트를 사용하여 트랜잭션에 의해 스캔된 범위 집합에 대한 배타적 잠금을 요청합니다. 배타적 잠금을 획득하면 대규모 쓰기 경합이 관찰되는 시나리오에서, 즉 여러 트랜잭션이 동시에 같은 데이터를 읽고 쓰려고 시도하여 많은 중단이 발생할 때 도움이 됩니다.

힌트가 없으면 여러 동시 트랜잭션이 공유 잠금을 획득한 후 배타적 잠금으로 업그레이드될 수 있습니다. 각 트랜잭션의 공유 잠금이 다른 트랜잭션이 배타적 잠금으로 업그레이드되는 것을 방지하므로 교착 상태가 발생합니다. Cloud Spanner는 하나를 제외한 모든 트랜잭션을 취소합니다. 이 힌트를 사용하여 배타적 잠금을 요청하는 경우 한 트랜잭션은 잠금을 획득하고 실행을 진행하는 반면 다른 트랜잭션은 잠금을 설정할 때까지 대기합니다. 충돌하는 트랜잭션은 한 번에 하나씩만 수행할 수 있기 때문에 처리량이 여전히 제한되지만, 이 경우 Cloud Spanner는 항상 한 트랜잭션에서 진행되므로, 트랜잭션을 중단하고 재시도하는 데 소요되는 시간이 줄어듭니다.

이 힌트는 쿼리 및 DML 모두의 모든 문에서 지원됩니다.

Cloud Spanner는 항상 직렬화 가능성을 적용합니다. 잠금 모드 힌트는 경합이 발생한 워크로드에서 대기하거나 중단할 트랜잭션에 영향을 줄 수 있지만 격리 수준은 변경하지 않습니다.

이는 힌트일 뿐이므로 뮤텍스에 상응하는 것으로 간주되지 않습니다. 즉, Cloud Spanner 외부에서 코드를 실행하기 위해 Cloud Spanner 배타적 잠금을 상호 제외 메커니즘으로 사용해서는 안 됩니다.

자세한 내용은 잠금을 참조하세요.

샘플 테이블

다음 테이블은 이 참조에서 다양한 쿼리 절의 동작을 설명하는 데 사용됩니다.

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            |
| Adams      | 52         | 4            |
| Buchanan   | 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 [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }]
    { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...]

SELECT 목록은 쿼리가 반환할 열을 정의합니다. SELECT 목록의 표현식은 해당하는 FROM 절에서 from_item의 열을 참조할 수 있습니다.

SELECT 목록의 각 항목은 다음 중 하나입니다.

  • *
  • expression
  • expression.*

SELECT *

SELECT *select star라고 읽으며 전체 쿼리를 실행한 후에 표시되는 열마다 출력 열을 한 개씩 생성합니다.

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

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

SELECT expression

SELECT 목록의 항목은 표현식일 수 있습니다. 표현식은 단일 값으로 평가되며 명시적 alias(선택사항)가 있는 출력 열 한 개를 생성합니다.

표현식에 명시적 별칭이 없는 경우 가능하면 암시적 별칭 규칙에 따라 암시적 별칭을 수신합니다. 그 밖의 경우 열은 익명이며, 이름을 쿼리의 다른 곳에서 참조할 수 없습니다.

SELECT expression.*

SELECT 목록의 항목은 expression.* 형식을 취할 수도 있습니다. 그러면 expression의 열 또는 최상위 필드마다 출력 열이 한 개씩 생성됩니다. 이 표현식은 테이블 별칭이거나 STRUCT와 같은 필드가 있는 데이터 유형의 단일 값으로 평가되어야 합니다.

다음 쿼리는 테이블 groceries(별칭: 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 * EXCEPT

SELECT * EXCEPT 문은 결과에서 제외할 열의 이름을 한 개 이상 지정합니다. 일치하는 모든 열 이름은 출력에서 생략됩니다.

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 * REPLACE

SELECT * REPLACE 문은 expression AS identifier 절을 한 개 이상 지정합니다. 각 식별자는 SELECT * 문의 열 이름 한 개와 일치해야 합니다. 출력 열 목록에서 REPLACE 절의 식별자와 일치하는 열은 이 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 쿼리에서 반환된 결과를 수정할 수 있습니다.

SELECT DISTINCT

SELECT DISTINCT 문은 중복 행을 삭제하고 나머지 행만 반환합니다. SELECT DISTINCT는 다음 유형의 열을 반환할 수 없습니다.

  • STRUCT
  • ARRAY

SELECT ALL

SELECT ALL 문은 중복 행을 포함한 모든 행을 반환합니다. SELECT ALLSELECT의 기본 동작입니다.

SELECT와 함께 STRUCT 사용

  • 반환 유형의 루트에서 STRUCT를 반환하는 쿼리는 Cloud Spanner API에서 지원되지 않습니다. 예를 들어 다음 쿼리는 서브 쿼리로만 지원됩니다.

    SELECT STRUCT(1, 2) FROM Users;
    
  • 구조체 배열 반환이 지원됩니다. 예를 들어 다음 쿼리는 Cloud Spanner API에서 지원됩니다.

    SELECT ARRAY(SELECT STRUCT(1 AS A, 2 AS B)) FROM Users;
    
    SELECT ARRAY(SELECT AS STRUCT 1 AS a, 2 AS b) FROM Users;
    
  • 그러나 NULL 요소가 포함된 ARRAY<STRUCT<...>> 유형의 NULL 값 또는 ARRAY<STRUCT<...>> 유형의 값을 반환할 수 있는 쿼리 셰이프는 Cloud Spanner API에서 지원되지 않으므로, 다음 쿼리는 서브 쿼리로만 지원됩니다.

    SELECT ARRAY(SELECT IF(STARTS_WITH(Users.username, "a"), NULL, STRUCT(1, 2)))
    FROM Users;
    

ARRAY 내에서 STRUCTs를 쿼리하는 방법에 대한 자세한 예시는 ARRAY의 STRUCT 요소 쿼리를 참조하세요.

서브 쿼리에서의 STRUCTs 사용에 대한 참고사항도 참조하세요.

값 테이블

Cloud Spanner SQL에서 값 테이블은 행 유형이 단일 값인 테이블입니다. 일반 테이블에서 각 행은 각각 이름과 유형이 있는 열로 구성됩니다. 값 테이블에서 행 유형은 단일 값이며 열 이름은 없습니다.

Cloud Spanner에서 값 테이블은 주로 UNNEST 연산자 또는 서브 쿼리의 출력으로 발생합니다. WITH은 사용된 서브쿼리가 값 테이블을 생성하는 경우 값 테이블을 도입합니다. Cloud Spanner는 값 스키마를 데이터베이스 스키마의 기본 테이블로 지원하지 않으며 쿼리 결과에서 값 테이블 반환을 지원하지 않습니다. 따라서 쿼리를 생성하는 값 테이블은 최상위 쿼리로 지원되지 않습니다.

정확히 하나의 열이 있는 쿼리가 예상되는 상황이라면 값 테이블 쿼리를 대신 사용할 수 있습니다. 예를 들어 스칼라 서브 쿼리와 배열 서브 쿼리(서브 쿼리 참조)에는 일반적으로 단일 열 쿼리가 필요하지만 Cloud Spanner SQL에서는 값 테이블 쿼리도 사용할 수 있습니다.

SELECT AS를 사용하는 쿼리는 아래 구문 중 하나를 사용하여 값 테이블을 생성할 수 있습니다.

SELECT AS STRUCT

SELECT AS STRUCT expr [[AS] struct_field_name1] [,...]

이 경우 STRUCT 행 유형의 값 테이블이 생성됩니다. 여기서 STRUCT 필드 이름과 유형은 SELECT 목록에 생성된 열 이름 및 유형과 일치합니다.

예를 들면 다음과 같습니다.

SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)

스칼라 또는 배열 서브 쿼리에서 SELECT AS STRUCT를 사용하면 여러 값을 함께 그룹화하는 단일 STRUCT 유형을 생성할 수 있습니다. 스칼라 및 배열 서브 쿼리(서브 쿼리 참조)에서는 일반적으로 여러 열 반환이 허용되지 않지만 STRUCT 유형을 사용하여 단일 열을 반환할 수 있습니다.

익명 열이 허용됩니다.

예를 들면 다음과 같습니다.

SELECT AS STRUCT 1 x, 2, 3

위 쿼리는 STRUCT<int64 x, int64, int64>. 유형의 STRUCT 값을 생성합니다. 첫 번째 필드는 이름이 x이고 두 번째 및 세 번째 필드는 익명입니다.

위 예시는 구조체 생성자를 사용하는 다음 SELECT AS VALUE 쿼리와 동일한 결과를 생성합니다.

SELECT AS VALUE STRUCT(1 AS x, 2, 3)

중복 열이 허용됩니다.

예를 들면 다음과 같습니다.

SELECT AS STRUCT 1 x, 2 y, 3 x

위 쿼리는 STRUCT<int64 x, int64 y, int64 x>. 유형의 STRUCT 값을 생성합니다. 첫 번째 및 세 번째 필드는 이름이 동일하게 x이고 두 번째 필드는 이름이 y입니다.

위 예시는 구조체 생성자를 사용하는 다음 SELECT AS VALUE 쿼리와 동일한 결과를 생성합니다.

SELECT AS VALUE STRUCT(1 AS x, 2 AS y, 3 AS x)

SELECT AS VALUE

SELECT AS VALUE는 정확하게 열을 한 개 생성하는 SELECT 목록에서 값 테이블을 생성합니다. 열이 한 개 있는(이름이 포함될 수 있음) 출력 테이블을 생성하는 대신 행 유형이 SELECT 열 한 개에서 생성된 값 유형인 값 테이블이 출력됩니다. 열 별칭은 값 테이블에서 삭제됩니다.

예를 들면 다음과 같습니다.

SELECT AS VALUE 1

위 쿼리는 행 유형이 INT64인 테이블을 생성합니다.

예를 들면 다음과 같습니다.

SELECT AS VALUE STRUCT(1 AS a, 2 AS b) xyz

위 쿼리는 행 유형이 STRUCT<a int64, b int64>인 테이블을 생성합니다.

예를 들면 다음과 같습니다.

SELECT AS VALUE v FROM (SELECT AS STRUCT 1 a, true b) v WHERE v.b

값 테이블 v를 입력으로 지정하면 위의 쿼리는 WHERE 절의 특정 값을 필터링한 다음 입력 테이블에 있는 것과 동일한 값을 사용하여 값 테이블을 생성합니다. 위의 쿼리에서 SELECT AS VALUE를 사용하지 않는 경우 출력 테이블은 입력 값이 포함된 v 열이 있는 일반 테이블이므로 출력 테이블 스키마는 입력 테이블 스키마와 다릅니다.

별칭

SELECT 목록 별칭의 구문과 표시 여부는 별칭 사용을 참조하세요.

FROM 절

FROM from_clause[, ...]

from_clause:
    from_item
    [ tablesample_operator ]

from_item:
    {
      table_name [ table_hint_expr ] [ as_alias ]
      | join_operation
      | ( query_expr ) [ table_hint_expr ] [ as_alias ]
      | field_path
      | unnest_operator
      | cte_name [ table_hint_expr ] [ as_alias ]
    }

table_hint_expr:
    '@{' table_hint_key = table_hint_value '}'

table_hint_key:
    {
      FORCE_INDEX
      | GROUPBY_SCAN_OPTIMIZATION
    }

as_alias:
    [ AS ] alias

FROM 절은 행을 검색할 테이블을 나타내고 쿼리의 나머지 부분에서 처리할 단일 행 스트림을 생성하기 위해 해당 행을 조인하는 방법을 지정합니다.

tablesample_operator

TABLESAMPLE 연산자를 참조하세요.

table_name

기존 테이블의 이름입니다.

SELECT * FROM Roster;
테이블 힌트

테이블에 대해 다음과 같은 힌트가 지원됩니다.

힌트 키 가능한 값 설명
FORCE_INDEX 문자열. 데이터베이스의 기존 색인 이름 또는 색인이 아닌 기본 테이블을 사용하는 _BASE_TABLE.
  • 색인 이름으로 설정되면 기본 테이블 대신 해당 색인을 사용합니다. 색인이 필요한 모든 열을 제공할 수 없는 경우, 기본 테이블을 사용하여 다시 조인합니다.
  • 문자열 _BASE_TABLE로 설정된 경우 색인 대신 색인 전략의 기본 테이블을 사용합니다. 문 힌트 표현식에서 FORCE_INDEX가 사용되는 경우 이 값만 유효한 값입니다.

참고: FORCE_INDEX는 실제로는 힌트가 아니라 지시문이므로 색인이 없으면 오류가 발생합니다.

GROUPBY_SCAN_OPTIMIZATION TRUE
FALSE

그룹화 기준 스캔 최적화를 사용하면 GROUP BY 또는 SELECT DISTINCT를 사용하는 쿼리를 더 빨리 실행할 수 있습니다. 그룹화 키가 기본 테이블 또는 색인 키의 프리픽스를 형성할 수 있으며 쿼리가 각 그룹의 첫 번째 행만 필요로 하는 경우에 이러한 최적화를 적용할 수 있습니다.

옵티마이저가 쿼리를 더 효율적으로 수행할 것으로 예상하는 경우 최적화가 적용됩니다. 힌트는 그러한 결정보다 우선합니다. 힌트를 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_operation

JOIN 연산을 참조하세요.

query_expr

( query_expr ) [ [ 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_operator

UNNEST 연산자를 참조하세요.

cte_name

WITH의 공통 테이블 표현식(CTE)은 FROM 절의 어디에서나 참조할 수 있는 임시 테이블처럼 작동합니다. 아래 예시에서 subQ1subQ2는 CTE입니다.

예를 들면 다음과 같습니다.

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

사용자가 테이블 이름을 한정하지 않는 한 WITH 절은 쿼리 기간 동안 같은 이름의 영구 테이블을 숨깁니다.

db.Roster.

UNNEST 연산자

unnest_operator:
    {
      UNNEST( array_expression )
      | UNNEST( array_path )
      | array_path
    }
    [ table_hint_expr ]
    [ as_alias ]
    [ WITH OFFSET [ as_alias ] ]

UNNEST 연산자는 ARRAY를 취하고 ARRAY의 각 요소에 대한 행이 한 개씩 포함된 테이블을 반환합니다. FROM 절 외부의 UNNESTIN 연산자를 사용할 수도 있습니다.

대부분의 요소 유형의 입력 ARRAY의 경우 일반적으로 UNNEST 출력에는 열이 한 개 있습니다. 이 단일 열에는 alias(선택사항)가 있어 쿼리의 다른 위치에서 열을 참조할 때 사용될 수 있습니다. 이러한 요소 유형을 사용하는 ARRAYS는 열을 여러 개 반환합니다.

  • STRUCT

UNNEST는 입력 ARRAY의 요소 순서를 무시합니다. WITH OFFSET 절(선택사항)을 사용하면 배열 요소 색인을 포함한 두 번째 열을 반환할 수 있습니다(다음 참조).

생성, 평면화, 필터링을 포함하여 UNNEST를 사용하는 여러 가지 방법은 Working with arrays를 참조하세요.

UNNEST 및 STRUCT

STRUCT의 입력 ARRAY의 경우 UNNESTSTRUCT의 각 필드에 대해 별도의 열을 만들고 각 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}     |
+---+-----+--------------+

명시적 및 암시적 UNNEST

ARRAY 중첩 해제는 명시적이거나 암시적일 수 있습니다. 명시적 중첩 해제에서 array_expressionARRAY 값을 반환해야 하지만 ARRAY으로 해석될 필요는 없으며 UNNEST 키워드가 필요합니다.

예를 들면 다음과 같습니다.

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

암시적 중첩 해제에서 array_pathARRAY로 해석되어야 하며 UNNEST 키워드는 선택사항입니다.

예를 들면 다음과 같습니다.

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

이 시나리오에서 array_path는 데이터 구조의 임의의 깊이까지 들어갈 수 있지만 마지막 필드는 ARRAY 유형이어야 합니다. ARRAY에서 명명된 필드를 추출할 수 없으므로 표현식의 이전 필드는 ARRAY 유형이 될 수 없습니다.

UNNEST 및 NULL

UNNEST는 NULL을 다음과 같이 처리합니다.

  • NULL과 빈 배열은 행 0개를 생성합니다.
  • NULL을 포함하는 배열은 NULL 값을 포함하는 행을 생성합니다.

선택사항인 WITH OFFSET절은 UNNEST 연산으로 생성된 각 행의 'offset' 값(즉, 카운트가 0에서 시작)을 포함하는 별도의 열을 반환합니다. 이 열에는 alias(선택사항)이 있고, 기본 별칭은 offset입니다.

예를 들면 다음과 같습니다.

SELECT * FROM UNNEST ( ) WITH OFFSET AS num;

TABLESAMPLE 연산자

tablesample_clause:
    TABLESAMPLE sample_method (sample_size percent_or_rows )

sample_method:
    { BERNOULLI | RESERVOIR }

sample_size:
    numeric_value_expression

percent_or_rows:
    { PERCENT | ROWS }

partition_by:
    PARTITION BY partition_expression [, ...]

설명

TABLESAMPLE 연산자를 사용하여 데이터 세트의 무작위 샘플을 선택할 수 있습니다. 이 연산자는 많은 양의 데이터가 있고 정확한 답변이 필요하지 않은 테이블을 사용하여 작업하는 경우에 유용합니다.

  • sample_method: TABLESAMPLE 연산자 사용 시 사용할 샘플링 알고리즘을 지정해야 합니다.
    • BERNOULLI: 각 행이 percent 절에 주어진 확률로 독립적으로 선택됩니다. 결과적으로 약 N * percent/100개의 행이 생깁니다.
    • RESERVOIR: 실제 샘플 크기 K(행 수로 표현됨)를 매개변수로 사용합니다. 입력이 K보다 작으면 전체 입력 관계를 출력합니다. 입력이 K보다 크면 저장소 샘플링은 크기 K와 정확한 샘플을 출력합니다. 여기서 크기 K의 모든 샘플은 거의 동일합니다.
  • sample_size: 샘플의 크기입니다.
  • percent_or_rows: 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;

JOIN 연산

join_operation:
    { cross_join_operation | join_operation_with_condition }

cross_join_operation:
    from_item CROSS JOIN [ join_hint_expr ] from_item

join_operation_with_condition:
    from_item [ join_type ] [ join_method ] JOIN [ join_hint_expr ] from_item
    [ { on_clause | using_clause } ]

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 } on_clause: ON bool_expression using_clause: USING ( join_column [, ...] )

JOIN 작업은 from_item 두 개를 병합하여 SELECT 절이 이들을 소스 한 개로 쿼리할 수 있도록 합니다. join_typeON 또는 USING 절('조인 조건')은 단일 소스를 형성하도록 from_item 두 개에서 행을 결합 및 삭제하는 방법을 지정합니다.

모든 JOIN 연산에는 join_type이 필요합니다. JOIN 작업에 join_type이 제공되지 않으면 INNER JOIN이 수행됩니다.

다음 조건 중 하나가 true인 경우를 제외하고 JOIN 작업에 조인 조건이 필요합니다.

  • join_typeCROSS입니다.
  • from_item 중 하나 또는 둘 모두가 테이블이 아닙니다(예: array_path 또는 field_path).

Join 힌트

JOIN에 다음 힌트가 지원됩니다.

힌트 키 가능한 값 설명
FORCE_JOIN_ORDER TRUE
FALSE(기본값)
True로 설정하면 쿼리에 지정된 조인 순서가 사용됩니다.
JOIN_METHOD HASH_JOIN
APPLY_JOIN
MERGE_JOIN
PUSH_BROADCAST_HASH_JOIN
논리적 조인 구현 시 기본 조인 메소드에 사용할 특정 대안을 선택합니다. 조인 메서드에서 자세히 알아보세요.
HASH 조인을 사용하려면 HASH JOIN 또는 JOIN@{JOIN_METHOD=HASH_JOIN} 중 하나만 사용합니다.
HASH_JOIN_BUILD_SIDE BUILD_LEFT
BUILD_RIGHT
빌드 측으로 해시 조인의 어느 쪽이 사용되는지를 지정합니다. JOIN_METHOD=HASH_JOIN으로만 사용할 수 있습니다.
BATCH_MODE TRUE (default)
FALSE
한 번에 하나의 행을 적용하기 위해 일괄 적용 조인을 사용 중지하는 데 사용됩니다. JOIN_METHOD=APPLY_JOIN으로만 사용할 수 있습니다.

조인 메서드

조인 메소드는 다양한 논리 조인 유형이 특정하게 구현된 것입니다. 일부 조인 메소드는 특정 조인 유형에만 사용할 수 있습니다. 퀴리의 구체적 내용과 쿼리 대상 데이터에 따라 사용할 조인 메소드를 선택합니다. 특정 조인 메서드가 쿼리 수행에 유용한지 확인하는 가장 좋은 방법은 메서드를 시도하고 결과 쿼리 실행 계획을 보는 것입니다. 자세한 내용은 쿼리 실행 연산자를 참조하세요.

조인 메서드 설명 피연산자
HASH_JOIN 해시 조인 연산자는 한쪽(빌드 측)에서 해시 테이블을 작성하고 다른 쪽(프로브 측)의 모든 요소에 대해 해시 테이블에서 프로브를 작성합니다. 여러 가지 변형이 다양한 조인 유형에 사용됩니다. 쿼리의 쿼리 실행 계획에서 어떤 변형이 사용되고 있는지 확인합니다. 해시 조인 연산자에 대해 자세히 알아보세요.
APPLY_JOIN 적용 조인 연산자는 각 항목을 한쪽(입력 측)에서 가져오고 입력 측의 항목 값을 사용하여 다른 쪽(맵 측)에서 서브 쿼리를 평가합니다. 여러 가지 변형이 다양한 조인 유형에 사용됩니다. 교차 적용은 내부 조인에 사용되고 외부 적용은 왼쪽 조인에 사용됩니다. 교차 적용외부 적용 연산자에 대해 자세히 알아보세요.
MERGE_JOIN 병합 조인 연산자는 2개의 정렬 데이터 스트림을 조인합니다. 지정된 조인 조건에 대해 데이터가 필요한 정렬 속성을 아직 제공하지 않으면 옵티마이저가 계획에 정렬 연산자를 추가합니다. 엔진은 기본적으로 분산 병합 정렬을 제공합니다. 이를 병합 조인과 함께 사용하면 더 큰 조인이 허용되어, 디스크 스필링을 방지하고 확장성 및 지연 시간을 향상시킬 수 있습니다. 여러 가지 변형이 다양한 조인 유형에 사용됩니다. 쿼리의 쿼리 실행 계획에서 어떤 변형이 사용되고 있는지 확인합니다. 병합 조인 연산자에 대해 자세히 알아보세요.
PUSH_BROADCAST_HASH_JOIN 푸시 브로드캐스트 해시 조인 연산자는 조인의 빌드 측으로부터 데이터 배치를 빌드합니다. 그런 후 배치가 해당 조인의 프로브 측의 모든 로컬 분할로 동시에 전송됩니다. 각 로컬 서버에서 해시 조인은 배치와 로컬 데이터 사이에 실행됩니다. 이 조인은 한 배치 내에 입력이 들어갈 수 있지만 엄격하지 않은 경우에 유용성이 가장 높을 수 있습니다. 또한 조인 후 발생하는 집계와 같이 로컬 서버에 작업을 분산할 수 있는 경우에도 이점을 얻을 수 있습니다. 푸시 브로드캐스트 해시 조인은 기존 해시 조인이 할 수 없는 몇 가지 집계를 분산할 수 있습니다. 여러 가지 변형이 다양한 조인 유형에 사용됩니다. 쿼리의 쿼리 실행 계획에서 어떤 변형이 사용되고 있는지 확인합니다. 푸시 브로드캐스트 해시 조인 연산자에 대해 자세히 알아보세요.

[INNER] JOIN

INNER JOIN(또는 간단히 JOIN)은 두 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 | k |     | 2 | b | 2 | k |
| 2 | b |     | 3 | m |     | 3 | c | 3 | m |
| 3 | c |     | 3 | n |     | 3 | c | 3 | n |
| 3 | d |     | 4 | p |     | 3 | d | 3 | m |
+-------+     +-------+     | 3 | d | 3 | n |
                            +---------------+
FROM A INNER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +-----------+
| x | y |  *  | x | z |  =  | x | y | z |
+-------+     +-------+     +-----------+
| 1 | a |     | 2 | k |     | 2 | b | k |
| 2 | b |     | 3 | m |     | 3 | c | m |
| 3 | c |     | 3 | n |     | 3 | c | n |
| 3 | d |     | 4 | p |     | 3 | d | m |
+-------+     +-------+     | 3 | d | n |
                            +-----------+

예시

이 쿼리는 RosterTeamMascot 테이블에서 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은 두 from_item의 카티전 프로덕트를 반환합니다. 즉, 첫 번째 from_item의 각 행과 두 번째 from_item의 각 행을 결합합니다.

2개의 from_item 행이 독립적이면 결과에 M * N개 행이 포함되며, M개 행은 1개의 from_item에 있고 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 열을 평면화할 수 있습니다. 이 경우 두 번째 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 시퀀스를 참조하세요.

예시

이 쿼리는 RosterTeamMascot 테이블에서 명시적인 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는 한 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 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+
FROM A FULL OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+

예시

이 쿼리는 RosterTeamMascot 테이블에서 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

오른쪽 from_item의 행이 조인 조건을 충족하지 않더라도 두 from_item에 대한 한 LEFT OUTER JOIN(또는 간단히 LEFT JOIN)의 결과는 항상 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 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            +---------------------------+
FROM A LEFT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            +--------------------+

예시

이 쿼리는 RosterTeamMascot 테이블에서 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 | k |     | 2    | b    | 2    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | 3    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | 3    | m    |
+-------+     +-------+     | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+
FROM A RIGHT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 2    | b    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | m    |
+-------+     +-------+     | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+

예시

이 쿼리는 RosterTeamMascot 테이블에서 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를 반환하면 결합된 행(두 행을 조인한 결과)은 조인 조건을 충족합니다.

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 |     +-------+
+---+     +---+

예시

이 쿼리는 RosterTeamMascot 테이블에서 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 절에는 두 입력 테이블 모두에서 발생하는 열이 한 개 이상인 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 |     +---+
+---+     +---+

예시

이 쿼리는 RosterTeamMascot 테이블에서 INNER JOIN을 수행합니다.

이 문은 RosterTeamMascot의 행을 반환합니다. 여기서 Roster.SchooldIDTeamMascot.SchooldID과 동일합니다. 결과에는 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는 한 개의 열을 반환합니다.

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 절은 FROM 절의 결과를 필터링합니다.

bool_expressionTRUE로 평가된 행만 포함됩니다. bool_expressionNULL 또는 FALSE로 평가되는 행은 삭제됩니다.

WHERE 절이 있는 쿼리 평가는 일반적으로 다음 순서로 완료됩니다.

  • FROM
  • WHERE
  • GROUP BY 및 집계
  • HAVING
  • WINDOW
  • QUALIFY
  • DISTINCT
  • ORDER BY
  • LIMIT

WHERE 절은 FROM 절을 통해 사용할 수 있는 열만 참조할 수 있으며 SELECT 목록 별칭을 참조할 수 없습니다.

예시

이 쿼리는 SchoolID 열의 값이 52Roster 테이블의 모든 행을 반환합니다.

SELECT * FROM Roster
WHERE SchoolID = 52;

bool_expression에는 하위 조건 여러 개가 포함될 수 있습니다.

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

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에서 구분되지 않는 값이 있는 테이블의 행을 그룹화합니다. expression에 구분되지 않는 값이 있는 소스 테이블의 행이 여러 개인 경우 GROUP BY 절은 결합된 단일 행을 생성합니다. GROUP BY는 집계 함수가 SELECT 목록에 있거나 출력의 중복성을 제거할 때 주로 사용됩니다. expression의 데이터 유형은 그룹화 가능이어야 합니다.

예를 들면 다음과 같습니다.

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

GROUP BY 절은 SELECT 목록의 표현식 이름을 참조할 수 있습니다. 또한 GROUP BY 절은 정수 값을 사용하는 SELECT 목록의 표현식에 대한 서수 참조도 허용합니다. 1SELECT 목록의 첫 번째 표현식을 참조하고 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 절은 GROUP BY 또는 집계로 생성된 결과를 필터링합니다. GROUP BY 또는 집계가 쿼리에 있어야 합니다. 집계가 있는 경우 HAVING 절은 결과 집합에서 집계된 행마다 1회 평가됩니다.

bool_expressionTRUE로 평가된 행만 포함됩니다. bool_expressionNULL 또는 FALSE로 평가되는 행은 삭제됩니다.

HAVING 절이 있는 쿼리 평가는 일반적으로 다음 순서로 완료됩니다.

  • FROM
  • WHERE
  • GROUP BY 및 집계
  • HAVING
  • WINDOW
  • QUALIFY
  • DISTINCT
  • ORDER BY
  • LIMIT

HAVING 절은 SELECT 목록 별칭뿐만 아니라 FROM 절을 통해 사용할 수 있는 열을 참조할 수 있습니다. 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
  [COLLATE collate_string]
  [{ ASC | DESC }]
  [, ...]

ORDER BY 절은 열 또는 표현식을 결과 집합의 정렬 기준으로 지정합니다. ORDER BY 절이 없으면 쿼리 결과 순서는 정의되지 않습니다. FROM 절 또는 SELECT 목록의 열 별칭이 허용됩니다. 쿼리에 SELECT 절의 별칭이 포함되어 있으면 이 별칭은 해당 FROM 절의 이름을 재정의합니다. expression의 데이터 유형은 정렬 가능이어야 합니다.

옵션 절

  • COLLATE: 데이터가 정렬되는 방식을 구체화합니다.
  • 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 절이 두 번째 SELECT 문에만 적용되는 다음 쿼리와는 다릅니다.

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

또한 ORDER BY 절에서 정수 리터럴을 열 참조로 사용할 수 있습니다. 정수 리터럴은 SELECT 목록의 서수(예: 1부터 카운트 시작)가 됩니다.

예 - 다음 두 쿼리는 동일합니다.

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

COLLATE 절

COLLATE collate_string

collate_string:
  language_tag[:collation_attribute]

COLLATE 절을 사용하여 ORDER BY 절에서 데이터가 정렬되는 방식을 구체화할 수 있습니다. 대조란 특정 기록 언어, 지역, 국가의 관습과 표준에 따라 문자열이 비교되는 방식을 결정하는 규칙 집합입니다. 이러한 규칙은 올바른 문자 순서 및 대소문자 구분 여부를 정의할 수 있습니다.

collate_stringlanguage_tag을 포함하며, 선택적으로 콜론으로 구분된 collation_attribute 서픽스를 가질 수 있습니다.

language_tag은 리터럴 또는 매개변수입니다.

  • 표준 언어 문자열입니다. 이 이름은 일반적으로 언어를 나타내는 2~3개의 문자이며, 필요에 따라 밑줄이나 대시, 그리고 지역을 나타내는 2개의 문자(예: en_US)가 뒤에 옵니다. 이러한 이름은 Common Locale Data Repository(CLDR)에 의해 정의됩니다. 아래의 유니코드 대조를 참조하세요.

  • und: 미확인 언어를 나타내는 언어 문자열입니다. 아래의 유니코드 대조를 참조하세요.

  • unicode: 자세한 내용은 아래를 참조하세요.

collate_stringlanguage_tag 이외에 선택적으로 콜론으로 구분된 collation_attribute 서픽스를 가질 수 있습니다. 허용되는 값은 다음과 으며, + ci는 대소문자를 구분하지 않는 경우, + cs는 대소문자를 구분하는 경우입니다(참고: 'cs'가 기본값이므로 아무런 영향을 주지 않음).

유니코드 대조

unicode가 아닌 language_tag의 경우 Cloud Spanner SQL은 유니코드 대조 알고리즘을 따릅니다. 이 표준은 언어 태그의 형식을 정의하며, 여기에는 유용한 확장 프로그램 및 비교에 사용되는 알고리즘이 포함됩니다.

undIANA 언어 하위 태그 레지스트리에 정의된 특수 언어 태그이며 미확인 언어를 나타내는 데 사용됩니다. 루트 언어라고도 하며 기본 유니코드 대조로 간주될 수 있습니다. 이는 대조에 상관없이 합리적인 언어를 정의합니다. unicode와 크게 다릅니다.

-u-<extension>을 추가하여 language_tag를 확장할 수 있습니다. 예를 들어 숫자 순서를 지정하는 확장은 kn-true입니다. 따라서 en-us-u-kn-true는 영어(미국식)를 숫자 정렬로 나타냅니다(abc1abc12보다 작은 것으로 간주). 확장 프로그램의 몇 가지 유용한 예는 다음과 같습니다.

확장 이름
-ks-level2 대소문자를 구분하지 않음 'a1' < 'A2'
-ks-level1 억양 및 대소문자를 구분하지 않음 'ä1' < 'a2' < 'A3'
-ks-level1-kc-true 억양을 구분하지 않음 'ä1' < 'a2'
-kn-true 숫자 순서 'a1b' < 'a12b'

전체 목록 및 자세한 기술 세부정보는 유니코드 언어 데이터 마크업 언어 5부: 대조를 참조하세요.

주의사항:

  • 서로 다른 문자열이 동일한 것으로 간주될 수 있습니다. 예를 들어 ẞ(라틴어 대문자 SHARP S)는 기본 수준에서 'SS'와 동일한 것으로 간주되므로 'ẞ1' < 'SS2'입니다. 이는 대소문자를 구분하지 않는 방식과 비슷합니다.

  • 무시할 수 있는 코드 포인트: 유니코드 대조는 대체로 존재하지 않는 것처럼 취급되는 다양한 코드 포인트를 지정합니다. 따라서 U2060 - 'Word JOINER'와 같이 문자열 포함 여부에 관계없이 문자열이 동일하게 정렬됩니다.

      SELECT "oran\u2060ge1" UNION ALL SELECT "\u2060orange2" UNION ALL SELECT "orange3"
      ORDER BY 1 COLLATE "und"
      +---------+
      |         |
      +---------+
      | orange1 |
      | orange2 |
      | orange3 |
      +---------+
    
  • 순서가 변경될 수 있습니다. 유니코드는 드물게 기본 대조("und")를 변경하여 문자열의 상대적 순서를 변경할 수 있습니다. 'und'가 아닌 언어의 정렬 순서는 표준이 변경되거나 새로운 정보가 수집될 때 더 자주 변경됩니다. 고정 정렬 순서가 필요한 경우 unicode를 사용합니다.

또한 unicodelanguage_tag가 지원됩니다.

  • unicode: 유니코드 코드 포인트 순서로 데이터를 반환합니다. 이는 COLLATE가 사용되지 않을 때의 순서 지정 동작과 동일합니다. 정렬 순서는 대부분 사용자에게 임의로 표시됩니다.
  • unicode:cs: unicode와 동일함
  • unicode:ci: und:ci와 동일함

예시

캐나다 영어를 사용하여 결과 대조:

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"

기본 유니코드로 대소문자를 구분하지 않고 대조:

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

집합 연산자

set_operation:
  query_expr set_operator query_expr

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

집합 연산자는 입력 쿼리 결과 두 개 이상을 단일 결과 조합으로 결합합니다. ALL 또는 DISTINCT를 지정해야 합니다. 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의 경우 DISTINCTUNION이 계산된 후 계산되므로 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 연산자는 왼쪽 입력 쿼리에서 오른쪽 입력 쿼리에 없는 행을 반환합니다.

예를 들면 다음과 같습니다.

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개 반환합니다.

집합 연산이 있으면 집합 연산이 평가된 후에 LIMIT가 적용됩니다.

OFFSETLIMIT을 적용하기 전에 건너뛸 행 수를 지정합니다. 이때 이 수는 음수가 아닌 값입니다. 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 cte[, ...]

WITH 절에는 하나 이상의 공통 테이블 표현식(CTE)이 포함됩니다. CTE는 단일 쿼리 표현식 내에서 참조할 수 있는 임시 테이블과 같이 작동합니다. 각 CTE는 서브 쿼리의 결과를 테이블 이름에 결합합니다. 이 이름은 동일한 쿼리 표현식의 어디에든 사용할 수 있지만 규칙이 적용됩니다.

CTE

cte:
    cte_name AS ( query_expr )

공통 테이블 표현식(CTE)에는 서브 쿼리와 CTE와 연결된 이름이 포함됩니다.

  • CTE는 자신을 참조할 수 없습니다.
  • CTE는 WITH 절을 포함하는 쿼리 표현식에서 참조될 수 있지만 규칙이 적용됩니다.
예시

이 예시에서 WITH 절은 관련 집합 연산에서 참조되는 2개의 CTE를 정의합니다. 각 CTE는 집합 연산의 각 입력 쿼리 표현식에 의해 참조됩니다.

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 절은 DML 문에서 지원되지 않습니다.

WITH 절로 정의된 임시 테이블은 메모리에 저장됩니다. Cloud Spanner SQL은 쿼리로 생성된 모든 임시 테이블에 대해 메모리를 동적으로 할당합니다. 사용 가능한 리소스가 충분하지 않으면 쿼리가 실패합니다.

CTE 규칙 및 제약조건

공통 테이블 확장(CTE)은 WITH 절을 포함하는 쿼리 표현식 내에서 참조할 수 있습니다.

CTE를 사용할 때 고려해야 하는 일반 규칙 및 제약조건은 다음과 같습니다.

  • 동일한 WITH 절의 각 CTE는 이름이 고유해야 합니다.
  • WITH 절에 정의된 CTE는 그 뒤에 정의된 동일한 WITH 절의 다른 CTE에만 표시됩니다.
  • 로컬 CTE는 동일한 이름의 외부 CTE 또는 테이블을 재정의합니다.
  • 서브 쿼리의 CTE는 외부 쿼리에서 상관 관계가 있는 열을 참조할 수 없습니다.

CTE 공개 상태

WITH 절에서 공통 테이블 표현식(CTE)은 서로 역방향으로 이동할 수 있지만 전달할 수는 없습니다.

이 경우는 2개의 CTE가 WITH 절에서 자체 또는 서로를 참조하는 경우에 발생합니다. 여기에서는 A가 첫 번째 CTE이고 B가 두 번째 CTE라고 가정합니다.

  • A 참조 A = 잘못됨
  • A 참조 B = 잘못됨
  • B 참조 A = 유효함
  • A 참조 B 참조 A = 잘못됨(주기는 허용되지 않음)

이렇게 하면 오류가 발생합니다. 자체 참조가 지원되지 않기 때문에 A가 자신을 참조할 수 없습니다.

WITH
  A AS (SELECT 1 AS n UNION ALL (SELECT n + 1 FROM A WHERE n < 3))
SELECT * FROM A

-- Error

이렇게 하면 오류가 발생합니다. CTE 사이의 참조가 정방향이 아닌 역방향으로 실행될 수 있기 때문에 AB를 참조할 수 없습니다.

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT 1 AS n)
SELECT * FROM B

-- Error

CTE 사이의 참조가 역방향으로 실행될 수 있기 때문에 BA를 참조할 수 있습니다.

WITH
  A AS (SELECT 1 AS n),
  B AS (SELECT * FROM A)
SELECT * FROM B

+---+
| n |
+---+
| 1 |
+---+

이렇게 하면 오류가 발생합니다. AB가 서로를 참조하여, 순환이 발생합니다.

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT * FROM A)
SELECT * FROM B

-- Error

별칭 사용

별칭은 쿼리에 있는 테이블, 열 또는 표현식에 제공되는 임시 이름입니다. 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;

암시적 별칭

SELECT 목록에서 명시적 별칭이 없는 표현식이 있으면 Cloud Spanner SQL은 다음 규칙을 따라 암시적 별칭을 할당합니다. SELECT 목록에는 동일한 별칭을 가진 열이 여러 개 있을 수 있습니다.

  • 식별자의 경우 별칭은 식별자입니다. 예를 들어 SELECT abcAS abc를 암시합니다.
  • 경로 표현식의 경우 별칭은 경로의 마지막 식별자입니다. 예를 들어 SELECT abc.def.ghiAS ghi을 암시합니다.
  • 'dot' 구성원 필드 액세스 연산자를 사용하는 필드 액세스의 경우 별칭은 필드 이름입니다. 예를 들어 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)에는 암시적 별칭이 없습니다.

별칭 가시성

쿼리에 명시적 별칭을 사용하면 쿼리에서 해당 별칭을 참조할 수 있는 다른 위치가 제한됩니다. 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 BY, ORDER BY, HAVING의 세 가지 절은 다음 값만 참조할 수 있습니다.

  • FROM 절의 테이블과 해당 열
  • SELECT 목록의 별칭

또한 GROUP BYORDER BY는 세 번째 그룹을 참조할 수 있습니다.

  • SELECT 목록의 항목을 참조하는 정수 리터럴. 정수 1SELECT 목록의 첫 번째 항목을 참조하고 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;

중복된 별칭

같은 이름의 여러 명시적 또는 암시적 별칭을 포함하는 SELECT 목록 또는 서브 쿼리는 참조가 모호할 수 있기 때문에 별칭 이름이 쿼리의 다른 곳에서 참조되지 않는 한 허용됩니다.

예를 들면 다음과 같습니다.

SELECT 1 AS a, 2 AS a;

+---+---+
| a | a |
+---+---+
| 1 | 2 |
+---+---+

모호한 별칭

이름 액세스가 모호한 경우, 대상 테이블의 스키마를 포함하여 쿼리 또는 테이블 스키마에서 2개 이상의 고유 객체로 확인될 수 있는 경우에는 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 절로는 모호한 별칭이 포함되어 있습니다. tablex, y, z 열이 있다고 가정합시다. z는 STRUCT 유형이며 v, w, x 필드가 있습니다.

예를 들면 다음과 같습니다.

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

별칭 T는 모호하며 GROUP BY 절의 T.xtable.x 또는 table.z.x를 참조할 수 있으므로 오류가 발생합니다.

열 이름이면서 SELECT 목록 별칭인 이름은 동일한 기본 객체로 확인되기만 한다면 GROUP BY, ORDER BY 또는 HAVING에서 모호하지 않습니다.

예를 들면 다음과 같습니다.

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

별칭 BirthYear는 동일한 기본 열인 Singers.BirthYear로 확인되므로 모호하지 않습니다.

범위 변수

Cloud Spanner SQL에서 범위 변수는 FROM 절의 테이블 표현식 별칭입니다. 경우에 따라 범위 변수를 table alias라고도 부릅니다. 범위 변수를 사용하면 테이블 표현식에서 스캔되는 행을 참조할 수 있습니다. 테이블 표현식은 테이블을 반환하는 FROM 절의 항목을 나타냅니다. 이 표현식이 나타낼 수 있는 일반적인 항목에는 테이블, 값 테이블, 서브 쿼리, 조인, 괄호로 묶인 조인이 포함됩니다.

일반적으로 범위 변수는 테이블 표현식의 행에 대한 참조를 제공합니다. 범위 변수를 사용하여 열 참조를 한정하고 관련 테이블(예: range_variable.column_1)을 명확하게 식별할 수 있습니다.

지정된 열 서픽스 없이 그 자체로 범위 변수를 참조할 때 테이블 표현식의 결과는 관련 테이블의 행 유형입니다. 값 테이블에 명시적 열 유형이 포함되므로, 값 테이블 관련 범위 변수에서 결과 유형은 값 테이블의 행 유형입니다. 다른 테이블에는 명시적 행 유형이 포함되지 않으며, 이러한 테이블에서 범위 변수 유형은 해당 테이블의 모든 열이 포함되는 동적으로 정의된 STRUCT입니다.

예시

이러한 예시에서 WITH 절은 Grid라는 임시 테이블을 에뮬레이션하기 위해 사용됩니다. 이 테이블에는 xy 열이 포함됩니다. Coordinate라는 범위 변수는 테이블을 스캔할 때 현재 행을 참조합니다. Coordinate를 사용하면 전체 행 또는 행의 열에 액세스할 수 있습니다.

다음 예시에서는 범위 변수 Coordinate에서 x 열을 선택합니다. 이는 실제로 Grid 테이블에서 x 열을 선택하는 것과 같습니다.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.x FROM Grid AS Coordinate;

+---+
| x |
+---+
| 1 |
+---+

다음 예시에서는 범위 변수 Coordinate에서 모든 열을 선택합니다. 이는 Grid 테이블에서 모든 열을 선택하는 것과 같습니다.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.* FROM Grid AS Coordinate;

+---+---+
| x | y |
+---+---+
| 1 | 2 |
+---+---+

다음 예시에서는 범위 변수 Coordinate를 선택합니다. 이는 Grid 테이블의 행에 대한 참조입니다. Grid가 값 테이블이 아니기 때문에 Coordinate의 결과 유형은 Grid의 모든 열이 포함된 STRUCT입니다.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate FROM Grid AS Coordinate;

+--------------+
| Coordinate   |
+--------------+
| {x: 1, y: 2} |
+--------------+

부록 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 문의 결과 조합에 있는 열을 쌍으로 만들고 이를 세로로 연결하여 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)