Set operators¶

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 Use the UNION operator and cast mismatched data types section illustrates the potential problem and solution when data types don’t match.

  • In general, the “meanings”, as well as the data types, of the columns should match. The following won’t produce the desired results:

    SELECT LastName, FirstName FROM employees
    UNION ALL
    SELECT FirstName, LastName FROM contractors;
    

    Copy

    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;
    

    Copy

    If the tables have the same number of columns, but the columns aren’t 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, consider the following query:

    SELECT LastName, FirstName FROM employees
    UNION ALL
    SELECT FirstName, LastName FROM contractors;
    

    Copy

    This query behaves as though the query were the following:

    SELECT LastName, FirstName FROM employees
    UNION ALL
    SELECT FirstName AS LastName, LastName AS FirstName FROM contractors;
    

    Copy

• The precedence of the set operators matches the ANSI and ISO SQL standards:

  • The UNION [ALL] and MINUS (EXCEPT) operators have equal precedence.

  • The INTERSECT operator has higher precedence than UNION [ALL] and MINUS (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.

Sample tables for examples¶

The examples in this topic use the following sample tables. Both tables have a zip code column. One table records the zip code of each sales office, while the other records the zip code of each customer.

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¶

Returns rows from one query’s result set which also appear in another query’s result set, with duplicate elimination.

SELECT ...
INTERSECT
SELECT ...

SELECT zip FROM sales_office_zip_example
INTERSECT
SELECT zip FROM customer_zip_example;

+-------+
| ZIP   |
|-------|
| 94061 |
| 98005 |
+-------+

MINUS , EXCEPT¶

Returns the rows returned by the first query that aren’t also returned by the second query.

The MINUS and EXCEPT keywords have the same meaning and can be used interchangeably.

SELECT ...
MINUS
SELECT ...

SELECT ...
EXCEPT
SELECT ...

SELECT zip FROM sales_office_zip_example
MINUS
SELECT zip FROM customer_zip_example;

+-------+
| ZIP   |
|-------|
| 98116 |
| 94070 |
+-------+

SELECT zip FROM customer_zip_example
MINUS
SELECT zip FROM sales_office_zip_example;

+-------+
| ZIP   |
|-------|
| 98444 |
| 94066 |
+-------+

UNION [ ALL ]¶

Combines the result sets from two queries:

• UNION combines with duplicate elimination.

• UNION ALL combines without duplicate elimination.

The default is UNION (that is, with duplicate elimination).

SELECT ...
UNION [ ALL ]
SELECT ...

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

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

SELECT v FROM union_test1
UNION
SELECT i FROM union_test2;

100038 (22018): Numeric value 'Adams, Douglas' is not recognized

SELECT v::VARCHAR FROM union_test1
UNION
SELECT i::VARCHAR FROM union_test2;

+----------------+
| V::VARCHAR     |
|----------------|
| Adams, Douglas |
| 42             |
+----------------+