Set operators¶
Set operators allow queries to be combined.
General syntax¶
[ ( ] <query> [ ) ] { INTERSECT | { MINUS | EXCEPT } | UNION [ ALL ] } [ ( ] <query> [ ) ]
[ ORDER BY ... ]
[ LIMIT ... ]
General usage notes¶
Each query can itself contain query operators to allow any number of query expressions to be combined with set operators.
The ORDER BY and LIMIT / FETCH clauses are applied to the result of the set operator.
When using these operators:
Make sure that each query selects the same number of columns.
Make sure that the data type of each column is consistent across the rows from different sources. One of the examples in the Examples section below illustrates the potential problem and solution when data types do not match.
In general, the “meanings”, as well as the data types, of the columns should match. The following will not produce the desired results:
SELECT LastName, FirstName FROM employees UNION ALL SELECT FirstName, LastName FROM contractors;
The risk of error increases when using the asterisk to specify all columns of a table, for example:
SELECT * FROM table1 UNION ALL SELECT * FROM table2;
If the tables have the same number of columns, but the columns are not in the same order, the query results will probably be incorrect.
The names of the output columns are based on the names of the columns of the first query. For example, the following query:
SELECT LastName, FirstName FROM employees UNION ALL SELECT FirstName, LastName FROM contractors;
behaves as though the query were:
SELECT LastName, FirstName FROM employees UNION ALL SELECT FirstName AS LastName, LastName AS FirstName FROM contractors;
The precedence of the set operators matches the ANSI and ISO SQL standards:
The
UNION [ALL]
andMINUS
(EXCEPT
) operators have equal precedence.The
INTERSECT
operator has higher precedence thanUNION [ALL]
andMINUS
(EXCEPT
).
Operators of equal precedence are processed from left to right.
You can use parentheses to force the expressions to be evaluated in a different order.
Not all database vendors follow the ANSI/ISO standard for precedence of set operators. Snowflake recommends using parentheses to specify the order of evaluation, especially if you are porting code from another vendor to Snowflake, or writing code that you might execute on other databases as well as on Snowflake.
INTERSECT¶
Returns rows from one query’s result set which also appear in another query’s result set, with duplicate elimination.
Syntax¶
SELECT ...
INTERSECT
SELECT ...
MINUS , EXCEPT¶
Removes rows from one query’s result set which appear in another query’s result set, with duplicate elimination.
The MINUS and EXCEPT keywords have the same meaning and can be used interchangeably.
Syntax¶
SELECT ...
MINUS
SELECT ...
SELECT ...
EXCEPT
SELECT ...
UNION [ ALL ]¶
Combines the result sets from two queries:
UNION combines with duplicate elimination.
UNION ALL combines without duplicate elimination.
The default is UNION (i.e. duplicate elimination).
Syntax¶
SELECT ...
UNION [ ALL ]
SELECT ...
Examples¶
This example demonstrates the basic usage of the UNION operator. It also demonstrates a potential issue when data types do not match, then provides the solution.
Start by creating the tables and inserting some data:
CREATE TABLE t1 (v VARCHAR); CREATE TABLE t2 (i INTEGER); INSERT INTO t1 (v) VALUES ('Adams, Douglas'); INSERT INTO t2 (i) VALUES (42);
Execute a UNION operation with different data types:
SELECT v FROM t1 -- VARCHAR UNION SELECT i FROM t2 -- INTEGER ;Output:
100038 (22018): Numeric value 'Adams, Douglas' is not recognized
Now use explicit casting to convert the inputs to a compatible type:
SELECT v::VARCHAR FROM t1 UNION SELECT i::VARCHAR FROM t2;Output:
+----------------+ | V::VARCHAR | |----------------| | Adams, Douglas | | 42 | +----------------+