Union

Snowflake and Snowpark. Has there ever been a more perfect union?

Description

Combines two subqueries into a single one. Databricks SQL supports three types of set operators:

• EXCEPT

• INTERSECT

• UNION

(Databricks SQL Language Reference UNION)

Set operators allow queries to be combined. (Snowflake SQL Language Reference UNION)

Syntax

Databricks

subquery1 { { UNION [ ALL | DISTINCT ] |
              INTERSECT [ ALL | DISTINCT ] |
              EXCEPT [ ALL | DISTINCT ] } subquery2 } [...] }

Snowflake

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

Sample Source Patterns

Setup data

Databricks

Query

CREATE TEMPORARY VIEW number1(c) AS VALUES (3), (1), (2), (2), (3), (4);

CREATE TEMPORARY VIEW number2(c) AS VALUES (5), (1), (1), (2);

Snowflake

Query

CREATE TEMPORARY TABLE number1(c int);
INSERT INTO number1 VALUES (3), (1), (2), (2), (3), (4);

CREATE TEMPORARY TABLE number2(c int);
INSERT INTO number2 VALUES (5), (1), (1), (2);

Pattern code

Databricks

Query

-- EXCEPT (MINUS) Operator:
SELECT c FROM number1 EXCEPT SELECT c FROM number2;

SELECT c FROM number1 MINUS SELECT c FROM number2;

-- EXCEPT ALL (MINUS ALL) Operator:
SELECT c FROM number1 EXCEPT ALL (SELECT c FROM number2);

SELECT c FROM number1 MINUS ALL (SELECT c FROM number2);

-- INTERSECT Operator:
(SELECT c FROM number1) INTERSECT (SELECT c FROM number2);

-- INTERSECT DISTINCT Operator:
(SELECT c FROM number1) INTERSECT DISTINCT (SELECT c FROM number2);

-- INTERSECT ALL Operator:
(SELECT c FROM number1) INTERSECT ALL (SELECT c FROM number2);

-- UNION Operator:
(SELECT c FROM number1) UNION (SELECT c FROM number2);

-- UNION DISTINCT Operator:
(SELECT c FROM number1) UNION DISTINCT (SELECT c FROM number2);

-- UNION ALL Operator:
SELECT c FROM number1 UNION ALL (SELECT c FROM number2);

Result

EXCEPT (MINUS) Operator:

c
3
4

EXCEPT ALL (MINUS ALL) Operator:

c
3
3
4

INTERSECT Operator:

c
1
2

INTERSECT DISTINCT Operator:

c
1
2

INTERSECT ALL Operator:

c
1
2
2

UNION Operator:

c
1
3
5
4
2

UNION DISTINCT Operator:

c
1
3
5
4
2

UNION ALL Operator:

c
3
1
2
2
3
4
5
1
1
2

Snowflake

Query

-- EXCEPT (MINUS) Operator
SELECT c FROM number1 EXCEPT SELECT c FROM number2;

SELECT c FROM number1 MINUS SELECT c FROM number2;

-- EXCEPT ALL (MINUS ALL) Operator:
SELECT number1.c FROM number1 
LEFT JOIN number2 
    ON number1.c = number2.c
WHERE number2.c IS NULL;
-- ** MSC-WARMING - MSC-S000# - EXCEPT ALL IS TRANSFORMED TO A LEFT JOIN. **

SELECT number1.c FROM number1 
LEFT JOIN number2 
    ON number1.c = number2.c
WHERE number2.c IS NULL;
-- ** MSC-WARMING - MSC-S000# - MINUS ALL IS TRANSFORMED TO A LEFT JOIN. **

-- INTERSECT Operator:
(SELECT c FROM number1) INTERSECT (SELECT c FROM number2);

-- INTERSECT DISTINCT Operator:
(SELECT c FROM number1) INTERSECT (SELECT c FROM number2);

-- INTERSECT ALL Operator:
SELECT DISTINCT number1.c FROM number1 
INNER JOIN number2 
    ON number1.c = number2.c;
-- ** MSC-WARMING - MSC-S000# - INTERSECT ALL IS TRANSFORMED TO A INNER JOIN. **

-- UNION Operator:
(SELECT c FROM number1) UNION (SELECT c FROM number2);

-- UNION DISTINCT Operator:
(SELECT c FROM number1) UNION DISTINCT (SELECT c FROM number2);

-- UNION ALL Operator:
SELECT c FROM number1 UNION ALL (SELECT c FROM number2);

Result

EXCEPT (MINUS) Operator:

c
3
4

EXCEPT ALL (MINUS ALL) Operator:

c
3
3
4

INTERSECT Operator:

c
1
2

INTERSECT DISTINCT Operator:

c
1
2

INTERSECT ALL Operator:

c
1
2
2

UNION Operator:

c
1
3
5
4
2

UNION DISTINCT Operator:

c
1
3
5
4
2

UNION ALL Operator:

c
3
1
2
2
3
4
5
1
1
2

Known Issues

No related EWIs

Related EWIs

• MSC-S000#: SET OPERATOR WITH ALL IS TRANSFORMED TO A JOIN.