UNION, INTERSECT and EXCEPT

Joins put columns side by side. Set operators work on whole rows instead: they take two queries with the same columns and combine their results top to bottom.

-- A: products ordered Jan–Mar
SELECT p.ProductId, p.Name FROM …
UNION
-- B: products ordered Apr–Jun
SELECT p.ProductId, p.Name FROM …
ORDER BY ProductId;

UNION and UNION ALL

1SELECT City FROM dbo.Customers WHERE CustomerId <= 42UNION3SELECT City FROM dbo.Customers WHERE CustomerId >= 54ORDER BY City;5 6SELECT City FROM dbo.Customers WHERE CustomerId <= 47UNION ALL8SELECT City FROM dbo.Customers WHERE CustomerId >= 59ORDER BY City;

City
---------
NULL
Bradford
Harrogate
Leeds
York

City
---------
NULL
Bradford
Harrogate
Leeds
Leeds
Leeds
York
York

UNION removes duplicates across and within the two results: Leeds appears three times in the input but once in the output. UNION ALL just stacks them. Removing duplicates means sorting or hashing every row, so use UNION ALL when duplicates cannot happen or do not matter.

The rules

• Both queries return the same number of columns, matched by position, not by name.
• Matching columns need compatible types; the result uses the type with the higher precedence.
• Column names come from the first query.
• There is one ORDER BY, at the very end, and it sorts the combined result.

1SELECT CustomerId, FirstName FROM dbo.Customers2UNION3SELECT OrderId FROM dbo.Orders;

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Matching a text column with a number makes SQL Server convert the text to a number (number types have the higher precedence), which fails at run time:

1SELECT FirstName FROM dbo.Customers WHERE CustomerId = 12UNION ALL3SELECT OrderId FROM dbo.Orders WHERE OrderId = 1;

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Asha' to data type int.

UNION ALL is often used to build one list from different sources, with a literal column saying where each row came from. The names and the ORDER BY use the first query’s column names:

1SELECT N'Joined' AS Event, FirstName AS Who, JoinedOn AS OnDate2FROM dbo.Customers3WHERE JoinedOn >= '2024-01-01'4UNION ALL5SELECT N'Ordered', c.FirstName, o.OrderDate6FROM dbo.Orders AS o7JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId8WHERE o.OrderDate < '2024-03-01'9ORDER BY OnDate;

Event    Who    OnDate
-------  -----  ----------
Joined   Grace  2024-01-08
Ordered  Asha   2024-02-03
Ordered  Ben    2024-02-10
Joined   Zoë    2024-02-15

INTERSECT and EXCEPT

Which products sold in the first quarter but not in the second? Each query can be as complex as you like, joins included:

1SELECT p.ProductId, p.Name2FROM dbo.Products AS p3JOIN dbo.OrderItems AS oi ON oi.ProductId = p.ProductId4JOIN dbo.Orders     AS o  ON o.OrderId    = oi.OrderId5WHERE o.OrderDate < '2024-04-01'6EXCEPT7SELECT p.ProductId, p.Name8FROM dbo.Products AS p9JOIN dbo.OrderItems AS oi ON oi.ProductId = p.ProductId10JOIN dbo.Orders     AS o  ON o.OrderId    = oi.OrderId11WHERE o.OrderDate >= '2024-04-01'12ORDER BY ProductId;

ProductId  Name
---------  --------------
        2  Cordless drill
        5  Sand 25 kg

EXCEPT is also the shortest way to find keys with no partner, the same question as the LEFT JOIN … IS NULL pattern from the outer joins lesson:

1SELECT CustomerId FROM dbo.Customers2EXCEPT3SELECT CustomerId FROM dbo.Orders;

CustomerId
----------
         5
         7

Set operators and NULL

In WHERE and ON, NULL = NULL is unknown, so NULLs never match. Set operators are different: they treat two NULLs as the same value. Ella (customer 5) has no city and is in both halves here, so NULL is in the intersection:

1SELECT City FROM dbo.Customers WHERE CustomerId <= 52INTERSECT3SELECT City FROM dbo.Customers WHERE CustomerId >= 54ORDER BY City;

City
-----
NULL
Leeds
York

1SELECT ProductId, Name FROM dbo.Products2EXCEPT3SELECT p.ProductId, p.Name4FROM dbo.Products AS p5JOIN dbo.OrderItems AS oi ON oi.ProductId = p.ProductId6JOIN dbo.Orders     AS o  ON o.OrderId    = oi.OrderId7JOIN dbo.Customers  AS c  ON c.CustomerId = o.CustomerId8WHERE c.City = N'Leeds'9ORDER BY ProductId;

ProductId  Name
---------  -------------------
        4  Cement 25 kg
        5  Sand 25 kg
        6  Timber 2x4 2.4 m
        7  Plywood sheet 18 mm
        9  Wall plugs (100)
       10  Masonry paint 10 L
       11  Paint roller set