UNION, INTERSECT, and EXCEPT

Description

The UNION, INTERSECT, and EXCEPT set operators are used to compare and merge the results of two separate query expressions. (Redshift SQL Language Reference Set Operators)

Set operators are fully supported in Snowflake.

Grammar Syntax

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

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT id, name, manager_id
INTO some_employees
FROM
employee
WHERE manager_id = 101

UNION

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 102

UNION ALL

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 101

INTERSECT

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 103

EXCEPT

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 104;

Results

ID	NAME	MANAGER_ID
103	Kwaku	101
110	Liu	101
102	Jorge	101
106	Mateo	102
201	Sofía	102

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TABLE IF NOT EXISTS some_employees AS
SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 101

UNION

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 102

UNION ALL

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 101

INTERSECT

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 103

EXCEPT

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 104;

Results

ID	NAME	MANAGER_ID
102	Jorge	101
103	Kwaku	101
110	Liu	101
106	Mateo	102
201	Sofía	102

Known Issues

There are no known issues.

Related EWIs.

There are no related EWIs.