Sintaxe de consulta do SQL padrão

Com as instruções de consulta, você verifica uma ou mais tabelas ou expressões e retorna as linhas de resultado. Este tópico descreve a sintaxe de consultas SQL no BigQuery.

Sintaxe SQL

query_statement:
    query_expr

query_expr:
    [ WITH with_query_name AS ( query_expr ) [, ...] ]
    { select | ( query_expr ) | query_expr set_op query_expr }
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ LIMIT count [ OFFSET skip_rows ] ]

select:
    SELECT [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }]
        { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...] [ FROM from_item [, ...] ] [ WHERE bool_expression ] [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ] [ HAVING bool_expression ] [ WINDOW window_name AS ( window_definition ) [, ...] ] set_op: UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT from_item: { table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ] | join | ( query_expr ) [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ [ AS ] alias ] } join: from_item [ join_type ] JOIN from_item [ { ON bool_expression | USING ( join_column [, ...] ) } ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

Notação:

  • Colchetes "[ ]" indicam cláusulas opcionais.
  • Parênteses "( )" indicam parênteses literais.
  • A barra vertical "|" indica um OU lógico.
  • 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.

Lista de SELECT

Sintaxe:

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 de SELECT

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 * 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 que não têm 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 que não têm nomes.

SELECT ALL

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

Tabela de valores

No BigQuery, 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.

Observação: no BigQuery, uma consulta só pode retornar uma tabela de valores com um tipo de STRUCT.

Em contextos em que uma consulta com exatamente uma coluna é esperada, é possível usar uma consulta de tabela de valores. Por exemplo, subconsultas escalares e de matriz (consulte Subconsultas) normalmente exigem uma consulta de coluna única. No entanto, no BigQuery também é possível usar 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

Sintaxe:

SELECT AS STRUCT expr1 [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. Colunas anônimas e duplicadas são permitidas.

Exemplo:

SELECT AS STRUCT 1 x, 2, 3 x

A consulta acima produz valores STRUCT do tipo STRUCT<int64 x, int64, int64 x>.. O primeiro e o terceiro campo têm o mesmo nome x e o segundo campo é anônimo.

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

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

Exemplo:

SELECT
  ARRAY(SELECT AS STRUCT t.f1, t.f2 WHERE t.f3=true)
FROM
  Table t

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.

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 STRUCT(1 a, 2 b) xyz FROM Table;

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

Aliases

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

Funções analíticas

As funções analíticas e as cláusulas relacionadas a elas, incluindo OVER, PARTITION BY e WINDOW, estão documentadas nos Conceitos da função analítica.

Cláusula FROM

Com a cláusula FROM, você indica a tabela ou as tabelas de onde quer recuperar as linhas. Além disso, especifica como essas linhas serão associadas para produzir um único stream de linhas para processamento no restante da consulta.

Sintaxe

from_item: {
    table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ]  |
    join |
    ( query_expr ) [ [ AS ] alias ] |
    field_path |
    { UNNEST( array_expression ) | UNNEST( array_path ) | array_path }
        [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] |
    with_query_name [ [ AS ] alias ]
}

table_name

Nome (opcionalmente qualificado) de uma tabela existente.

SELECT * FROM Roster;
SELECT * FROM dataset.Roster;
SELECT * FROM project.dataset.Roster;

FOR SYSTEM_TIME AS OF

FOR SYSTEM_TIME AS OF faz referência às versões históricas da definição da tabela e às linhas que eram atuais em timestamp_expression.

Limitações:

A tabela de origem na cláusula FROM que contém FOR SYSTEM_TIME AS OF não pode ser nenhuma das seguintes:

  • Uma verificação ARRAY, incluindo uma matriz plana ou a saída do operador UNNEST.
  • Uma expressão de tabela comum definida por uma cláusula WITH.

timestamp_expression precisa ser uma expressão constante. e não pode conter os seguintes elementos:

  • Subconsultas.
  • referências correlatas - referências a colunas de uma tabela que aparecem em um nível mais alto da instrução de consulta, como na lista SELECT
  • Funções definidas pelo usuário (UDFs, na sigla em inglês).

O valor de timestamp_expression não pode se enquadrar nos seguintes intervalos:

  • Após o carimbo de data/hora atual (no futuro).
  • mais de sete (7) dias antes do carimbo de data/hora atual

Uma única declaração de consulta não pode fazer referência a uma única tabela em mais de um horário definido, incluindo a hora atual. Ou seja, com uma consulta é possível referenciar uma tabela várias vezes no mesmo carimbo de data/hora, mas não a versão atual e uma versão histórica ou duas versões históricas diferentes.

Exemplos:

A consulta a seguir retorna uma versão histórica de uma hora atrás da tabela.

SELECT *
FROM t
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

A consulta a seguir retorna uma versão histórica da tabela em um horário absoluto.

SELECT *
FROM t
  FOR SYSTEM_TIME AS OF '2017-01-01 10:00:00-07:00';

A consulta a seguir retorna um erro porque timestamp_expression contém uma referência correlacionada a uma coluna da própria consulta.

SELECT *
FROM t1
WHERE t1.a IN (SELECT t2.a
               FROM t2 FOR SYSTEM_TIME AS OF t1.timestamp_column);

join

Consulte os Tipos de JOIN abaixo.

select

( select ) [ [ 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 tem apenas um nome, ele será interpretado como uma tabela. Para solucionar esse problema, utilize o caminho com 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

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 esses 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).

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

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

Exemplo:

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

No implícito, array_path precisa resolver em 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 do array_path em uma estrutura de dados é arbitrária, mas o último campo precisa ser do tipo ARRAY. Nenhum campo anterior na expressão pode ser desse tipo porque não é possível extrair um campo nomeado de uma ARRAY.

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

  • NULL e ARRAYs vazios não geram nenhuma linha.
  • Uma ARRAY 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 cujo valor padrão é offset.

Exemplo:

SELECT * FROM UNNEST ( ) WITH OFFSET AS num;

Veja o Arrays topic para mais formas de usar UNNEST, incluindo construção, nivelamento e filtragem.

with_query_name

Os nomes de consulta em uma cláusula WITH (consulte Cláusula WITH) agem como nomes de tabelas temporárias que são acessíveis em qualquer lugar na cláusula FROM. No exemplo abaixo, subQ1 e subQ2 são with_query_names.

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 quaisquer tabelas permanentes com o mesmo nome durante a consulta, a menos que você qualifique o nome da tabela, como dataset.Roster ou project.dataset.Roster.

Subconsultas

Uma subconsulta é uma consulta que aparece dentro de outra instrução, escrita entre parênteses. Ela também é chamada de "sub-SELECTs" ou "SELECT aninhado". A sintaxe completa do SELECT é válida nas subconsultas.

Há dois tipos de subconsulta:

  • Subconsultas de expressão, que podem ser usadas em uma consulta sempre que as expressões forem válidas. As subconsultas de expressão retornam um único valor.
  • Subconsultas de tabela, que podem ser usadas apenas em uma cláusula FROM. A consulta externa trata o resultado da subconsulta como uma tabela.

Note que ambos os tipos de subconsultas precisam estar entre parênteses.

Exemplo:

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

Opcionalmente, uma subconsulta de tabela pode ter um alias.

Exemplo:

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

Aliases

Consulte Aliases para mais informações sobre sintaxe e visibilidade dos aliases da cláusula FROM.

Tipos de JOIN

Sintaxe

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

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

A cláusula 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.

Todas as cláusulas JOIN exigem um join_type.

A condição de junção é obrigatória em uma cláusula JOIN, a menos que uma das seguintes condições seja verdadeira:

  • join_type é CROSS.
  • Um ou ambos os from_items não são tabela, por exemplo, um array_path ou field_path.

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

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 houver M linhas da primeira e N linhas da segunda, o resultado será M * N linhas. Observe que, se from_item tiver zero linhas, o resultado será zero linhas.

Correlações implícitas

Os CROSS JOINs podem ser escritos de maneira explícita, como descrito anteriormente, ou implícita, usando uma vírgula para separar os from_items.

Exemplo de "correlação implícita":

SELECT * FROM Roster, TeamMascot;

Veja um equivalente de correlação explícita:

SELECT * FROM Roster CROSS JOIN TeamMascot;

Não escreva as correlações implícitas entre parênteses.

Inválido - correlação implícita entre parênteses:

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

Consulte Sequências de JOINs para ver detalhes sobre o comportamento de uma correlação implícita em uma sequência de JOINs.

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.

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 cláusula 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.

RIGHT [OUTER] JOIN

O resultado de um RIGHT OUTER JOIN (ou simplesmente RIGHT JOIN) é semelhante e simétrico ao de LEFT OUTER JOIN.

Cláusula ON

A cláusula ON contém um bool_expression. Uma linha combinada (o resultado da junção de duas linhas) atende à condição de junção se bool_expression retornar TRUE.

Exemplo:

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

Cláusula USING

A cláusula USING requer um column_list com uma ou mais colunas, que ocorrem em ambas as 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 junção.

Na maioria dos casos, uma instrução com a palavra-chave USING é equivalente a usar a palavra-chave ON. Por exemplo, a instrução:

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

é equivalente a:

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

Se você usa USING, os resultados das consultas com ON são diferentes das consultas com SELECT *. Como exemplo, veja a seguinte consulta:

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

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

Por outro lado, veja a seguinte consulta:

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

Essa instrução retorna as linhas de Roster e PlayerStats, em que Roster.LastName é igual a PlayerStats.LastName. Os resultados incluem duas colunas LastName: uma de Roster e uma de PlayerStats.

Sequências de JOINs

A cláusula FROM pode conter diversas cláusulas JOIN em sequência.

Exemplo:

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

em que a, b e c são quaisquer from_items. Os JOINs são vinculados da esquerda para a direita, mas é possível inserir parênteses para agrupá-los em uma ordem diferente.

Considere as seguintes consultas: A (sem parênteses) e B (com parênteses) são equivalentes entre si, mas não com C. O FULL JOIN em negrito é vinculado primeiro.

A.

SELECT * FROM Roster FULL JOIN TeamMascot USING (SchoolID)
FULL JOIN PlayerStats USING (LastName);

B.

SELECT * FROM ( (Roster FULL JOIN TeamMascot USING (SchoolID))
FULL JOIN PlayerStats USING (LastName));

C.

SELECT * FROM (Roster FULL JOIN (TeamMascot FULL JOIN PlayerStats USING
(LastName)) USING (SchoolID)) ;

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.

Exemplo:

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

A consulta acima é equivalente a:

SELECT * FROM ((a JOIN b ON TRUE) CROSS JOIN b) JOIN c ON TRUE);

Não pode haver RIGHT JOIN ou FULL JOIN após um join implícito.

Inválido - RIGHT JOIN após uma correlação implícita:

SELECT * FROM Roster, TeamMascot RIGHT JOIN PlayerStats ON TRUE;  // INVALID.

Cláusula WHERE

Sintaxe

WHERE bool_expression

Com a cláusula WHERE, as linhas são filtradas pela avaliação de cada linha com relação a uma bool_expression, e todas as linhas que não retornam TRUE, ou seja, que retornam FALSE ou NULL, são descartadas.

Exemplo:

SELECT * FROM Roster
WHERE SchoolID = 52;

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

Exemplo:

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

Não use aliases de coluna da lista de SELECT como referência na cláusula WHERE.

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.

Por exemplo, a consulta a seguir:

SELECT * FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

é equivalente a:

SELECT * FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;

Cláusula GROUP BY

Sintaxe

GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) }

A cláusula GROUP BY agrupa as linhas em uma tabela com valores não distintos para expression na cláusula . 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;

GROUP BY ROLLUP retorna os resultados de GROUP BY para prefixos das expressões na lista ROLLUP, cada um dos quais é conhecido como um conjunto de agrupamentos. Para a lista ROLLUP (a, b, c), os conjuntos de agrupamento são (a, b, c), (a, b), (a), (). Ao avaliar os resultados de GROUP BY para um conjunto de agrupamento específico, GROUP BY ROLLUP trata expressões que não estão no conjunto de agrupamento como tendo um valor NULL. Em uma instrução SELECT como esta:

SELECT a,    b,    SUM(c) FROM Input GROUP BY ROLLUP(a, b);

usa a lista cumulativa (a, b). O resultado incluirá os resultados de GROUP BY para os conjuntos de agrupamentos (a, b), (a) e (), que inclui todas as linhas. Com isso, são retornadas as mesmas linhas como:

SELECT NULL, NULL, SUM(c) FROM Input               UNION ALL
SELECT a,    NULL, SUM(c) FROM Input GROUP BY a    UNION ALL
SELECT a,    b,    SUM(c) FROM Input GROUP BY a, b;

Isso permite a computação de agregados para os conjuntos de agrupamento definidos pelas expressões na lista ROLLUP e os prefixos dessa lista.

Exemplo:

WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, 2, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  day,
  SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(day);

Na consulta acima é exibida uma linha para cada dia, além do total acumulado em todos os dias, conforme indicado por um dia NULL:

+------+-------+
| day  | total |
+------+-------+
| NULL | 39.77 |
|    1 | 23.54 |
|    2 |  9.99 |
|    3 |  6.24 |
+------+-------+

Exemplo:

WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, 2, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  sku,
  day,
  SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(sku, day)
ORDER BY sku, day;

Na consulta acima são retornadas as linhas agrupadas pelos seguintes conjuntos de agrupamento:

  • sku e dia
  • sku (o dia é NULL)
  • o conjunto de agrupamento vazio (dia e sku são NULL)

A soma desses conjuntos de agrupamento corresponde ao total de cada combinação distinta de sku-dia, o total de cada sku em todos os dias e o total geral:

+------+------+-------+
| sku  | day  | total |
+------+------+-------+
| NULL | NULL | 39.77 |
|  123 | NULL | 28.97 |
|  123 |    1 | 18.98 |
|  123 |    2 |  9.99 |
|  456 | NULL |  8.81 |
|  456 |    1 |  4.56 |
|  456 |    3 |  4.25 |
|  789 |    3 |  1.99 |
|  789 | NULL |  1.99 |
+------+------+-------+

Cláusula HAVING

Sintaxe

HAVING bool_expression

A cláusula HAVING é semelhante à cláusula WHERE: filtra as linhas que não retornam TRUE quando são avaliadas em relação a bool_expression.

Assim como na cláusula WHERE, bool_expression pode ser qualquer expressão que retorne um booleano e pode conter múltiplas subcondições.

A cláusula HAVING é diferente da cláusula WHERE:

  • A cláusula HAVING requer que GROUP BY ou agregação esteja presente na consulta.
  • A HAVING cláusula ocorre após GROUP BY e agregação, e antes de ORDER BY. Isso significa que a cláusula HAVING é avaliada uma vez para cada linha agregada do conjunto de resultados. No caso da cláusula WHERE, a avaliação acontece antes de GROUP BY e da agregação.

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

Sintaxe

ORDER BY expression
  [{ 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.

Cláusulas opcionais

  • 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 é, NULLs aparecem primeiro em listagens ASC e, por último, em listagens 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
ORDER BY LastName;
SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY 2;

Operadores de conjunto

Sintaxe

UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT 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 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 en = 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 as posições delas nas respectivas listas do 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.
  • Você precisa 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 declaraçã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

Sintaxe

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.

No OFFSET, um skip_rows não negativo do tipo INT64 é especificado, e apenas as linhas desse intervalo da tabela são consideradas.

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.

Cláusula WITH

A cláusula WITH contém uma ou mais subconsultas nomeadas que são executadas toda vez que uma declaração SELECT subsequente as referencia. Qualquer cláusula ou subconsulta pode referenciar subconsultas definidas na cláusula WITH. Isso inclui qualquer instrução SELECT em qualquer um dos lados de um operador de conjunto como UNION.

A cláusula WITH é útil principalmente para facilitar a leitura, porque o BigQuery não materializa o resultado das consultas dentro da cláusula . Se uma consulta aparecer em mais de uma cláusula WITH, ela será executada em cada cláusula.

Exemplo:

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

Outra função útil da cláusula WITH é dividir consultas mais complexas em uma instrução SELECT e em cláusulas . Nesse caso, a alternativa menos desejável é escrever subconsultas de tabela aninhada. Se uma cláusula WITH contém várias subconsultas, os nomes delas não podem se repetir.

O BigQuery é compatível com cláusulas WITH em subconsultas, como de tabela, de expressão e assim por diante.

WITH q1 AS (my_query)
SELECT *
FROM
  (WITH q2 AS (SELECT * FROM q1) SELECT * FROM q2)

Veja as seguintes regras de escopo para cláusulas WITH:

  • O escopo de aliases serve para que, quando introduzidos em uma cláusula WITH, os aliases fiquem visíveis apenas nas subconsultas posteriores da mesma cláusula e na consulta abaixo da cláusula .
  • Os aliases introduzidos na mesma cláusula WITH precisam ser exclusivos. Entretanto, o mesmo alias pode ser usado em várias cláusulas na mesma consulta. O alias local tem prioridade sobre os aliases externos sempre que ele for visível.
  • As subconsultas com alias em uma cláusula WITH nunca podem ser correlacionadas. Nenhuma coluna externa à consulta é visível. Os únicos nomes externos visíveis são outros aliases de WITH introduzidos anteriormente na mesma cláusula WITH.

Veja um exemplo de uma declaração que usa aliases em subconsultas WITH:

WITH q1 AS (my_query)
SELECT *
FROM
  (WITH q2 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q3 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q1 AS (SELECT * FROM q1),  # q1 (in the query) resolves to my_query
        q4 AS (SELECT * FROM q1)   # q1 resolves to the WITH subquery
                                   # on the previous line.
    SELECT * FROM q1)  # q1 resolves to the third inner WITH subquery.

OBSERVAÇÃO: o BigQuery não é compatível com WITH RECURSIVE.

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

Sintaxe do alias explícito

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;

Visibilidade do alias explícito

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

Aliases da cláusula FROM

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

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

Aliases da 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;

Aliases explícitos 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 de SELECT. O inteiro 1 refere-se ao primeiro item da lista do 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 acima é equivalente a:

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

Aliases ambíguos

No BigQuery, um erro é emitido se um nome é ambíguo, ou seja, se ele puder ser resolvido em mais de um objeto exclusivo.

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;

Ambiguidade entre um nome de coluna da cláusula FROM e um alias de lista SELECT em GROUP BY:

SELECT UPPER(LastName) AS LastName
FROM Singers
GROUP BY LastName;

A consulta acima é ambígua e produzirá um erro porque LastName na cláusula GROUP BY poderia se referir à coluna original em Singers ou poderia se referir ao alias AS LastName, cujo valor é UPPER(LastName).

As mesmas regras de ambiguidade aplicam-se a expressões de caminho. Considere a consulta a seguir onde table tem colunas x e y, e a coluna z é do tipo STRUCT e tem os campos v, w e x.

Exemplo:

SELECT x, z AS T
FROM table 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.

Aliases implícitos

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

  • Em 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:

  • Se houver uma expressão que não tenha um alias explícito, o BigQuery atribui um alias implícito nestes casos:
    • Em 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.

Apêndice A: exemplos com dados de amostra

Tabelas de amostra

As três tabelas a seguir contêm dados de amostra de atletas, as escolas deles e os pontos marcados durante a temporada. Usaremos estas tabelas para ilustrar o comportamento das diferentes cláusulas de consulta.

Tabela Roster:

LastName SchoolID
Adams 50
Buchanan 52
Coolidge 52
Davis 51
Eisenhower 77

A tabela Roster inclui uma lista de nomes de jogadores (LastName) e o ID exclusivo atribuído à escola deles (SchoolID).

Tabela PlayerStats:

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

Na tabela PlayerStats está incluída uma lista de nomes de jogadores (LastName), o código exclusivo atribuído ao oponente contra quem jogaram em uma partida específica (OpponentID) e o número de pontos marcados pelo atleta nessa partida (PointsScored).

Tabela TeamMascot:

SchoolId Mascot
50 Jaguars
51 Knights
52 Lakers
53 Mustangs

Na tabela TeamMascot há uma lista de códigos escolares únicos (SchoolID) e a mascote da escola (Mascot).

Tipos de JOIN

1) [INNER] JOIN

Exemplo:

SELECT * FROM Roster JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

Resultados:

LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Buchanan 52 52 Lakers
Coolidge 52 52 Lakers
Davis 51 51 Knights

2) CROSS JOIN

Exemplo:

SELECT * FROM Roster CROSS JOIN TeamMascot;

Resultados:

LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Adams 50 51 Knights
Adams 50 52 Lakers
Adams 50 53 Mustangs
Buchanan 52 50 Jaguars
Buchanan 52 51 Knights
Buchanan 52 52 Lakers
Buchanan 52 53 Mustangs
Coolidge 52 50 Jaguars
Coolidge 52 51 Knights
Coolidge 52 52 Lakers
Coolidge 52 53 Mustangs
Davis 51 50 Jaguars
Davis 51 51 Knights
Davis 51 52 Lakers
Davis 51 53 Mustangs
Eisenhower 77 50 Jaguars
Eisenhower 77 51 Knights
Eisenhower 77 52 Lakers
Eisenhower 77 53 Mustangs

3) FULL [OUTER] JOIN

Exemplo:

SELECT * FROM Roster FULL JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Buchanan 52 52 Lakers
Coolidge 52 52 Lakers
Davis 51 51 Knights
Eisenhower 77 NULL NULL
NULL NULL 53 Mustangs

4) LEFT [OUTER] JOIN

Exemplo:

SELECT * FROM Roster LEFT JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

Resultados:

LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Buchanan 52 52 Lakers
Coolidge 52 52 Lakers
Davis 51 51 Knights
Eisenhower 77 NULL NULL

5) RIGHT [OUTER] JOIN

Exemplo:

SELECT * FROM Roster RIGHT JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

Resultados:

LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Davis 51 51 Knights
Coolidge 52 52 Lakers
Buchanan 52 52 Lakers
NULL NULL 53 Mustangs

Cláusula GROUP BY

Exemplo:

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

Operadores de conjunto

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 DISTINCT
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)

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.