Sintaxe de consulta do SQL padrão do Google

Com as instruções de consulta, você verifica uma ou mais tabelas ou expressões e retorna as linhas de resultado. Neste tópico, descrevemos a sintaxe de consultas SQL no SQL padrão.

Sintaxe do 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
    }

Regras de anotação

• Os colchetes "[ ]" indicam cláusulas opcionais.
• Os parênteses "( )" indicam parênteses literais.
• A barra vertical "|" indica um operador lógico "OR".
• As chaves "{ }" delimitam um conjunto de opções.
• Uma vírgula seguida por reticências entre colchetes "[, ... ]" indica que o item anterior pode se repetir em uma lista separada por vírgulas.

Dicas de instrução

As seguintes dicas são compatíveis com as instruções de consulta:

Chave da dica	Valores possíveis	Descrição
USE_ADDITIONAL_PARALLELISM	TRUE FALSE (padrão)	Se for TRUE, o mecanismo de execução favorece o uso de mais paralelismo sempre que possível. Como isso pode reduzir os recursos disponíveis para outras operações, evite essa dica se você executar operações sensíveis à latência na mesma instância.
OPTIMIZER_VERSION	1 a N|latest_version|default_version	Executa a consulta usando a versão do otimizador especificada. Os valores possíveis são 1 para N (a versão mais recente do otimizador), default_version ou latest_version. Se a dica não estiver definida, o otimizador será executado no pacote definido nas opções de banco de dados ou especificado pela API do cliente. Se nenhum deles estiver definido, o otimizador usará a versão padrão. Em termos de precedência de configuração de versão, o valor definido pela API do cliente tem precedência sobre o valor nas opções do banco de dados e o valor definido por essa dica. tem precedência sobre todo o restante. Saiba mais em otimizador de consultas.
OPTIMIZER_STATISTICS_PACKAGE	package_name|latest	Executa a consulta usando o pacote especificado de estatísticas do otimizador. Os valores possíveis para package_name podem ser encontrados executando a seguinte consulta: SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS Se a dica não estiver definida, o otimizador será executado no pacote definido na opção de banco de dados ou especificado pela API do cliente. Se nenhum deles estiver definido, o otimizador usará o pacote mais recente por padrão. O valor definido pela API do cliente tem precedência sobre o valor nas opções do banco de dados, e o valor definido por essa dica tem precedência sobre todo o restante. O pacote especificado precisa ser fixado pela opção de banco de dados ou ter allow_gc=false para evitar a coleta de lixo. Para mais informações, consulte Pacotes de estatísticas do otimizador de consultas.
ALLOW_DISTRIBUTED_MERGE	TRUE (padrão) FALSE	Se TRUE (padrão), o mecanismo favorece o uso de um algoritmo de classificação de mesclagem distribuída para determinadas consultas ORDER BY. Quando aplicável, as classificações globais são alteradas para classificações locais. Com isso, você tem a vantagem da classificação paralela perto do local em que os dados são armazenados. Em seguida, os dados classificados localmente são mesclados para fornecer dados classificados globalmente. Isso permite a remoção de classificações globais completas e latência potencialmente aprimorada. Esse recurso pode aumentar o paralelismo de determinadas consultas ORDER BY. A dica foi fornecida para que os usuários possam testar a desativação do algoritmo de mesclagem distribuído, se desejado.
LOCK_SCANNED_RANGES	exclusive shared (padrão)	Use esta dica para solicitar um bloqueio exclusivo em um conjunto de intervalos verificados por uma transação. A aquisição de um bloqueio exclusivo é útil quando você observa alta contenção de gravação, ou seja, percebe que várias transações tentam simultaneamente ler e gravar os mesmos dados, resultando em um grande número de cancelamentos. Sem a dica, é possível que várias transações simultâneas adquiram bloqueios compartilhados e tentem fazer upgrade para bloqueios exclusivos. Isso causará um impasse, porque o bloqueio compartilhado de cada transação impede que outras façam upgrade para bloqueio exclusivo. O Cloud Spanner cancela todas as transações, exceto uma. Ao solicitar um bloqueio exclusivo usando essa dica, uma transação adquire o bloqueio e continua a ser executada, enquanto outras aguardam a vez para fazer isso. A capacidade ainda é limitada porque as transações conflitantes só podem ser realizadas uma de cada vez. Mas, nesse caso, o Cloud Spanner sempre está realizando progresso em uma transação, economizando tempo que seria gasto cancelando e tentando as transações de novo. Essa dica é compatível com todos os tipos de instrução, tanto a consulta quanto a DML. O Cloud Spanner sempre impõe capacidade de serialização. As dicas do modo bloqueado podem afetar quais transações aguardarão ou quais serão canceladas em cargas de trabalho interrompidas, mas não alteram o nível de isolamento. Como esta é apenas uma dica, ela não deve ser considerada como um mutex. Em outras palavras, não convém usar bloqueios exclusivos do Cloud Spanner como um mecanismo de exclusão mútua para a execução de código fora do Cloud Spanner. Para mais informações, consulte Bloqueio.

Tabelas de amostra

As tabelas a seguir são usadas para ilustrar o comportamento de diferentes cláusulas de consulta nesta referência.

Tabela Roster

A tabela Roster inclui uma lista dos nomes de jogadores (LastName) e o ID exclusivo atribuído à escola (SchoolID). Ele tem esta aparência:

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

Use esta cláusula WITH para emular um nome de tabela temporário para os exemplos nesta referência:

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

Tabela PlayerStats

A tabela PlayerStats inclui uma lista dos nomes de jogadores (LastName), o ID exclusivo atribuído ao oponente com quem ele jogou (OpponentID) e o número de pontos marcados (PointsScored).

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

Use esta cláusula WITH para emular um nome de tabela temporário para os exemplos nesta referência:

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

Tabela TeamMascot

Na tabela TeamMascot, há uma lista de IDs exclusivos das escolas (SchoolID) e o mascote da escola (Mascot).

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

Use esta cláusula WITH para emular um nome de tabela temporário para os exemplos nesta referência:

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

Lista de SELECT

SELECT [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }]
    { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
        [ REPLACE ( expression [ AS ] column_name [, ...] ) ]
    | expression [ [ AS ] alias ] } [, ...]

A lista de SELECT define as colunas retornadas na consulta. As expressões na lista de SELECT referem-se a colunas de qualquer um dos from_item da cláusula FROM correspondente.

Cada item na lista de SELECT tem um dos seguintes formatos:

• *
• expression
• expression.*

SELECT *

SELECT *, geralmente conhecido como select star. Com ele, é possível produzir uma coluna de saída para cada coluna visível após a conclusão da consulta.

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

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

expression SELECT

Os itens de uma lista de SELECT podem ser expressões. Essas expressões são avaliadas como um valor único e produzem uma coluna de saída, com um alias explícito opcional.

Se a expressão não tem um alias explícito, ela recebe um alias implícito de acordo com as regras de aliases implícitos, se possível. Caso contrário, a coluna é anônima, ou seja, o nome dela não pode ser usado como referência em outro lugar da consulta.

expression.* SELECT

Um item em uma lista SELECT também pode assumir a forma de expression.*. Isso produz uma coluna de saída para cada coluna ou campo de nível superior da expression. A expressão precisa ser um alias de tabela ou ser avaliada como um valor único de um tipo de dados com campos, como um STRUCT.

A consulta a seguir produz uma coluna de saída para cada coluna na tabela groceries, com o alias como 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 |
+-------+---------+-------+

Mais exemplos:

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

Modificadores do operador *

SELECT * EXCEPT

Uma instrução SELECT * EXCEPT especifica os nomes de uma ou mais colunas a serem excluídas do resultado. Todas as colunas com nomes correspondentes são omitidas da saída.

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

Observação: SELECT * EXCEPT não exclui colunas sem nomes.

SELECT * REPLACE

Uma instrução SELECT * REPLACE especifica uma ou mais cláusulas expression AS identifier. Cada identificador precisa corresponder a um nome de coluna da instrução SELECT *. Na lista de colunas de saída, a coluna correspondente ao identificador em uma cláusula REPLACE é substituída pela expressão nessa cláusula REPLACE.

Uma instrução SELECT * REPLACE não altera os nomes nem a ordem das colunas. No entanto, o valor e o tipo de valor podem ser alterados.

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

Observação: SELECT * REPLACE não substitui colunas sem nomes.

Manipulação de linhas duplicadas

Modifique os resultados retornados de uma consulta SELECT das seguintes maneiras.

SELECT DISTINCT

Em uma instrução SELECT DISTINCT, as linhas duplicadas são descartadas, e apenas as demais linhas são retornadas. SELECT DISTINCT não pode retornar colunas dos seguintes tipos:

• STRUCT
• ARRAY

SELECT ALL

Uma instrução SELECT ALL retorna todas as linhas, incluindo linhas duplicadas. SELECT ALL é o comportamento padrão de SELECT.

Como usar STRUCTs com SELECT

• As consultas que retornam um STRUCT na raiz do tipo de retorno não são aceitas nas APIs do Cloud Spanner. Por exemplo, a consulta a seguir é aceita apenas como uma subconsulta:

  SELECT STRUCT(1, 2) FROM Users;
  

• O retorno de uma matriz de estruturas é aceito. Por exemplo, as consultas a seguir são compatíveis com as APIs do Cloud Spanner:

  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;
  

• No entanto, as formas de consulta que podem retornar um valor NULL de tipo ARRAY<STRUCT<...>> ou um valor de tipo ARRAY<STRUCT<...>> com um elemento que é NULL não são aceitas nas APIs do Cloud Spanner. Portanto, a consulta a seguir é aceita somente como uma subconsulta:

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

Observação: a lógica dentro do Cloud Spanner que decide se uma consulta pode ou não retornar uma matriz NULL de estrutura ou uma matriz NULL de elementos de estrutura não é completa (no sentido lógico de completa). Isso significa que algumas consultas que claramente não retornam NULLs ainda são rejeitadas e, às vezes, um ajuste fino é necessário para conseguir um formato de consulta compatível. As formas de consulta menos problemáticas usam a subconsulta ARRAY(SELECT AS STRUCT ... ) para construir a matriz de valores de estrutura.

Consulte Como consultar elementos STRUCT em uma ARRAY para ver mais exemplos sobre como consultar STRUCTs dentro de um ARRAY.

Veja também as observações sobre o uso de STRUCTs em subconsultas.

Tabela de valores

No Google SQL padrão, uma tabela de valores é uma tabela em que o tipo de linha é um valor único. Em uma tabela regular, cada linha é composta por colunas, cada uma com um nome e um tipo. Em uma tabela de valores, o tipo de linha é apenas um valor único e não há nomes de coluna.

No Cloud Spanner, as tabelas de valor ocorrem principalmente como a saída do operador UNNEST ou de uma subconsulta. A cláusula WITH introduzirá uma tabela de valores se a subconsulta usada produzir uma tabela de valores. O Cloud Spanner não aceita tabelas de valores como tabelas base em esquemas de banco de dados e não é compatível com tabelas de valor de retorno nos resultados da consulta. Como consequência, as tabelas de valor que produzem consultas não são aceitas como consultas de alto nível.

Em contextos em que uma consulta com exatamente uma coluna é esperada, é possível usar uma consulta à tabela de valores. Por exemplo, subconsultas escalares e subconsultas de matriz (consulte Subconsultas) normalmente exigem uma consulta de coluna única, mas no Google Standard SQL elas também permitem o uso de uma consulta de tabela de valores.

Uma consulta produzirá uma tabela de valores se usar SELECT AS, utilizando uma das sintaxes abaixo:

SELECT AS STRUCT

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

Isso produz uma tabela de valores com um tipo de linha STRUCT, em que os nomes e tipos de campos STRUCT correspondem aos nomes e tipos de colunas produzidos na lista SELECT.

Exemplo:

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

SELECT AS STRUCT pode ser usado em uma subconsulta escalar ou de matriz para produzir um único tipo STRUCT que agrupa vários valores. Subconsultas escalares e de matriz (consulte Subconsultas) normalmente não podem retornar várias colunas, mas podem retornar uma única coluna com o tipo STRUCT.

Colunas anônimas são permitidas.

Exemplo:

SELECT AS STRUCT 1 x, 2, 3

A consulta acima produz valores STRUCT do tipo STRUCT<int64 x, int64, int64>.. O primeiro campo tem o nome x, enquanto o segundo e o terceiro campos são anônimos.

O exemplo acima produz o mesmo resultado que essa consulta SELECT AS VALUE usando um construtor struct:

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

Colunas duplicadas são permitidas.

Exemplo:

SELECT AS STRUCT 1 x, 2 y, 3 x

A consulta acima produz valores STRUCT do tipo STRUCT<int64 x, int64 y, int64 x>.. O primeiro e o terceiro campos têm o mesmo nome x, e o segundo tem o nome y.

O exemplo acima produz o mesmo resultado que esta consulta SELECT AS VALUE usando um construtor de estrutura:

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

SELECT AS VALUE

SELECT AS VALUE produz uma tabela de valores com base em qualquer lista SELECT que produza exatamente uma coluna. Em vez de produzir uma tabela de saída com uma coluna, possivelmente com um nome, a saída será uma tabela de valores em que o tipo de linha é apenas o tipo de valor produzido na única coluna SELECT. Qualquer alias que a coluna tenha será descartado na tabela de valores.

Exemplo:

SELECT AS VALUE 1

A consulta acima produz uma tabela com o tipo de linha INT64.

Exemplo:

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

A consulta acima produz uma tabela com o tipo de linha STRUCT<a int64, b int64>.

Exemplo:

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

Dada uma tabela de valores v como entrada, a consulta acima filtra determinados valores na cláusula WHERE e, em seguida, produz uma tabela de valores usando exatamente o mesmo valor que estava na tabela de entrada. Se a consulta acima não tiver usadoSELECT AS VALUE, o esquema da tabela de saída será diferente do esquema da tabela de entrada, porque a tabela de saída seria normal com uma coluna chamada v contendo o valor de entrada.

Aliases

Consulte Como usar aliases para mais informações sobre sintaxe e visibilidade dos aliases da lista SELECT.

Cláusula FROM

FROM from_clause[, ...]

from_clause:
    from_item
    [ tablesample_operator ]

from_item:
    {
      table_name [ table_hint_expr ] [ as_alias ]
      | { join_operation | ( 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

Com a cláusula FROM, você indica uma ou mais tabelas de onde quer recuperar as linhas. Além disso, ela especifica como mesclar essas linhas para produzir um único streaming de linhas a serem processadas no restante da consulta.

tablesample_operator

Consulte operador TABLESAMPLE.

table_name

O nome de uma tabela atual.

SELECT * FROM Roster;

Dicas de tabela

As dicas a seguir são compatíveis com tabelas:

Chave da dica	Valores possíveis	Descrição
FORCE_INDEX	String. Nome de um índice existente no banco de dados ou _BASE_TABLE para usar a tabela base em vez de um índice.	Se definida com o nome de um índice, use esse índice em vez da tabela base. Se o índice não puder fornecer todas as colunas necessárias, execute uma junção de volta com a tabela base. Se definida com a string _BASE_TABLE, use a tabela base para a estratégia de índice em vez de um índice. Este é o único valor válido quando FORCE_INDEX é usado em uma expressão de dica de instrução. Observação: FORCE_INDEX é, na verdade, uma diretiva, não uma dica. Isso significa que um erro será gerado se o índice não existir.
GROUPBY_SCAN_OPTIMIZATION	TRUE FALSE	O grupo por otimização de verificação pode agilizar as consultas se elas usarem GROUP BY ou SELECT DISTINCT. Isso será possível se as chaves de agrupamento conseguirem formar um prefixo, uma tabela subjacente ou uma chave de índice, e se a consulta exigir apenas a primeira linha de cada grupo. A otimização será aplicada se o otimizador estimar que a consulta será eficiente. A dica substitui essa decisão. Se a dica estiver definida como FALSE, a otimização não será considerada. Se a dica estiver definida como TRUE, a otimização será aplicada caso seja válida.

O exemplo a seguir mostra como usar um índice secundário ao ler de uma tabela, anexando uma diretiva de índice da forma @{FORCE_INDEX=index_name} ao nome da tabela:

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

É possível incluir vários índices em uma consulta, mas apenas um único índice é aceito para cada referência de tabela distinta. Exemplo:

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";

Leia mais sobre diretivas de índice em Índices secundários.

join_operation

Consulte Operação JOIN.

query_expr

( query_expr ) [ [ AS ] alias ] é uma subconsulta da tabela.

field_path

Na cláusula FROM, field_path é qualquer caminho resolvido em um campo com um tipo de dados. A profundidade de field_path em uma estrutura de dados aninhada é arbitrária.

Alguns exemplos de valores field_path válidos incluem:

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;

Os caminhos de campo na cláusula FROM precisam terminar com um campo matriz. Além disso, eles não podem conter matrizes antes do final do caminho. Por exemplo, o caminho array_column.some_array.some_array_field é inválido porque contém uma matriz antes do final do caminho.

Observação: se um caminho tiver apenas um nome, ele será interpretado como uma tabela. Para solucionar esse problema, inclua o caminho usando UNNEST ou use o caminho totalmente qualificado.

Observação: se um caminho tiver mais de um nome e corresponder a um nome de campo, ele será interpretado como um nome de campo. Para forçar o caminho a ser interpretado como um nome de tabela, inclua o caminho usando `.

unnest_operator

Consulte operador UNNEST.

cte_name

As expressões de tabela comuns (CTEs) em uma cláusula WITH funcionam como tabelas temporárias que podem ser referenciadas em qualquer lugar da cláusula FROM. No exemplo abaixo, subQ1 e subQ2 são CTEs.

Exemplo:

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

A cláusula WITH oculta todas as tabelas permanentes com o mesmo nome durante a consulta, a menos que você qualifique o nome da tabela, por exemplo:

db.Roster

Operador UNNEST

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

O operador UNNEST usa um ARRAY e retorna uma tabela com uma linha para cada elemento em ARRAY. Também é possível usar UNNEST fora da cláusula em FROM com o operador IN.

Para ARRAYs de entrada de quase todos os tipos de elemento, a saída de UNNEST geralmente tem uma coluna. Essa coluna única tem um alias opcional, que pode ser usado para fazer referência à coluna em qualquer outro lugar da consulta. Várias colunas podem ser retornadas quando você usa ARRAYS com estes tipos de elementos:

• STRUCT

UNNEST destrói a ordem dos elementos no ARRAY de entrada. Use a cláusula opcional WITH OFFSET para retornar uma segunda coluna com os índices dos elementos da matriz (veja abaixo).

Para várias maneiras de usar o UNNEST, incluindo construção, nivelamento e filtragem, consulte Working with arrays.

UNNEST e STRUCTs

Em um ARRAY de entrada de STRUCTs, UNNEST retorna uma linha para cada STRUCT com uma coluna separada para cada campo em STRUCT. O alias para cada coluna é o nome do campo STRUCT correspondente.

Exemplo:

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

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

Como o operador UNNEST retorna uma tabela de valores, é possível definir um alias para o UNNEST a fim de definir uma variável de intervalo que você possa acessar em outro lugar na consulta. Se você fizer referência à variável de intervalo na lista SELECT, a consulta retorna um STRUCT contendo todos os campos do STRUCT original na tabela de entrada.

Exemplo:

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 explícito e implícito

O desaninhamento de ARRAY pode ser explícito ou implícito. No explícito, array_expression precisa retornar um valor ARRAY, mas não precisa ser resolvido para um ARRAY. Além disso, a palavra-chave UNNEST é obrigatória.

Exemplo:

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

No implícito, array_path precisa ser resolvido para um ARRAY e a palavra-chave UNNEST é opcional.

Exemplo:

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

Nesse cenário, a profundidade de array_path em uma estrutura de dados pode ser arbitrária, mas o último campo precisa ser do tipo ARRAY. Nenhum campo anterior na expressão pode ser do tipo ARRAY porque não é possível extrair um campo nomeado de um ARRAY.

UNNEST e NULLs

Os NULLs são tratados pelo UNNEST da seguinte forma:

• NULL e matrizes vazias não geram nenhuma linha.
• Uma matriz que contém NULLs gera linhas com valores NULL.

Com a cláusula opcional WITH OFFSET, para cada linha produzida pela operação UNNEST, uma coluna separada é retornada com o valor "offset", ou seja, a contagem começa do zero. Essa coluna tem um alias opcional com valor padrão offset.

Exemplo:

SELECT * FROM UNNEST ( ) WITH OFFSET AS num;

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

Descrição

É possível usar o operador TABLESAMPLE para selecionar uma amostra aleatória de um conjunto de dados. Esse operador é útil quando se trabalha com tabelas com grandes quantidades de dados sem necessidade de respostas precisas.

• sample_method: ao usar o operador TABLESAMPLE, é preciso especificar o algoritmo de amostragem a ser usado:
  • BERNOULLI: cada linha é selecionada de forma independente com a probabilidade fornecida na cláusula percent. Como resultado, você recebe aproximadamente N * percent/100 linhas.
  • RESERVOIR: toma como parâmetro um tamanho de amostra real K, expresso como um número de linhas. Se a entrada for menor que K, ela gerará toda a relação de entrada. Se a entrada for maior que K, a amostragem do reservatório gerará uma amostra de tamanho exatamente igual, em que qualquer amostra do mesmo tamanho é igualmente provável.
• sample_size: o tamanho da amostra.
• percent_or_rows: o operador TABLESAMPLE requer que você escolha ROWS ou PERCENT. Se você escolher PERCENT, o valor precisará estar entre 0 e 100. Se você escolher ROWS, o valor precisa ser maior ou igual a 0.

Exemplos

Os exemplos a seguir ilustram o uso do operador TABLESAMPLE.

Selecione a partir de uma tabela com o método de amostragem RESERVOIR:

SELECT MessageId
FROM Messages TABLESAMPLE RESERVOIR (100 ROWS);

Selecione a partir de uma tabela com o método de amostragem BERNOULLI:

SELECT MessageId
FROM Messages TABLESAMPLE BERNOULLI (0.1 PERCENT);

Use TABLESAMPLE com uma subconsulta:

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

Use uma operação TABLESAMPLE com uma junção a outra tabela.

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;

Operação JOIN

join_operation:
    { cross_join_operation | condition_join_operation }

cross_join_operation:
    from_item cross_join_operator [ join_hint_expr ] from_item

condition_join_operation:
    from_item condition_join_operator [ join_hint_expr ] from_item join_condition

cross_join_operator:
    { CROSS JOIN | , }

condition_join_operator:
    {
      [INNER] [ join_method ] JOIN
      | FULL [OUTER] [ join_method ] JOIN
      | LEFT [OUTER] [ join_method ] JOIN
      | RIGHT [OUTER] [ join_method ] JOIN
    }

join_method:
    { HASH }

join_hint_expr:
    '@{' join_hint_key = join_hint_value [, ...] '}'

join_hint_key:
    { FORCE_JOIN_ORDER | JOIN_METHOD }

join_condition:
    { on_clause | using_clause }

on_clause:
    ON bool_expression

using_clause:
    USING ( join_column [, ...] )

A operação JOIN mescla dois from_items para que a cláusula SELECT possa consultá-los como uma única fonte. As cláusulas join_type e ON ou USING (uma "condição de junção") especificam como combinar e descartar linhas dos dois from_items para formar uma única fonte.

Dicas de junção

As seguintes dicas são aceitas para JOIN:

Chave da dica	Valores possíveis	Descrição
FORCE_JOIN_ORDER	TRUE FALSE (padrão)	Se definida como verdadeira, use a ordem de junção especificada na consulta.
JOIN_METHOD	HASH_JOIN APPLY_JOIN MERGE_JOIN PUSH_BROADCAST_HASH_JOIN	Ao implementar uma junção lógica, escolha uma alternativa específica para usar para o método de junção subjacente. Saiba mais em Métodos de junção. Para usar uma junção HASH, use HASH JOIN ou JOIN@{JOIN_METHOD=HASH_JOIN}, mas não os dois.
HASH_JOIN_BUILD_SIDE	BUILD_LEFT BUILD_RIGHT	Especifica qual lado da junção de hash é usado como o lado da versão. Só pode ser usado com JOIN_METHOD=HASH_JOIN
BATCH_MODE	TRUE (default) FALSE	Usado para desativar a junção em lote em favor da junção de linha a tempo. Só pode ser usado com JOIN_METHOD=APPLY_JOIN.

Métodos de junção

Os métodos de junção são implementações específicas dos vários tipos de junção lógica. Alguns métodos de junção estão disponíveis apenas para determinados tipos de junção. A escolha do método de junção a usar depende das especificidades da consulta e dos dados que estão sendo consultados. A melhor maneira de descobrir se um método de junção específico contribui para o desempenho da consulta é tentar o método e ver o plano de execução da consulta resultante. Consulte Operadores de execução de consulta para mais detalhes.

Método de junção	Descrição	Operandos
HASH_JOIN	O operador hash join constrói uma tabela de hash de um lado (o lado build) e sonda a tabela de hash para todos os elementos do outro lado (o lado probe).	Diferentes variantes são usadas para vários tipos de junção. Veja o plano de execução da consulta para ver qual é a variante usada. Leia mais sobre o operador hash join.
APPLY_JOIN	O operador apply join recebe cada item de um lado (o lado input) e avalia a subconsulta do outro lado (o lado map) usando os valores do item do lado input.	Diferentes variantes são usadas para vários tipos de junção. Cross apply é usado para junção interna, e outer apply é usado para junções à esquerda. Saiba mais sobre os operadores Cross apply e Outer apply.
MERGE_JOIN	O operador mesclar mesclagem mescla dois fluxos de dados classificados. O otimizador adicionará operadores Sort ao plano se os dados ainda não fornecerem a propriedade de classificação necessária para a condição de junção fornecida. O mecanismo fornece uma classificação de mesclagem distribuída por padrão, que, quando associada a mesclagem, pode permitir junções maiores, evitando o vazamento de disco e melhorando a escala e a latência.	Diferentes variantes são usadas para vários tipos de junção. Veja o plano de execução da consulta para ver qual é a variante usada. Leia mais sobre o operador de mesclagem mesclada.
PUSH_BROADCAST_HASH_JOIN	O operador de hash de transmissão push cria um lote de dados do lado de compilação da junção. Depois, o lote é enviado em paralelo a todas as divisões locais do lado da sondagem da junção. Em cada servidor local, uma junção de hash é executada entre o lote e os dados locais. Essa junção provavelmente será benéfica quando a entrada pode caber em um lote, mas não é rigorosa. Outra área potencial de benefícios é quando as operações podem ser distribuídas para os servidores locais, como uma agregação que ocorre após uma junção. Uma junção de hash de transmissão push pode distribuir alguma agregação em que uma junção de hash tradicional não pode.	Diferentes variantes são usadas para vários tipos de junção. Veja o plano de execução da consulta para ver qual é a variante usada. Leia mais sobre o operador de hash de transmissão push.

[INNER] JOIN

Com um INNER JOIN, ou apenas JOIN, o produto cartesiano dos dois from_items é calculado com eficácia, e todas as linhas que não cumprem a condição de junção são descartadas. "Com eficácia" significa que é possível implementar um INNER JOIN sem calcular de fato o produto cartesiano.

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

Exemplo

Essa consulta executa um INNER JOIN nas tabelas Roster e TeamMascot.

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 retorna o produto cartesiano dos dois from_items. Em outras palavras, ele combina cada linha do primeiro from_item com cada linha do segundo from_item.

Se as linhas dos dois from_items forem independentes, o resultado terá M * N linhas, considerando M linhas em um from_item e N no outro. Observe que isso ainda se aplica quando from_item não tem linhas.

Em uma cláusula FROM, um CROSS JOIN pode ser escrito assim:

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

Você pode usar uma correlação correlacionado para converter ou nivelar um ARRAY em um conjunto de linhas. Para saber mais, consulte Converter elementos em uma matriz para linhas em uma tabela.

Exemplos

Essa consulta executa um CROSS JOIN nas tabelas Roster e TeamMascot.

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

Correlação implícita (,)

Os CROSS JOINs podem ser escritos implicitamente com uma vírgula. Isso é chamado de correlação implícita.

Uma correção implícita é semelhante a esta em uma cláusula FROM:

FROM A, 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 |
                            +---------------+

Não escreva as correlações implícitas entre parênteses. Para saber mais, consulte Participar de operações em uma sequência.

FROM (A, B)  // INVALID

É possível usar uma correlação implícita correlacionada para converter ou nivelar um ARRAY em um conjunto de linhas. Para saber mais, consulte Converter elementos em uma matriz para linhas em uma tabela.

Exemplos

Essa consulta realiza uma correção implícita nas tabelas Roster e TeamMascot.

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

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

FULL [OUTER] JOIN

Com um FULL OUTER JOIN, ou apenas FULL JOIN, todos os campos de todas as linhas dos dois from_items que atendam à condição de junção são retornados.

FULL indica que todas as linhas de ambos os from_items serão retornadas, ainda que não atendam à condição de junção.

OUTER indica que, se uma linha de um from_item não se associa a nenhuma linha no outro from_item, a linha retornará com NULLs para todas as colunas do outro from_item.

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

Exemplo

Essa consulta executa um FULL JOIN nas tabelas Roster e TeamMascot.

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

O resultado de um LEFT OUTER JOIN (ou simplesmente LEFT JOIN) para dois from_items sempre mantém todas as linhas do lado esquerdo de from_item na operação JOIN, mesmo que nenhuma linha à direita de from_item satisfaça o predicado de junção.

LEFT indica que todas as linhas à esquerda de from_item são retornadas. Se uma determinada linha da esquerda de from_item não se unir a nenhuma linha à direita de from_item, a linha retornará com NULLs para todas as colunas à direita de from_item. Linhas à direita de from_item que não se juntam a nenhuma linha à esquerda de from_item são descartadas.

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

Exemplo

Essa consulta executa um LEFT JOIN nas tabelas Roster e TeamMascot.

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

O resultado de um RIGHT OUTER JOIN (ou simplesmente RIGHT JOIN) é semelhante e simétrico ao de 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    |
                            +--------------------+

Exemplo

Essa consulta executa um RIGHT JOIN nas tabelas Roster e TeamMascot.

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

Cláusula ON

Uma linha combinada (o resultado da junção de duas linhas) atende à condição de junção ON se a condição de junção retornar 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 |     +-------+
+---+     +---+

Exemplo

Esta consulta executa um INNER JOIN na tabela Roster e TeamMascot.

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

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

Cláusula USING

Para a cláusula USING, é necessária uma lista de coluna de uma ou mais colunas presentes nas duas tabelas de entrada. Uma comparação de igualdade é feita nessas colunas. Quando essa comparação retorna TRUE, significa que as linhas atendem à condição de correlação.

FROM A JOIN B USING (x)

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

Exemplo

Esta consulta executa um INNER JOIN na tabela Roster e TeamMascot.

Essa instrução retorna as linhas de Roster e TeamMascot, em que Roster.SchooldID é igual a TeamMascot.SchooldID. Os resultados incluem uma única coluna SchooldID.

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

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

Equivalência ON e USING

As palavras-chave ON e USING não são equivalentes, mas são semelhantes. ON retorna várias colunas, e USING retorna uma.

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

Embora ON e USING não sejam equivalentes, eles podem retornar os mesmos resultados se você especificar as colunas que quer retornar.

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

Unir operações em uma sequência

A cláusula FROM pode conter várias operações JOIN em uma sequência. JOINs estão vinculados da esquerda para a direita. Exemplo:

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

Você também pode inserir parênteses para agrupar JOINs:

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

Com parênteses, você pode agrupar JOINs para que eles sejam vinculados em uma ordem diferente:

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

Quando as correlações implícitas estão presentes em uma consulta com uma sequência de JOINs, elas são agrupadas da esquerda para a direita como outros tipos de 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

Não pode haver RIGHT JOIN ou FULL JOIN após uma correlação implícita, a menos que ele esteja entre parênteses:

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

FROM A, (B RIGHT JOIN C ON TRUE) // VALID

FROM A, (B FULL JOIN C ON TRUE)  // VALID

Operação de correlação correlacionada

Uma operação de correlação é correlacionada quando o from_item à direita contém uma referência a pelo menos uma variável de intervalo ou nome de coluna introduzido pelo from_item à esquerda.

Em uma operação de correlação correlacionada, as linhas do from_item à direita são determinadas por uma linha do from_item à esquerda. Consequentemente, as correlações RIGHT OUTER e FULL OUTER não podem ser correlacionadas porque as linhas do from_item corretas não podem ser determinadas no caso em que não há linha da from_item à esquerda.

Todas as operações de correlação correlacionadas devem fazer referência a uma matriz no from_item à direita.

Este é um exemplo conceitual de uma operação de correlação correlacionada que inclui uma subconsulta correlacionada:

FROM A JOIN UNNEST(ARRAY(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)) AS C

• Esquerdo from_item: A
• Direito from_item: UNNEST(...) AS C
• Uma subconsulta correlacionada: (SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)

Este é outro exemplo conceitual de uma operação de correlação correlacionada. array_of_IDs faz parte do from_item à esquerda, mas é referenciado na from_item direita.

FROM A JOIN UNNEST(A.array_of_IDs) AS C

O operador UNNEST pode ser explícito ou implícito. Ambos são permitidos:

FROM A JOIN UNNEST(A.array_of_IDs) AS IDs

FROM A JOIN A.array_of_IDs AS IDs

Em uma operação de correlação correlacionada, o from_item à direita é reavaliado em cada linha distinta do from_item à esquerda. No exemplo conceitual a seguir, a operação de correlação correlacionada primeiro avalia A e B, depois A e C:

FROM
  A
  JOIN
  UNNEST(ARRAY(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)) AS C
  ON A.Name = C.Name

Exemplos

Este é um exemplo de junção correlacionada, usando as tabelas Roster e PlayerStats:

SELECT *
FROM
  Roster
JOIN
  UNNEST(
    ARRAY(
      SELECT AS STRUCT *
      FROM PlayerStats
      WHERE PlayerStats.OpponentID = Roster.SchoolID
    )) AS PlayerMatches
  ON PlayerMatches.LastName = 'Buchanan'

+------------+----------+----------+------------+--------------+
| LastName   | SchoolID | LastName | OpponentID | PointsScored |
+------------+----------+----------+------------+--------------+
| Adams      | 50       | Buchanan | 50         | 13           |
| Eisenhower | 77       | Buchanan | 77         | 0            |
+------------+----------+----------+------------+--------------+

Cláusula WHERE

WHERE bool_expression

A cláusula WHERE filtra os resultados da cláusula FROM.

Somente linhas com bool_expression avaliado como TRUE são incluídas. Linhas com bool_expression avaliadas como NULL ou FALSE são descartadas.

A avaliação de uma consulta com uma cláusula WHERE normalmente é concluída nesta ordem:

• FROM
• WHERE
• GROUP BY e agregação
• HAVING
• WINDOW
• QUALIFY
• DISTINCT
• ORDER BY
• LIMIT

A cláusula WHERE só pode referenciar colunas disponíveis pela cláusula FROM, mas não pode referenciar aliases da lista SELECT.

Exemplos

Esta consulta retorna todas as linhas da tabela Roster em que a coluna SchoolID tem o valor 52:

SELECT * FROM Roster
WHERE SchoolID = 52;

A bool_expression pode conter várias subcondições.

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

As expressões em um INNER JOIN têm uma expressão equivalente na cláusula WHERE. Por exemplo, uma consulta com INNER JOIN e ON tem uma expressão equivalente com CROSS JOIN e WHERE. No exemplo abaixo, as seguintes consultas são equivalentes:

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;

Cláusula GROUP BY

GROUP BY expression [, ...]

A cláusula GROUP BY agrupa as linhas em uma tabela com valores não distintos para expression na cláusula GROUP BY. Para várias linhas na tabela de origem com valores não distintos para expression, a cláusula GROUP BY produz uma única linha combinada. GROUP BY é comumente usado quando funções de agregação estão presentes na lista SELECT ou para eliminar a redundância na saída. O tipo de dados da expression precisa ser agrupável.

Exemplo:

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

A cláusula GROUP BY pode se referir a nomes de expressões na lista SELECT. A cláusula GROUP BY também permite referências ordinais a expressões na lista SELECT usando valores inteiros. 1 refere-se à primeira expressão na lista SELECT, 2 à segunda, e assim por diante. A lista de expressões pode combinar nomes ordinais e de expressão.

Exemplo:

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

A consulta acima é equivalente a:

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

As cláusulas GROUP BY também podem conter referências a aliases. Se uma consulta contiver aliases na cláusula SELECT, eles substituirão nomes na cláusula FROM correspondente.

Exemplo:

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

Cláusula HAVING

HAVING bool_expression

A cláusula HAVING filtra os resultados produzidos por GROUP BY ou agregação. GROUP BY ou agregação precisa estar presente na consulta. Se houver agregação, a cláusula HAVING será avaliada uma vez para cada linha agregada do conjunto de resultados.

Somente linhas com bool_expression avaliado como TRUE são incluídas. Linhas com bool_expression avaliadas como NULL ou FALSE são descartadas.

A avaliação de uma consulta com uma cláusula HAVING normalmente é concluída nesta ordem:

• FROM
• WHERE
• GROUP BY e agregação
• HAVING
• WINDOW
• QUALIFY
• DISTINCT
• ORDER BY
• LIMIT

A cláusula HAVING pode conter referências a colunas disponibilizadas pela cláusula FROM, assim como aliases da lista de SELECT. As expressões referenciadas na cláusula HAVING precisam aparecer na cláusula GROUP BY ou ser o resultado de uma função de agregação:

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

Se uma consulta tem aliases na cláusula SELECT, esses aliases substituem os nomes em uma cláusula FROM.

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

Agregação obrigatória

Não é necessário que a agregação esteja presente na cláusula HAVING, mas ela precisa aparecer ao menos em uma das formas a seguir:

Função de agregação na lista de SELECT

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

Função de agregação na cláusula HAVING

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

Agregação na lista de SELECT e na cláusula HAVING

Quando as funções de agregação estão presentes na lista de SELECT e na cláusula HAVING, não é necessário que as funções de agregação e as colunas referenciadas sejam iguais. No exemplo abaixo, as duas funções de agregação, COUNT() e SUM(), são diferentes e também usam colunas diferentes.

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

Cláusula ORDER BY

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

Com a cláusula ORDER BY, você especifica uma coluna ou expressão como critério de classificação para o conjunto de resultados. Se uma cláusula ORDER BY não estiver presente, a ordem dos resultados de uma consulta não será definida. Os aliases de coluna da cláusula FROM ou da lista do SELECT são permitidos. Se uma consulta contiver aliases na cláusula SELECT, eles substituirão nomes na cláusula FROM correspondente. O tipo de dados da expression precisa ser ordenável.

Cláusulas opcionais

• COLLATE: refine como os dados são ordenados.
• ASC | DESC: classifica os resultados em ordem crescente ou decrescente de valores de expression. ASC é o valor padrão.

Exemplos

Use a ordem de classificação padrão (crescente).

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

Use a ordem de classificação decrescente.

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

A classificação pode ser feita considerando diversas colunas. No exemplo abaixo, o conjunto de resultados é classificado primeiro por SchoolID e depois por LastName:

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

As seguintes regras são aplicadas na classificação dos valores:

• NULLs: no contexto da cláusula ORDER BY, NULLs são os valores mínimos possíveis, isto é, eles aparecem primeiro em classificações ASC e, por último, em classificações DESC.
• Tipos de dados de ponto flutuante: consulte Semântica de ponto flutuante para ler sobre classificação e agrupamento.

Quando usada com operadores de conjunto, a cláusula ORDER BY se aplica ao conjunto de resultados da consulta inteira, e não apenas à instrução SELECT mais próxima. Por essa razão, use parênteses para mostrar o escopo do ORDER BY, mesmo que isso seja opcional.

Esta consulta sem parênteses:

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

é equivalente a esta consulta com parênteses:

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

mas não é equivalente a esta consulta, em que a cláusula ORDER BY aplica-se apenas à segunda instrução SELECT:

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

Também é possível usar literais de número inteiro como referências de coluna em cláusulas ORDER BY. Na lista do SELECT, um literal inteiro torna-se um ordinal, por exemplo, contando a partir do 1.

No exemplo abaixo, as seguintes consultas são equivalentes:

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

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

Cláusula COLLATE

COLLATE collate_string

collate_string:
  language_tag[:collation_attribute]

É possível usar o COLLATE para refinar a forma como os dados são ordenados em uma cláusula ORDER BY. A compilação refere-se a um conjunto de regras que determinam como as strings são comparadas de acordo com as convenções e os padrões de um determinado idioma, região ou país. Essas regras podem definir a sequência correta de caracteres, com opções para especificar a não diferenciação de maiúsculas e minúsculas.

Observação :só é possível usar COLLATE em colunas do tipo STRING.

Um collate_string contém um language_tag e pode ter um collation_attribute opcional como um sufixo, separado por dois pontos.

O language_tag é um literal ou um parâmetro de consulta:

• Uma string de localidade padrão. Esse nome geralmente é composto por duas ou três letras que representam o idioma. Também é possível usar um sublinhado ou um hífen e duas letras representando a região. Por exemplo, en_US. Esses nomes são definidos pelo Common Locale Data Repository (CLDR) (em inglês). Consulte Compilação Unicode abaixo.

• und, uma string de localidade que representa a localidade indeterminada. Consulte Compilação Unicode abaixo.

• unicode. Veja os detalhes abaixo.

Além de language_tag, uma collate_string pode ter um collation_attribute opcional como sufixo, separado por dois-pontos. Os valores permitidos são: + ci para maiúsculas e minúsculas + cs para maiúsculas e minúsculas (observação: "cs" é o padrão, então especificar que nunca tem um efeito).

Compilação Unicode

Para language_tags diferentes de unicode, o Google Standard SQL segue o Algoritmo de compilação de Unicode. O padrão define o formato das tags de idioma, o que inclui algumas extensões úteis e o algoritmo usado para comparação.

und é uma tag de idioma especial definida no registro de subtag de idioma da IANA e usada para indicar uma localidade indeterminada. Isso também é conhecido como a localidade root e pode ser considerado o agrupamento Unicode padrão. Ele define um agrupamento agnóstico razoável de localidade. Ele é muito diferente de unicode.

Um language_tag pode ser estendido anexando -u-<extension>. Por exemplo, a extensão para especificar ordem numérica é kn-true. Portanto, en-us-u-kn-true indicaria a localidade em inglês americano, com classificação numérica (abc1 é considerado menor que abc12). Alguns exemplos úteis de extensões:

Extensão	Nome	Exemplo
-ks-nível2	Não diferencia maiúsculas de minúsculas	"a1" < "A2"
-ks-nível1	Acentos e indiferentes a maiúsculas	"ä1" < "a2" < "A3"
-ks-level1-kc-true	Insensível	"ä1" < "a2"
-kn-verdadeiro	Ordenação numérica	"a1b" < "a12b"

Para ver uma lista completa e detalhes técnicos detalhados, consulte Linguagem de marcação de dados Unicode parte 5: compilação.

Advertências:

• Strings diferentes podem ser consideradas iguais. Por exemplo, ẞ (LATIN CAPITAL LETTER SHARP S) é considerado igual a "SS" no nível principal, portanto, "ẞ1" < "SS2". Isso é semelhante à insensibilidade do caso.

• Pontos de código ignoráveis: a compilação Unicode especifica uma grande variedade de pontos de código que são tratados principalmente como se não estivessem presentes. Assim, as strings com e sem elas são classificadas de maneira idêntica, por exemplo, U2060 - 'WORD JOINER'.

    SELECT "oran\u2060ge1" UNION ALL SELECT "\u2060orange2" UNION ALL SELECT "orange3"
    ORDER BY 1 COLLATE "und"
    +---------+
    |         |
    +---------+
    | orange1 |
    | orange2 |
    | orange3 |
    +---------+
  

• A ordem pode mudar: o Unicode às vezes faz alterações na compilação padrão ("und") que, em raras circunstâncias, pode mudar a ordem relativa das strings. As ordens de classificação para idiomas diferentes de "und" mudam com mais frequência conforme os padrões mudam ou as novas informações são coletadas. Se uma ordem de classificação fixa for necessária, use unicode.

Além disso, um language_tag de unicode é compatível:

• unicode: retorna dados na ordem de ponto de código Unicode, que é idêntico ao comportamento de ordem quando COLLATE não é usado. A ordem de classificação será muito arbitrária para os usuários humanos.
• unicode:cs: idêntico a unicode
• unicode:ci: idêntico a und:ci

Exemplos

Agrupar resultados usando o inglês do Canadá:

SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_CA"

Agrupe resultados usando um parâmetro:

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

Usando várias cláusulas COLLATE em uma instrução:

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

Agrupamento indiferente a maiúsculas:

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

Agrupamento indiferente a maiúsculas em Unicode padrão:

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

Operadores de conjunto

set_operation:
  query_expr set_operator query_expr

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

Operadores de conjunto combinam os resultados de duas ou mais consultas de entrada em um único conjunto de resultados. Especifique ALL ou DISTINCT. Se você especificar ALL, todas as linhas serão retidas. Se DISTINCT é especificado, as linhas duplicadas são descartadas.

Se uma linha específica R aparece exatamente m vezes na primeira consulta de entrada e n vezes na segunda (m >= 0, n >= 0):

• Para UNION ALL, R aparece exatamente m + n vezes no resultado.
• Para INTERSECT ALL, R aparecerá exatamente MIN(m, n) no resultado.
• Para EXCEPT ALL, R aparece exatamente MAX(m - n, 0) no resultado.
• Para UNION DISTINCT, o DISTINCT é computado depois que UNION é computado, de modo que R apareça exatamente uma vez.
• Para INTERSECT DISTINCT, o DISTINCT é computado depois que o resultado acima é computado.
• Para EXCEPT DISTINCT, a linha R aparece uma vez na saída se m > 0 e n = 0.
• Se houver mais de duas consultas de entrada, as operações descritas acima se aplicam, e a saída é a mesma, como se as entradas fossem combinadas de maneira incremental da esquerda para a direita.

Aplicam-se as seguintes regras:

• Para operações de conjunto diferente de UNION ALL, todos os tipos de coluna precisam ser compatíveis com a comparação de igualdade.
• O mesmo número de colunas precisa ser retornado nas consultas de entrada de cada lado do operador.
• Com esses operadores, as colunas retornadas pelas consultas de entrada são pareadas de acordo com suas posições nas respectivas listas de SELECT. Ou seja, a primeira coluna da primeira consulta de entrada corresponde à primeira coluna da segunda consulta.
• No conjunto de resultados, sempre são usados os nomes de coluna da primeira consulta de entrada.
• O conjunto de resultados sempre usa os supertipos de entrada nas colunas correspondentes. Isso significa que essas colunas também precisam ser do mesmo tipo de dados ou de um supertipo em comum.
• É preciso usar parênteses para separar operações de conjunto diferentes. Para essa finalidade, operações de conjunto, como UNION ALL e UNION DISTINCT, são diferentes. Se a instrução repetir somente a mesma operação de conjunto, os parênteses não serão necessários.

Exemplos:

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

Inválido:

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

UNION

Com o operador UNION são combinados os conjuntos de resultados de duas ou mais consultas de entrada com o pareamento das colunas dos conjuntos de resultados de cada consulta e da respectiva concatenação vertical.

INTERSECT

Com o operador INTERSECT, são retornadas as linhas encontradas nos conjuntos de resultados das consultas de entrada à esquerda e à direita. Ao contrário de EXCEPT, o posicionamento das consultas de entrada (à esquerda versus direita do operador INTERSECT) não importa.

EXCEPT

Com o operador EXCEPT, são retornadas as linhas da consulta de entrada à esquerda que não estão na consulta de entrada à direita.

Exemplo:

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

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

Cláusulas LIMIT e OFFSET

LIMIT count [ OFFSET skip_rows ]

No LIMIT, um count não negativo do tipo INT64 é especificado, e apenas count linhas são retornadas. LIMIT 0 retorna 0 linha.

Se houver uma operação definida, o LIMIT é aplicado depois que essa operação for avaliada.

OFFSET especifica um número não negativo de linhas a serem ignoradas antes de aplicar LIMIT. skip_rows é do tipo INT64.

Nessas cláusulas, apenas valores literais ou de parâmetro são aceitos. As linhas retornadas por LIMIT e OFFSET não são especificadas, a menos que esses operadores sejam usados depois de ORDER BY.

Exemplos:

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

Cláusula WITH

WITH cte[, ...]

Uma cláusula WITH contém uma ou mais expressões de tabela comuns. Uma CTE atua como uma tabela temporária que pode ser referenciada em uma única expressão de consulta. Cada CTE vincula os resultados de uma subconsulta a um nome de tabela que pode ser usado em outro lugar na mesma expressão de consulta, mas as regras se aplicam.

CTEs

cte:
    cte_name AS ( query_expr )

Uma expressão de tabela comum (CTE) contém uma subconsulta e um nome associado a CTE.

• Uma CTE não pode se referir a si mesmo.
• Uma CTE pode ser referenciada pela expressão de consulta que contém a cláusula WITH, mas as regras se aplicam.

Exemplos

Neste exemplo, uma cláusula WITH define dois CTEs referenciados na operação de conjunto relacionada, em que um CTE é referenciado por cada uma das expressões de consulta de entrada da operação definida:

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

WITH não é compatível em uma subconsulta. Isso retorna um erro:

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

A cláusula WITH não é compatível com instruções DML.

Tabelas temporárias definidas pela cláusula WITH são armazenadas na memória. O SQL padrão do Google aloca dinamicamente memória para todas as tabelas temporárias criadas por uma consulta. Se os recursos disponíveis não forem suficientes, a consulta falhará.

Regras e restrições de CTEs

Expressões comuns de tabelas (CTEs, na sigla em inglês) podem ser referenciadas na expressão de consulta que contém a cláusula WITH.

Veja algumas regras e restrições gerais a serem consideradas ao trabalhar com CTEs:

• todas as CTEs na mesma cláusula WITH precisam ter um nome exclusivo;
• uma CTE definida em uma cláusula WITH só fica visível para outras CTEs na mesma cláusula WITH que foram definidas depois dela.
• uma CTE local substitui uma CTE externa ou tabela com o mesmo nome;
• uma CTE em uma subconsulta talvez não referencie colunas correlacionadas da consulta externa.

Visibilidade de CTEs

As referências entre expressões de tabela comuns (CTEs) na cláusula WITH podem voltar, mas não avançar.

Isso é o que acontece quando há duas CTEs que se referem a elas mesmas ou entre si em uma cláusula WITH. Suponha que A é a primeira CTE e B é a segunda CTE na cláusula:

• A referencia A = inválida;
• A referencia B = inválida;
• B referencia A = válida;
• A referencia B e referencia A = inválidas (ciclos não são permitidos).

Isso gera um erro. A não pode referenciar a si mesmo porque esse tipo de referência não é compatível:

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

-- Error

Isso gera um erro. A não pode referenciar B porque as referências entre CTEs podem voltar, mas não avançar:

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

-- Error

B pode referenciar A porque as referências entre CTEs podem voltar:

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

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

Isso gera um erro. A e B referenciam uma a outra, o que cria um ciclo:

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

-- Error

Como usar aliases

Um alias é um nome temporário atribuído a uma tabela, coluna ou expressão presente em uma consulta. Quando aliases explícitos não são introduzidos na lista do SELECT ou na cláusula FROM, um alias implícito é inferido no Google Standard para algumas expressões. Expressões sem um alias implícito ou explícito são anônimas. Nesse caso, não faça referências a elas pelo nome na consulta.

Aliases explícitos

Introduza aliases explícitos na cláusula FROM ou na lista de SELECT.

Em uma cláusula FROM, é possível introduzir aliases explícitos para qualquer item, incluindo tabelas, matrizes, subconsultas e cláusulas UNNEST, usando [AS] alias. A palavra-chave AS é opcional.

Exemplo:

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

Você pode introduzir aliases explícitos para qualquer expressão na lista SELECT usando [AS] alias. A palavra-chave AS é opcional.

Exemplo:

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

Aliases implícitos

Na lista de SELECT, quando uma expressão não contém um alias explícito, o Google Standard SQL atribui um alias implícito de acordo com as regras a seguir. Pode haver várias colunas com o mesmo alias na lista de SELECT.

• Para identificadores, o alias é o identificador. Por exemplo, SELECT abc implica AS abc.
• Para expressões de caminho, o alias é o último identificador no caminho. Por exemplo, SELECT abc.def.ghi implica AS ghi.
• Quando o operador "ponto" de acesso ao campo do membro é usado, o alias é o nome desse campo. Por exemplo, SELECT (struct_function()).fname implica AS fname.

Em todos os outros casos, não há alias implícito, ou seja, a coluna é anônima e não pode ser referenciada por nome. Os dados dessa coluna são retornados, e os resultados exibidos talvez tenham um rótulo gerado para ela, mas esse rótulo não pode ser usado da mesma forma que um alias.

Em uma cláusula FROM, from_items não precisam ter um alias. Aplicam-se as seguintes regras:

• Quando uma expressão não tem um alias explícito, o SQL padrão do Google atribui um alias implícito nestes casos:
• Para identificadores, o alias é o identificador. Por exemplo, FROM abc implica AS abc.
• Para expressões de caminho, o alias é o último identificador no caminho. Por exemplo, FROM abc.def.ghi implica AS ghi
• Na coluna produzida com uso de WITH OFFSET, o alias offset é implícito.
• As subconsultas de tabela não têm aliases implícitos.
• FROM UNNEST(x) não tem um alias implícito.

Visibilidade do alias

Depois de introduzir um alias explícito em uma consulta, há restrições sobre onde é possível fazer referência a esse alias dentro da consulta. Essas restrições à visibilidade do alias são resultado das regras de escopo de nome do SQL padrão do Google.

Visibilidade na cláusula FROM

No SQL padrão do Google, os aliases em uma cláusula FROM são processados da esquerda para a direita, e são visíveis apenas para as expressões de caminho posteriores em uma cláusula FROM.

Exemplo:

Considere que a tabela Singers tenha uma coluna Concerts do tipo ARRAY.

SELECT FirstName
FROM Singers AS s, s.Concerts;

Inválido:

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

Aliases da cláusula FROM não são visíveis para subconsultas na mesma cláusula FROM. As subconsultas de uma cláusula FROM não podem conter referências correlacionadas a outras tabelas na mesma cláusula FROM.

Inválido:

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

Use um nome de coluna de uma tabela no FROM como alias em qualquer lugar da consulta, com ou sem a qualificação com o nome dessa tabela.

Exemplo:

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

Se a cláusula FROM contiver um alias explícito, será preciso usar o alias explícito em vez do alias implícito para o restante da consulta (consulte Aliases implícitos). Um alias de tabela é útil para simplificar ou eliminar ambiguidades em casos como mesclagem automática, em que a mesma tabela é verificada várias vezes durante o processamento da consulta.

Exemplo:

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

Inválido — ORDER BY não usa o alias de tabela:

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

Visibilidade na lista de SELECT

Os aliases na lista SELECT são visíveis apenas para as seguintes cláusulas:

• Cláusula GROUP BY
• Cláusula ORDER BY
• Cláusula HAVING

Exemplo:

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

Visibilidade nas cláusulas GROUP BY, ORDER BY e HAVING

Essas três cláusulas, GROUP BY, ORDER BY e HAVING, podem se referir apenas aos seguintes valores:

• Tabelas na cláusula FROM e qualquer uma das colunas delas.
• Aliases da lista de SELECT.

GROUP BY e ORDER BY também podem se referir a um terceiro grupo:

• literais inteiros, que se referem a itens na lista do SELECT. O inteiro 1 refere-se ao primeiro item da lista de SELECT, 2 refere-se ao segundo, e assim por diante.

Exemplo:

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

A consulta anterior equivale a:

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

Aliases duplicados

É permitida uma subconsulta ou lista de SELECT que contenha vários aliases explícitos ou implícitos do mesmo nome, desde que o nome do alias não seja referenciado em outro local na consulta, já que a referência é ambígua.

Exemplo:

SELECT 1 AS a, 2 AS a;

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

Aliases ambíguos

O Google Standard SQL apresentará um erro se o acesso a um nome for ambíguo, o que significa que ele pode resolver mais de um objeto exclusivo na consulta ou em um esquema de tabela, incluindo o esquema de uma tabela de destino.

Exemplos:

Esta consulta contém nomes de colunas que entram em conflito entre tabelas, já que Singers e Songs têm uma coluna chamada SingerID:

SELECT SingerID
FROM Singers, Songs;

Esta consulta contém aliases que são ambíguos na cláusula GROUP BY porque estão duplicados na lista SELECT:

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

Essa consulta contém aliases que são ambíguos na lista SELECT e na cláusula FROM, porque compartilham o mesmo nome. Suponha que table tenha colunas x, y e z. z é do tipo STRUCT e tem campos v, w e x.

Exemplo:

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

O alias T é ambíguo e produzirá um erro porque T.x na cláusula GROUP BY pode se referir a table.x ou table.z.x.

Um nome não é ambíguo em GROUP BY, ORDER BY ou HAVING se for um nome de coluna e um alias de lista SELECT, desde que o nome corresponda ao mesmo objeto subjacente.

Exemplo:

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

O alias BirthYear não é ambíguo porque corresponde à mesma coluna subjacente, Singers.BirthYear.

Variáveis de intervalo

No SQL padrão do Google, uma variável de intervalo é um alias de expressão de tabela na cláusula FROM. Às vezes, uma variável de intervalo é conhecida como table alias. Uma variável de intervalo permite referenciar linhas que são verificadas a partir de uma expressão de tabela. Uma expressão de tabela representa um item na cláusula FROM que retorna uma tabela. Os itens comuns que essa expressão pode representar incluem tabelas, tabelas de valores, subconsultas, mesclagens e mesclagens entre parêntesis.

Em geral, uma variável de intervalo fornece uma referência às linhas de uma expressão de tabela. Uma variável de intervalo pode ser usada para qualificar uma referência de coluna e identificar sem ambiguidade a tabela relacionada, por exemplo, range_variable.column_1.

Ao referenciar uma variável de intervalo sozinha sem um sufixo de coluna especificado, o resultado de uma expressão de tabela é o tipo de linha da tabela relacionada. As tabelas de valor têm tipos de linha explícitos. Portanto, para variáveis de intervalo relacionadas a tabelas de valor, o tipo de resultado é o tipo de linha da tabela de valores. Outras tabelas não têm tipos de linha explícitos e, para essas tabelas, o tipo de variável de intervalo é um STRUCT definido dinamicamente que inclui todas as colunas na tabela.

Exemplos

Nestes exemplos, a cláusula WITH é usada para emular uma tabela temporária chamada Grid. Esta tabela tem colunas x e y. Uma variável de intervalo chamada Coordinate refere-se à linha atual durante a verificação da tabela. Coordinate pode ser usado para acessar toda a linha ou as colunas na linha.

No exemplo a seguir, selecionamos a coluna x da variável de intervalo Coordinate, que, na verdade, seleciona a coluna x da tabela Grid.

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

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

O exemplo a seguir seleciona todas as colunas da variável de intervalo Coordinate, que, na verdade, seleciona todas as colunas da tabela Grid.

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

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

No exemplo a seguir, selecionamos a variável de intervalo Coordinate, que é uma referência às linhas na tabela Grid. Como Grid não é uma tabela de valores, o tipo de resultado de Coordinate é um STRUCT que contém todas as colunas de Grid.

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

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

Apêndice A: exemplos com dados de amostra

Nesses exemplos, incluímos instruções que executam consultas em tabelas Roster, TeamMascot e PlayerStats.

Cláusula GROUP BY

Exemplo:

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

LastName	SOMA
Adams	7
Buchanan	13
Coolidge	1

UNION

Com o operador UNION é feita a combinação dos conjuntos de resultados de duas ou mais instruções SELECT pareando as colunas dos resultados de cada instrução SELECT e concatenando-as verticalmente.

Exemplo:

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

Resultados:

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

INTERSECT

Com esta consulta são retornados os sobrenomes constantes nas tabelas Roster e PlayerStats.

SELECT LastName
FROM Roster
INTERSECT ALL
SELECT LastName
FROM PlayerStats;

Resultados:

LastName
Adams
Coolidge
Buchanan

EXCEPT

Com a consulta abaixo são retornados os sobrenomes constantes na Roster mas que não estão na PlayerStats.

SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;

Resultados:

LastName
Eisenhower
Davis

A reversão da ordem das instruções SELECT retornará os sobrenomes em PlayerStats que não estão presentes na Roster:

SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;

Resultados:

(empty)