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 [ ALL ] } [ ( ] <query> [ ) ]
[ ORDER BY ... ]
[ LIMIT ... ]
Notas de uso geral¶
Cada consulta em si pode conter operadores de consulta para permitir que qualquer quantidade de expressões de consulta possa ser combinada com operadores de conjunto.
As cláusulas ORDER BY e LIMIT / FETCH são aplicadas ao resultado do operador de conjunto.
Ao utilizar esses operadores:
Certifique-se de que cada consulta selecione o mesmo número de colunas.
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”, assim como os tipos de dados, das colunas devem corresponder. O seguinte 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.
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).
Os operadores de igual precedência são processados 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¶
Os exemplos deste tópico usam as seguintes tabelas de amostra. Ambas as tabelas têm uma coluna de código postal. Uma tabela registra o código postal de cada escritório de vendas, enquanto a outra registra o código postal de cada cliente.
CREATE OR REPLACE TABLE sales_office_zip_example(
office_name VARCHAR,
zip VARCHAR);
INSERT INTO sales_office_zip_example VALUES ('sales1', '94061');
INSERT INTO sales_office_zip_example VALUES ('sales2', '94070');
INSERT INTO sales_office_zip_example VALUES ('sales3', '98116');
INSERT INTO sales_office_zip_example VALUES ('sales4', '98005');
CREATE OR REPLACE TABLE customer_zip_example(
customer VARCHAR,
zip VARCHAR);
INSERT INTO customer_zip_example VALUES ('customer1', '94066');
INSERT INTO customer_zip_example VALUES ('customer2', '94061');
INSERT INTO customer_zip_example VALUES ('customer3', '98444');
INSERT INTO customer_zip_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¶
SELECT ...
INTERSECT
SELECT ...
Exemplos do operador INTERSECT¶
Consulte as tabelas de amostra para encontrar os códigos postais com um escritório de vendas e um cliente:
SELECT zip FROM sales_office_zip_example
INTERSECT
SELECT zip FROM customer_zip_example;
+-------+
| ZIP |
|-------|
| 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¶
SELECT ...
MINUS
SELECT ...
SELECT ...
EXCEPT
SELECT ...
Exemplos do operador MINUS¶
Consulte as tabelas de amostra para encontrar os códigos postais na tabela sales_office_zip_example
que não estejam na tabela customer_zip_example
:
SELECT zip FROM sales_office_zip_example
MINUS
SELECT zip FROM customer_zip_example;
+-------+
| ZIP |
|-------|
| 98116 |
| 94070 |
+-------+
Consulte as tabelas de amostra para encontrar os códigos postais na tabela customer_zip_example
que não estejam na tabela sales_office_zip_example
:
SELECT zip FROM customer_zip_example
MINUS
SELECT zip FROM sales_office_zip_example;
+-------+
| ZIP |
|-------|
| 98444 |
| 94066 |
+-------+
UNION [ ALL ]¶
Combina os conjuntos de resultados de duas consultas:
UNION combina com eiminação de duplicatas.
UNION ALL combina sem eliminação de duplicatas.
O padrão é UNION (ou seja, com eliminação de duplicatas).
Sintaxe¶
SELECT ...
UNION [ ALL ]
SELECT ...
Exemplos do operador UNION¶
Os exemplos a seguir usam o operador UNION.
Use o operador UNION para combinar os resultados de duas consultas¶
Use o operador UNION para combinar os conjuntos de resultados de duas consultas nas tabelas de amostra:
SELECT office_name office_or_customer, zip FROM sales_office_zip_example
UNION
SELECT customer, zip FROM customer_zip_example
ORDER BY zip;
+--------------------+-------+
| OFFICE_OR_CUSTOMER | ZIP |
|--------------------+-------|
| 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 ('Adams, Douglas');
INSERT INTO union_test2 (i) VALUES (42);
Execute uma operação UNION 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 'Adams, Douglas' 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 |
|----------------|
| Adams, Douglas |
| 42 |
+----------------+