Set Operators

Set operators allow queries to be combined.

General Syntax

[ ( ] <query> [ ) ] { INTERSECT | { MINUS | EXCEPT } | UNION [ ALL ] } [ ( ] <query> [ ) ]
[ ORDER BY ... ]
[ LIMIT ... ]
Copy

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

      behaves as though the query were:

      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.

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 ...
Copy

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 ...
Copy

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 ...
Copy

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

Execute a UNION operation with different data types:

SELECT v FROM t1    -- VARCHAR
UNION
SELECT i FROM t2    -- INTEGER
;
Copy

Output:

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

Now use explicit casting to convert the inputs to a compatible type:

SELECT v::VARCHAR FROM t1
UNION
SELECT i::VARCHAR FROM t2;
Copy

Output:

+----------------+
| V::VARCHAR     |
|----------------|
| Adams, Douglas |
| 42             |
+----------------+
Copy
Language: English