Relationships and JOINs
UNION, INTERSECT and EXCEPT
Combine row sets instead of joining columns.
Understand the concept
UNION combines result rows and removes duplicates; UNION ALL preserves them and avoids adding deduplication work. Both SELECT statements need the same column count and compatible types. Output names come from the first SELECT.
INTERSECT returns common rows; EXCEPT returns rows present on the left but absent on the right, with DISTINCT set semantics. SQL Server treats NULLs as equal for these set comparisons. UNION is not a substitute for relational JOIN. Apply overall ordering at the end.
Use it to combine sources and compare datasets.
Assuming UNION and UNION ALL preserve the same duplicates.
One topic. Three levels of depth.
SELECT City FROM lab.Customer WHERE CustomerId <= 3
UNION SELECT City FROM lab.Customer WHERE CustomerId > 3;Returns the combined distinct city set.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Combine cities from two customer groups while preserving duplicates.
Show the solution and explanation
SELECT City FROM lab.Customer WHERE CustomerId <= 3 UNION ALL SELECT City FROM lab.Customer WHERE CustomerId > 3;Returns 6 rows; UNION would return 4 cities.