Snowflake Set Operators: UNION, EXCEPT/MINUS and INTERSECT

Snowflake set operators are used to combine similar resultset from two or more SELECT statements. The data type of the two or more result sets should also match, otherwise explicit type cast has to be used to convert the result set to required data types.

Types of Snowflake Set Operators

Cloud data warehouse supports the three types of set operators:

UNION and UNION ALL
INTERSECT
EXCEPT or MINUS

Snowflake Set Operators Syntax

Below is the syntax of Set operators in Snowflake:

query
 { UNION [ ALL ] | INTERSECT | EXCEPT | MINUS }
query;

Test Data

Following tables will be used to test the set operations in Snowflake;

SAMPLE_TABLE_SET
+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
|    1 |    2 | A    |
|    2 |    3 | B    |
|    4 |    5 | C    |
|    5 |    6 | D    |
+------+------+------+

SAMPLE_TABLE_SET2
+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
|    4 |    5 | C    |
|    2 |    3 | B    |
+------+------+------+

Now, let us check Snowflake set operations with an examples.

Snowflake UNION and UNION ALL

The UNION and UNION ALL set operations combine the results set from the two or more similar SELECT statements. The result contains the rows returned by the two or more SELECT statements.

The UNION and UNION ALL set operations in Snowflake are different from the JOIN, which combines results based on the common columns in two tables.

UNION combines with duplicate elimination.
UNION ALL combines result with duplicate records if any.

The UNION operation is usually costly because it sorts the records to eliminate duplicate rows.

Redshift UNION and UNION ALL Examples

Following example demonstrates the UNION operator.

SELECT col1, col2, col3 FROM SAMPLE_TABLE_SET
UNION 
SELECT col1, col2, col3 FROM SAMPLE_TABLE_SET2;

+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
|    1 |    2 | A    |
|    2 |    3 | B    |
|    4 |    5 | C    |
|    5 |    6 | D    |
+------+------+------+

As you can see, duplicate records are eliminated.

Following example demonstrate UNION ALL which retains duplicate values.

SELECT col1, col2, col3 FROM SAMPLE_TABLE_SET
UNION ALL
SELECT col1, col2, col3 FROM SAMPLE_TABLE_SET2;

+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
|    1 |    2 | A    |
|    2 |    3 | B    |
|    4 |    5 | C    |
|    5 |    6 | D    |
|    4 |    5 | C    |
|    2 |    3 | B    |
+------+------+------+

Snowflake INTERSECT

The Snowflake INTERSECT set operator returns the rows that are common to both query sets. In other words, it returns rows from one query’s result set which also appear in another query’s result set. Meanwhile, intersect also eliminates the duplicate if any.

Snowflake INTERSECT Example

For example, consider below query.

SELECT col1, col2, col3 FROM SAMPLE_TABLE_SET
INTERSECT
SELECT col1, col2, col3 FROM SAMPLE_TABLE_SET2;

+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
|    2 |    3 | B    |
|    4 |    5 | C    |
+------+------+------+

Snowflake EXCEPT/MINUS

The Snowflake EXCEPT/MINUS operator finds the difference between the two sub-queries and return the result contains the rows from the first SELECT statement. Both EXCEPT and MINUS operators are same. In short, you can either use EXCEPT or MINUS in the query construct.

Snowflake EXCEPT/MINUS Examples

Following example uses Except/MINUS operators.

SELECT col1, col2, col3 FROM SAMPLE_TABLE_SET
EXCEPT
SELECT col1, col2, col3 FROM SAMPLE_TABLE_SET2;

+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
|    1 |    2 | A    |
|    5 |    6 | D    |
+------+------+------+

SELECT col1, col2, col3 FROM SAMPLE_TABLE_SET
MINUS
SELECT col1, col2, col3 FROM SAMPLE_TABLE_SET2;

+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
|    1 |    2 | A    |
|    5 |    6 | D    |
+------+------+------+

Snowflake Set Operator Execution Order

In the SQL queries, INTERSECT operator takes higher precedence than the other set operations. UNION and EXCEPT/MINUS have the same precedence order. But, you can use parenthesis to override default precedence.