Snowpark Migration Accelerator: 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 (https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-setops.html))

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

Syntax

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

Sample Source Patterns

Setup data

Databricks

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

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

Snowflake

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

Pattern code

Databricks

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

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

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

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

Language: English