UNION, INTERSECT, and EXCEPT
Description
The
UNION
,INTERSECT
, andEXCEPT
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:
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;
Output Code:
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;
Known Issues
There are no known issues.
Related EWIs.
There are no related EWIs.
Last updated