Sintaxe da consulta

Com as instruções de consulta, você verifica uma ou mais tabelas ou expressões e retorna as linhas de resultado. Neste tópico, você verá a sintaxe de consultas SQL no Cloud Spanner.

Sintaxe SQL

query_statement:
    [ table_hint_expr ][ join_hint_expr ]
query_expr query_expr: { select | ( query_expr ) | query_expr set_op query_expr } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [{ ALL | DISTINCT }] { [ expression. ]* | expression [ [ AS ] alias ] } [, ...] [ FROM from_item [ tablesample_type ] [, ...] ] [ WHERE bool_expression ] [ GROUP BY expression [, ...] ] [ HAVING bool_expression ] set_op: UNION { ALL | DISTINCT } | INTERSECT { ALL | DISTINCT } | EXCEPT { ALL | DISTINCT } from_item: { table_name [ table_hint_expr ] [ [ AS ] alias ] | join | ( query_expr ) [ table_hint_expr ] [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ table_hint_expr ] [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] } table_hint_expr: '@{' table_hint_key = table_hint_value '}' table_hint_key: { FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION } join: from_item [ join_type ] [ join_method ] JOIN [ join_hint_expr ] from_item [ { ON bool_expression | USING ( join_column [, ...] ) } ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

join_method:
{ HASH }
join_hint_expr: '@{' join_hint_key = join_hint_value [, ...] '}' join_hint_key: { FORCE_JOIN_ORDER | JOIN_METHOD } tablesample_type: TABLESAMPLE sample_method (sample_size percent_or_rows ) sample_method: { BERNOULLI | RESERVOIR } sample_size: numeric_value_expression percent_or_rows: { PERCENT | ROWS }




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  [{ ALL | DISTINCT }]
    { [ expression. ]* | 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_items da cláusula FROM correspondente.

Cada item na lista do SELECT assume 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    |
+-------+-----------+

SELECT expression

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 usada como referência em outro lugar da consulta.

SELECT expression.*

Um item em uma lista de SELECT também pode ter o formato 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.

Na consulta a seguir, uma coluna de saída é produzida para cada coluna da visualização inline da tabela groceries.

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

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

Mais exemplos:

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

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

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

Modificadores do 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 retorna colunas dos seguintes tipos:

  • STRUCT
  • ARRAY

SELECT ALL

Com uma instrução SELECT ALL, todas as linhas são retornadas, incluindo as 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. A consulta a seguir, por exemplo, não é aceita:

    SELECT STRUCT(1, 2) FROM Users;
    
  • O retorno de uma matriz de estruturas na raiz do tipo de retorno é aceito. A consulta a seguir, por exemplo é aceita:

    SELECT ARRAY(SELECT STRUCT(1, 2)) FROM Users;
    
  • No entanto, como as formas de consulta que podem retornar uma estrutura NULL em um resultado de consulta não são aceitas, a consulta a seguir não é aceita:

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

Consulte também as observações sobre como usar STRUCTs em subconsultas.

Aliases

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

Cláusula FROM

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

Sintaxe

from_item: {
    table_name [ table_hint_expr ] [ [ AS ] alias ] |
    join |
    ( query_expr ) [ table_hint_expr ] [ [ AS ] alias ] |
    field_path |
    { UNNEST( array_expression ) | UNNEST( array_path ) | array_path }
        [ table_hint_expr ] [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ]
}
table_hint_expr: '@{' table_hint_key = table_hint_value '}' table_hint_key: { FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION }

table_name

Nome (opcionalmente qualificado) de uma tabela existente.

SELECT * FROM Roster;
SELECT * FROM db.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. Observe que 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

A otimização de agrupamento por verificação pode fazer consultas mais rápido se elas usarem GROUP BY ou SELECT DISTINCT. Isso será possível se as chaves de agrupamento conseguirem formar um prefixo ou uma correspondência exata da tabela subjacente ou de uma chave de índice, e se a consulta exigir apenas a primeira linha de cada grupo.

Normalmente, essa otimização é aplicada quando o otimizador decide tornar a consulta mais 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.

No exemplo a seguir, você verá como usar um índice secundário ao ler uma tabela. Basta anexar uma diretiva de índice no formato @{FORCE_INDEX=index_name} ao nome dela:

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

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 do field_path em uma estrutura de dados aninhada é arbitrária.

Veja abaixo alguns exemplos de valores de field_path válidos:

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 há uma matriz antes do final.

Nota: se um caminho tem apenas um nome, ele é interpretado como uma tabela. Para contornar essa questão, utilize-o com o UNNEST ou defina um caminho totalmente qualificado.

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 FROM com o operador IN.

Para ARRAYs de entrada de quase todos os tipos de elementos, a saída do UNNEST geralmente tem uma coluna. Nessa única coluna tem um alias opcional que você pode usar para se referir à coluna em 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).

Para um ARRAY de entrada de STRUCTs, UNNEST retorna uma linha para cada STRUCT, com uma coluna separada para cada campo no 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 |
+---+-----+

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, o array_path deve 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 originam nenhuma linha.
  • Uma ARRAY que contém NULLs origina 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;

Consulte Arrays topic para conhecer outras maneiras de usar UNNEST, como construção, nivelamento e filtragem.

Subconsultas

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

Há dois tipos de subconsulta:

  • As subconsultas de expressão, que podem ser usadas em uma consulta sempre que as expressões são válidas, retornam um valor único.
  • As subconsultas de tabela, que podem ser usadas apenas em uma cláusula FROM, resultam de uma consulta externa. Essa consulta 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;

Observações sobre subconsultas

Ao usar subconsultas, lembre-se disto:

  • Se uma subconsulta de expressão retornar mais de um valor, os resultados dessa subconsulta precisam ser retornados em uma matriz usando a palavra-chave ARRAY. Se ARRAY não estiver presente, a sintaxe da subconsulta é válida, mas quando a consulta é executada, essa subconsulta não pode retornar mais de um valor cada vez que é invocada. Se retornar mais de um valor, a consulta falhará durante a execução.
  • As subconsultas de expressão que selecionam mais de uma coluna precisam retornar os valores de coluna em uma estrutura usando AS STRUCT.

Veja abaixo um exemplo desse tipo consulta:

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

O uso de uma subseleção para buscar linhas de uma tabela intercalada é um padrão comum ao ler dados de bancos de dados do Cloud Spanner. É importante lembrar que o relacionamento de intercalação não faz parte do modelo de dados do SQL e que a subseleção precisa unir as linhas relacionadas neste caso.

Veja um exemplo de uma subconsulta que seleciona linhas relacionadas a partir de uma tabela intercalada e inclui uma condição de junção explícita:

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

Consulte também as observações sobre como usar STRUCTs nas listas de SELECT.

Operador TABLESAMPLE

É possível usar o operador TABLESAMPLE para selecionar uma amostra aleatória de um conjunto de dados. Esse operador é útil ao trabalhar com tabelas que têm grandes quantidades de dados e respostas precisas não são necessárias.

Sintaxe:

tablesample_type:
    TABLESAMPLE sample_method (sample_size percent_or_rows )
sample_method: { BERNOULLI | RESERVOIR }
sample_size: numeric_value_expression
percent_or_rows: { PERCENT | ROWS }


Ao usar o operador TABLESAMPLE, é preciso especificar o algoritmo de amostragem a ser usado:

  • BERNOULLI: cada linha é selecionada independentemente com a probabilidade dada 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 a K, onde qualquer amostra do tamanho K é igualmente provável.

O operador TABLESAMPLE exige que você selecione ROWS ou PERCENT. Se você selecionar PERCENT, o valor precisa estar entre 0 e 100. Se você selecionar ROWS, o valor precisa ser maior ou igual a 0.

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

Usar TABLESAMPLE com uma subconsulta:

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

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

Aliases

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

Tipos de JOIN

Sintaxe

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

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

join_method:
{ HASH }
join_hint_expr: '@{' join_hint_key = join_hint_value [, ...] '}' join_hint_key: { FORCE_JOIN_ORDER | JOIN_METHOD }



Com a cláusula JOIN, dois from_items são mesclados para que possam ser consultados em uma cláusula SELECT como uma única fonte. O join_type e as cláusulas ON ou USING (uma "condição de junção") especificam como as linhas dos dois from_items precisam ser combinadas e descartadas para formar uma única fonte.

Um join_type é obrigatório em todas as cláusulas JOIN.

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.

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 verdadeiro, use a ordem de junção especificada na consulta.
JOIN_METHOD HASH_JOIN
APPLY_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 ambas.

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. Para mais detalhes, consulte Operadores de execução de consulta, especialmente as seções sobre os operadores apply e hash join.

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.

[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

Com o CROSS JOIN, o produto cartesiano dos dois from_items é retornado. Em outras palavras, ele retém todas as linhas dos dois from_items e combina cada linha do primeiro from_item com cada uma do segundo from_item.

Cross joins implícitos

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 "cross join implícito":

SELECT * FROM Roster, TeamMascot;

Veja um equivalente de cross join explícito:

SELECT * FROM Roster CROSS JOIN TeamMascot;

Não escreva os cross joins implícitos entre parênteses.

Cross join implícito entre parênteses inválido:

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

Consulte Sequências de JOINs para detalhes sobre o comportamento de um cross join implícito 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 dos dois from_items serão retornadas, ainda que não atendam à condição de junção.

OUTER indica que, se uma linha específica de um from_item não faz junção com nenhuma linha do outro from_item, todas as colunas do outro from_item são preenchidas com NULLs na linha retornada.

LEFT [OUTER] JOIN

O resultado de um LEFT OUTER JOIN, ou apenas LEFT JOIN, para dois from_items sempre retém todas as linhas do from_item à esquerda na cláusula JOIN, mesmo que nenhuma linha do from_item à direita atenda ao predicado da junção.

LEFT indica que todas as linhas do from_item à esquerda serão retornadas. Se uma determinada linha do from_item à esquerda não se juntar a uma linha do from_item à direita, ela retornará com NULLs para todas as colunas do from_item à direita. As linhas do from_item à direita que não fazem junção com nenhuma linha do from_item à esquerda são descartadas.

RIGHT [OUTER] JOIN

O resultado de um RIGHT OUTER JOIN, ou apenas RIGHT JOIN, é similar e simétrico ao do LEFT OUTER JOIN.

Cláusula ON

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

Exemplo:

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

Cláusula USING

Para a cláusula USING, é necessária uma column_list 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 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 SELECT *, os resultados das consultas com USING são diferentes das consultas com ON. Como exemplo, veja a seguinte consulta:

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

Essa instrução retorna as linhas das tabelas 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 das tabelas Roster e PlayerStats, em que Roster.LastName é igual a PlayerStats.LastName. Os resultados incluem duas colunas LastName, uma da Roster e uma da 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 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.

Veja as seguintes consultas: A (sem parênteses) e B (com parênteses) são equivalentes entre si, mas não equivalem a 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 os cross joins implícitos estão presentes em uma consulta com uma sequência de JOINs, eles são agrupados 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 use um RIGHT JOIN ou FULL JOIN após um join implícito.

RIGHT JOIN após um cross join implícito inválido:

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;

O bool_expression pode conter múltiplas subcondições.

Exemplo:

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

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

As expressões em um INNER JOIN têm uma equivalente na cláusula WHERE. Por exemplo, uma consulta com INNER JOIN e ON tem uma equivalente com CROSS JOIN e WHERE.

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

Com a cláusula GROUP BY é possível agrupar linhas em uma tabela com valores não distintos para a expression na cláusula GROUP BY. Para várias linhas na tabela de origem com valores não distintos da expression, com a cláusula GROUP BY é produzida uma única linha combinada. O GROUP BY normalmente é usado quando existem funções agregadas na lista SELECT ou para eliminar redundância na saída. O tipo de dados de expression precisa ser agrupável.

Exemplo:

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

A cláusula GROUP BY pode fazer referência a nomes de expressão da lista do SELECT. A cláusula GROUP BY também permite referências ordinais a expressões na lista SELECT usando valores inteiros. 1 se refere à 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

Sintaxe

HAVING bool_expression

A cláusula HAVING é semelhante à cláusula WHERE. Com ela, as linhas que não retornam TRUE quando avaliadas em relação à bool_expression são descartadas.

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 GROUP BY ou agregação presentes na consulta.
  • A cláusula HAVING ocorre após o GROUP BY e a agregação e antes do 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 do 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 do 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 contém 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 em si 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 do 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. O sentido padrão da classificação é ASC, ou seja, os resultados são classificados na ordem crescente dos valores da expression. Com o DESC, isso é feito na ordem decrescente. Os aliases de coluna da cláusula FROM ou da lista do SELECT são permitidos. Se uma consulta contém aliases na cláusula SELECT, eles substituem os nomes na cláusula FROM correspondente.

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, os NULLs são o menor valor possível, ou seja, eles aparecem primeiro nas classificações ASC e por último nas 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 não seja obrigatório.

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 inteiros como referência de coluna nas 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;

Cláusula COLLATE

É possível usar a cláusula COLLATE para refinar como os dados são ordenados a partir de uma cláusula ORDER BY. Collation (agrupamento) 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 idioma, região ou país específico. Essas regras podem definir a sequência de caracteres correta, com opções para especificar indiferença às maiúsculas.

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

Adicione o agrupamento à instrução da seguinte maneira:

SELECT ...
FROM ...
ORDER BY value COLLATE collation_string

Uma collation_string contém um collation_name e pode ter um collation_attribute opcional, como um sufixo, separado por dois pontos. A collation_string é um literal ou um parâmetro. Geralmente, esse nome é composto por duas letras que representam o idioma. Elas podem ser seguidas por um sublinhado e duas letras que representam a região, por exemplo, en_US. Esses nomes são definidos pelo Common Locale Data Repository (CLDR) [em inglês]. Uma instrução também pode ter um collation_name de unicode. Esse valor significa que a instrução retornará dados usando o agrupamento unicode padrão.

Além do collation_name, uma collation_string pode ter um collation_attribute opcional. como um sufixo, separado por dois pontos. Este atributo especifica se as comparações de dados farão distinção entre maiúsculas e minúsculas. Os valores permitidos são cs, para distinção entre maiúsculas e minúsculas, e ci, para indiferença entre elas. Se um collation_attribute não for fornecido, os padrões CLDR serão usados.

Exemplos da cláusula COLLATE

Agrupar resultados usando o inglês do Canadá:

SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_CA"

Agrupar 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 "unicode:ci"

Operadores de conjunto

Sintaxe

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

Com operadores definidos são combinados 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 como "MIN (m, n)" no resultado.
  • Para EXCEPT ALL, R aparece exatamente como "MAX (m - n, 0)" no resultado.
  • Para UNION DISTINCT, DISTINCT é processado depois de UNION, e o R aparece exatamente uma vez.
  • Para INTERSECT DISTINCT, o DISTINCT é calculado após o resultado acima ser calculado.
  • Para EXCEPT DISTINCT, a linha R aparece uma vez na saída se m> 0 e n = 0.
  • Se há mais de duas consultas de entrada, as operações descritas acima aplicam-se 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 diferentes de UNION ALL, todos os tipos de colunas 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.
  • No conjunto de resultados, sempre são usados 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 set diferentes. Para essa finalidade, operações set, como UNION ALL e UNION DISTINCT, são diferentes. Se a declaração repetir somente a mesma operação set, 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.

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 após ORDER BY.

Aliases

Um alias é um nome temporário atribuído a uma tabela, coluna ou expressão presente em uma consulta. Você pode introduzir aliases explícitos na lista de SELECT ou na cláusula FROM, ou o Cloud Spanner SQL inferirá um alias implícito para algumas expressões. Expressões sem um alias implícito ou explícito são anônimas. Nesse caso, a consulta não pode conter referência a elas por nome.

Sintaxe do alias explícito

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

Em uma cláusula FROM, introduza aliases explícitos para qualquer item como 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;

Introduza aliases explícitos em qualquer expressão na lista do 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 sobre a visibilidade do alias são consequência das regras de escopo de nome do Cloud Spanner SQL.

Aliases da cláusula FROM

O Cloud Spanner SQL processa alias em uma cláusula FROM da esquerda para a direita, e os alias são visíveis somente para expressões de caminho subsequentes em uma cláusula FROM.

Exemplo:

Considere que a tabela Singers tem 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.

FROM clause aliases are not visible to subqueries in the same FROM clause. 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 tiver um alias explícito, será preciso usá-lo para o restante da consulta em vez do alias implícito. Consulte Aliases implícitos. Um alias de tabela é útil para simplificar ou eliminar ambiguidades em casos como self joins, 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: o 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 do SELECT

Os aliases da lista de SELECT são visíveis apenas para as cláusulas a seguir:

  • 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

Nessas três cláusulas, GROUP BY, ORDER BY e HAVING, podem haver referências apenas aos seguintes valores:

  • tabelas na cláusula FROM e qualquer uma das colunas delas
  • aliases da lista do SELECT

No GROUP BY e no ORDER BY, também podem haver referências 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 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

O Cloud Spanner SQL apresenta erro quando um nome é ambíguo, o que significa que ele pode resolver mais de um objeto exclusivo.

Exemplos:

Esta consulta contém nomes de coluna com um conflito entre as tabelas, uma vez que tanto Singers quanto Songs têm uma coluna denominada SingerID:

SELECT SingerID
FROM Singers, Songs;

A consulta a seguir contém aliases ambíguos na cláusula GROUP BY porque eles estão duplicados na lista do 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 da lista do SELECT no GROUP BY:

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

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

As mesmas regras de ambiguidade aplicam-se a expressões de caminho. Veja a seguinte consulta, em que table tem colunas x e y, e a coluna z é do tipo STRUCT e tem campos v, w e x.

Exemplo:

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

O alias T é ambíguo e causa um erro porque T.x na cláusula GROUP BY pode referir-se tanto à table.x quanto à table.z.x.

Um nome não é ambíguo em GROUP BY, ORDER BY ou HAVING quando aparece ao mesmo tempo como um nome de coluna e um alias de lista do SELECT, desde que seja resolvido como o mesmo objeto subjacente.

Exemplo:

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

O alias BirthYear não é ambíguo porque é resolvido como a mesma coluna subjacente Singers.BirthYear.

Aliases implícitos

Na lista de SELECT, se houver uma expressão sem um alias explícito, o Cloud Spanner SQL atribuirá um alias implícito 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 em AS abc.
  • Em expressões de caminho, o alias é o último identificador dele. Por exemplo, SELECT abc.def.ghi implica em 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 em 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 marcador gerado para ela, mas esse marcador não pode ser usado da mesma forma que um alias.

Em uma cláusula FROM, não é necessário que from_items tenham um alias. Aplicam-se as seguintes regras:

  • Se uma expressão não tem um alias explícito, o Cloud Spanner SQL atribui um alias implícito nos casos a seguir:
    • 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 essas 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 Mascote
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 (Mascote).

Tipos de JOIN

1) [INNER] JOIN

Exemplo:

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

Resultados:

LastName Roster.SchoolId TeamMascot.SchoolId Mascote
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
ON Roster.SchoolID = TeamMascot.SchoolID;

Resultados:

LastName Roster.SchoolId TeamMascot.SchoolId Mascote
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 Mascote
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 Mascote
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 Mascote
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 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 inversão da ordem das instruções SELECT retornará os sobrenomes constantes na PlayerStats, mas que não estão 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…

Documentação do Cloud Spanner