UNION Operations

UNION Operations

What it means

UNION combines the results of two or more SELECT queries into a single result set, stacked vertically. Every query in a UNION must return the same number of columns, in compatible data types. UNION removes duplicate rows across the combined results by default; UNION ALL keeps every row, including duplicates, and is faster since it skips the deduplication step.

Examples

-- Combine active and archived customers into one result
SELECT Name, Email FROM Customers
UNION
SELECT Name, Email FROM ArchivedCustomers;

-- UNION ALL keeps duplicates, and is faster (no dedup work)
SELECT Name, Email FROM Customers
UNION ALL
SELECT Name, Email FROM ArchivedCustomers;

-- Column names in the output come from the FIRST query
SELECT Name AS ContactName, Email FROM Customers
UNION
SELECT Name, Email FROM Suppliers;   -- still shows as "ContactName" in results

-- Adding a literal to distinguish the source of each row
SELECT Name, 'Customer' AS SourceType FROM Customers
UNION ALL
SELECT Name, 'Supplier' AS SourceType FROM Suppliers;

Common mistake

Using UNION when you actually want UNION ALL (or vice versa) — UNION’s automatic deduplication requires SQL Server to sort/compare every row across both result sets, which is noticeably slower on large data sets and unnecessary if you already know there are no duplicates (or don’t care about them).

-- Unnecessarily slow if duplicates are impossible or don't matter
SELECT Name FROM Customers
UNION
SELECT Name FROM Suppliers;

-- Faster when you don't need deduplication
SELECT Name FROM Customers
UNION ALL
SELECT Name FROM Suppliers;

Quick practice

1. What’s the difference between UNION and UNION ALL?

   Answer

   UNION removes duplicate rows from the combined result; UNION ALL keeps every row, including duplicates, and is faster since it skips deduplication.

2. What must be true about the SELECT queries combined with UNION?

   Answer

   They must return the same number of columns, in a compatible order and compatible data types.

3. Which query’s column names appear in a UNION’s combined result set?

   Answer

   The first query's column names (or aliases) — later queries' column names are ignored for output labeling purposes.