Operadores de conjuntos¶
Os operadores de conjunto combinam os resultados intermediários de vários blocos de consulta em um único conjunto de resultados.
Sintaxe geral¶
[ ( ] <query> [ ) ]
{
INTERSECT |
{ MINUS | EXCEPT } |
UNION [ { DISTINCT | ALL } ] [ BY NAME ]
}
[ ( ] <query> [ ) ]
[ ORDER BY ... ]
[ LIMIT ... ]
Notas de uso geral¶
Cada consulta em si pode conter operadores de consulta, para que você possa combinar várias expressões de consulta com operadores de conjunto.
Você pode aplicar as cláusulas ORDER BY e LIMIT / FETCH ao resultado do operador de conjunto.
Ao utilizar esses operadores:
Certifique-se de que cada consulta selecione o mesmo número de colunas, com exceção das consultas que incluem UNION BY NAME ou UNION ALL BY NAME.
Certifique-se de que o tipo de dados de cada coluna seja consistente entre as linhas de diferentes fontes. Um dos exemplos na seção Como usar o operador UNION e converter tipos de dados incompatíveis ilustra o possível problema e a solução quando os tipos de dados não correspondem.
Em geral, os “significados” das colunas, assim como os tipos de dados, devem corresponder. A seguinte consulta com o operador UNION ALL não produzirá os resultados desejados:
SELECT LastName, FirstName FROM employees UNION ALL SELECT FirstName, LastName FROM contractors;
O risco de erro aumenta ao utilizar o asterisco para especificar todas as colunas de uma tabela, por exemplo:
SELECT * FROM table1 UNION ALL SELECT * FROM table2;
Se as tabelas tiverem o mesmo número de colunas, mas as colunas não estiverem na mesma ordem, os resultados de consulta provavelmente estarão incorretos ao usar esses operadores.
Os operadores UNION BY NAME e UNION ALL BY NAME são exceções para esse cenário. Por exemplo, a consulta a seguir retorna os resultados corretos:
SELECT LastName, FirstName FROM employees UNION ALL BY NAME SELECT FirstName, LastName FROM contractors;
Os nomes das colunas de saída são baseados nos nomes das colunas da primeira consulta. Por exemplo, considere a seguinte consulta:
SELECT LastName, FirstName FROM employees UNION ALL SELECT FirstName, LastName FROM contractors;
Essa consulta se comporta como se a consulta fosse a seguinte:
SELECT LastName, FirstName FROM employees UNION ALL SELECT FirstName AS LastName, LastName AS FirstName FROM contractors;
A precedência dos operadores do conjunto corresponde aos padrões ANSI e ISO SQL:
Os operadores UNION [ALL] e MINUS (EXCEPT) têm a mesma precedência.
O operador INTERSECT tem maior precedência que UNION [ALL] e MINUS (EXCEPT).
O Snowflake processa operadores de igual precedência da esquerda para a direita.
Você pode usar parênteses para forçar as expressões a serem avaliadas em uma ordem diferente.
Nem todos os fornecedores de banco de dados seguem o padrão ANSI/ISO para precedência dos operadores definidos. Snowflake recomenda o uso de parênteses para especificar a ordem de avaliação, especialmente se você estiver portando código de outro fornecedor para Snowflake, ou escrevendo código que você pode executar em outros bancos de dados, assim como em Snowflake.
Tabelas de amostra para exemplos¶
Alguns dos exemplos deste tópico usam as tabelas de amostra a seguir. As duas tabelas têm uma coluna de código postal. Uma tabela registra o código postal de cada escritório de vendas e a outra registra o código postal de cada cliente.
CREATE OR REPLACE TABLE sales_office_postal_example(
office_name VARCHAR,
postal_code VARCHAR);
INSERT INTO sales_office_postal_example VALUES ('sales1', '94061');
INSERT INTO sales_office_postal_example VALUES ('sales2', '94070');
INSERT INTO sales_office_postal_example VALUES ('sales3', '98116');
INSERT INTO sales_office_postal_example VALUES ('sales4', '98005');
CREATE OR REPLACE TABLE customer_postal_example(
customer VARCHAR,
postal_code VARCHAR);
INSERT INTO customer_postal_example VALUES ('customer1', '94066');
INSERT INTO customer_postal_example VALUES ('customer2', '94061');
INSERT INTO customer_postal_example VALUES ('customer3', '98444');
INSERT INTO customer_postal_example VALUES ('customer4', '98005');
INTERSECT¶
Retorna linhas do conjunto de resultados de uma consulta que também aparecem no conjunto de resultados de outra consulta, com eliminação de duplicatas.
Sintaxe¶
[ ( ] <query> [ ) ]
INTERSECT
[ ( ] <query> [ ) ]
Exemplos do operador INTERSECT¶
Para encontrar os códigos postais que estão em nas tabelas sales_office_postal_example e customer_postal_example, consulte as tabelas de amostra:
SELECT postal_code FROM sales_office_postal_example
INTERSECT
SELECT postal_code FROM customer_postal_example
ORDER BY postal_code;
+-------------+
| POSTAL_CODE |
|-------------|
| 94061 |
| 98005 |
+-------------+
MINUS , EXCEPT¶
Retorna as linhas retornadas pela primeira consulta que não são retornadas pela segunda.
As palavras-chave MINUS e EXCEPT têm o mesmo significado e podem ser usadas de forma intercambiável.
Sintaxe¶
[ ( ] <query> [ ) ]
MINUS
[ ( ] <query> [ ) ]
[ ( ] <query> [ ) ]
EXCEPT
[ ( ] <query> [ ) ]
Exemplos do operador MINUS¶
Consulte as tabelas de amostra para encontrar os códigos postais na tabela sales_office_postal_example que não estejam também na tabela customer_postal_example:
SELECT postal_code FROM sales_office_postal_example
MINUS
SELECT postal_code FROM customer_postal_example
ORDER BY postal_code;
+-------------+
| POSTAL_CODE |
|-------------|
| 94070 |
| 98116 |
+-------------+
Consulte as tabelas de amostra para encontrar os códigos postais na tabela customer_postal_example que não estejam também na tabela sales_office_postal_example:
SELECT postal_code FROM customer_postal_example
MINUS
SELECT postal_code FROM sales_office_postal_example
ORDER BY postal_code;
+-------------+
| POSTAL_CODE |
|-------------|
| 94066 |
| 98444 |
+-------------+
UNION [ { DISTINCT | ALL } ] [ BY NAME ]¶
Combina os conjuntos de resultados de duas consultas:
UNION [ DISTINCT ] combina linhas por posição de coluna com eliminação de duplicatas.
UNION ALL combina linhas por posição de coluna sem eliminação de duplicatas.
UNION [ DISTINCT ] BY NAME combina linhas por nome de coluna com eliminação de duplicatas.
UNION ALL BY NAME combina linhas por nome de coluna sem eliminação de duplicatas.
O padrão é UNION DISTINCT (ou seja, combinar linhas por posição de coluna com eliminação de duplicatas). A palavra-chave DISTINCT é opcional. As palavras-chave DISTINCT e ALL são mutuamente exclusivas.
Use UNION ou UNION ALL quando as posições das colunas corresponderem nas tabelas que você está combinando. Use UNION BY NAME ou UNION ALL BY NAME para os seguintes casos de uso:
As tabelas que você está combinando têm ordens de coluna diferentes.
As tabelas que você está combinando têm esquemas em evolução, onde as colunas são adicionadas ou reordenadas.
Você deseja combinar subconjuntos de colunas que têm posições diferentes nas tabelas.
Sintaxe¶
[ ( ] <query> [ ) ]
UNION [ { DISTINCT | ALL } ] [ BY NAME ]
[ ( ] <query> [ ) ]
Notas de uso da cláusula BY NAME¶
Além das notas de uso geral, as seguintes notas de uso se aplicam a UNION BY NAME e UNION ALL BY NAME:
As colunas com os mesmos identificadores são correspondidas e combinadas. A correspondência de identificadores sem aspas não diferencia maiúsculas de minúsculas, e a correspondência de identificadores entre aspas diferencia maiúsculas de minúsculas.
As entradas não são obrigatórias para ter o mesmo número de colunas. Se existir uma coluna em uma entrada, mas não na outra, ela será preenchida com valores NULL no conjunto de resultados combinados de cada linha em que estiver faltando.
A ordem das colunas no conjunto de resultados combinados é determinada pela ordem das colunas exclusivas da esquerda para a direita, conforme encontradas primeiro.
Exemplos do operador UNION¶
Os exemplos a seguir usam o operador UNION.
Combinação dos resultados de duas consultas por posição de coluna
Combinação dos resultados de duas consultas por nome de coluna
Use um alias para combinar os resultados de duas consultas com nomes de colunas diferentes
Como usar o operador UNION e converter tipos de dados incompatíveis
Combinação dos resultados de duas consultas por posição de coluna¶
Para combinar os conjuntos de resultados por posição de coluna a partir de duas consultas nas tabelas de amostra, use o operador UNION:
SELECT office_name office_or_customer, postal_code FROM sales_office_postal_example
UNION
SELECT customer, postal_code FROM customer_postal_example
ORDER BY postal_code;
+--------------------+-------------+
| OFFICE_OR_CUSTOMER | POSTAL_CODE |
|--------------------+-------------|
| sales1 | 94061 |
| customer2 | 94061 |
| customer1 | 94066 |
| sales2 | 94070 |
| sales4 | 98005 |
| customer4 | 98005 |
| sales3 | 98116 |
| customer3 | 98444 |
+--------------------+-------------+
Combinação dos resultados de duas consultas por nome de coluna¶
Crie duas tabelas com ordem de coluna diferente e insira dados:
CREATE OR REPLACE TABLE union_demo_column_order1 (
a INTEGER,
b VARCHAR);
INSERT INTO union_demo_column_order1 VALUES
(1, 'one'),
(2, 'two'),
(3, 'three');
CREATE OR REPLACE TABLE union_demo_column_order2 (
B VARCHAR,
A INTEGER);
INSERT INTO union_demo_column_order2 VALUES
('three', 3),
('four', 4);
Para combinar os conjuntos de resultados por nome de coluna de duas consultas, use o operador UNION BY NAME:
SELECT * FROM union_demo_column_order1
UNION BY NAME
SELECT * FROM union_demo_column_order2
ORDER BY a;
+---+-------+
| A | B |
|---+-------|
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
+---+-------+
A saída mostra que a consulta eliminou a linha duplicada (com 3 na coluna A e three na coluna B).
Para combinar as tabelas sem eliminação de duplicatas, use o operador UNION ALL BY NAME:
SELECT * FROM union_demo_column_order1
UNION ALL BY NAME
SELECT * FROM union_demo_column_order2
ORDER BY a;
+---+-------+
| A | B |
|---+-------|
| 1 | one |
| 2 | two |
| 3 | three |
| 3 | three |
| 4 | four |
+---+-------+
Observe que os casos dos nomes das colunas não correspondem nas duas tabelas. Os nomes das colunas estão em letras minúsculas na tabela union_demo_column_order1 e maiúsculas na tabela union_demo_column_order2. Se você executar uma consulta entre aspas ao redor dos nomes das colunas, um erro é retornado porque a correspondência dos identificadores entre aspas diferencia maiúsculas de minúsculas. Por exemplo, a consulta a seguir coloca os nomes das colunas entre aspas:
SELECT 'a', 'b' FROM union_demo_column_order1
UNION ALL BY NAME
SELECT 'B', 'A' FROM union_demo_column_order2
ORDER BY a;
000904 (42000): SQL compilation error: error line 4 at position 9
invalid identifier 'A'
Use um alias para combinar os resultados de duas consultas com nomes de colunas diferentes¶
Quando você usa o operador UNION BY NAME para combinar os conjuntos de resultados por nome de coluna de duas consultas nas tabelas de amostra, as linhas no conjunto de resultados têm valores NULL porque os nomes das colunas não correspondem:
SELECT office_name, postal_code FROM sales_office_postal_example
UNION BY NAME
SELECT customer, postal_code FROM customer_postal_example
ORDER BY postal_code;
+-------------+-------------+-----------+
| OFFICE_NAME | POSTAL_CODE | CUSTOMER |
|-------------+-------------+-----------|
| sales1 | 94061 | NULL |
| NULL | 94061 | customer2 |
| NULL | 94066 | customer1 |
| sales2 | 94070 | NULL |
| sales4 | 98005 | NULL |
| NULL | 98005 | customer4 |
| sales3 | 98116 | NULL |
| NULL | 98444 | customer3 |
+-------------+-------------+-----------+
A saída mostra que colunas com identificadores diferentes não são combinadas e que linhas têm valores NULL para colunas que estão em uma tabela, mas não na outra. A coluna postal_code está nas duas tabelas, portanto, não há valores NULL na saída da coluna postal_code.
A consulta a seguir usa o alias office_or_customer de modo que as colunas com nomes diferentes tenham o mesmo nome durante a consulta:
SELECT office_name AS office_or_customer, postal_code FROM sales_office_postal_example
UNION BY NAME
SELECT customer AS office_or_customer, postal_code FROM customer_postal_example
ORDER BY postal_code;
+--------------------+-------------+
| OFFICE_OR_CUSTOMER | POSTAL_CODE |
|--------------------+-------------|
| sales1 | 94061 |
| customer2 | 94061 |
| customer1 | 94066 |
| sales2 | 94070 |
| sales4 | 98005 |
| customer4 | 98005 |
| sales3 | 98116 |
| customer3 | 98444 |
+--------------------+-------------+
Como usar o operador UNION e converter tipos de dados incompatíveis¶
Este exemplo demonstra um possível problema com o uso do operador UNION quando os tipos de dados não correspondem e, em seguida, apresenta a solução.
Comece criando as tabelas e inserindo alguns dados:
CREATE OR REPLACE TABLE union_test1 (v VARCHAR);
CREATE OR REPLACE TABLE union_test2 (i INTEGER);
INSERT INTO union_test1 (v) VALUES ('Smith, Jane');
INSERT INTO union_test2 (i) VALUES (42);
Executar um UNION por operação da posição da coluna com diferentes tipos de dados (um valor VARCHAR em union_test1 e um valor INTEGER em union_test2):
SELECT v FROM union_test1
UNION
SELECT i FROM union_test2;
Esta consulta retorna um erro:
100038 (22018): Numeric value 'Smith, Jane' is not recognized
Agora use conversão explícita para converter as entradas em um tipo compatível:
SELECT v::VARCHAR FROM union_test1
UNION
SELECT i::VARCHAR FROM union_test2;
+-------------+
| V::VARCHAR |
|-------------|
| Smith, Jane |
| 42 |
+-------------+