Set Operators

Same number of columns.

Compatible data types.

Column names come from the first query.

ORDER BY can appear only at the end.

Precedence: INTERSECT binds tighter than UNION and EXCEPT.

Set operators treat NULLs as equal, unlike =.

SELECT City FROM dbo.Customers
UNION                -- removes duplicates (sort/hash cost)
SELECT ShipCity FROM dbo.Orders;

SELECT City FROM dbo.Customers
UNION ALL            -- keeps duplicates (faster)
SELECT ShipCity FROM dbo.Orders;

SELECT CustomerID FROM dbo.Customers
INTERSECT            -- in both
SELECT CustomerID FROM dbo.Orders;

SELECT CustomerID FROM dbo.Customers
EXCEPT               -- in the first but not the second
SELECT CustomerID FROM dbo.Orders;