SQL set operations combine results from two or more SELECT queries into a single result set. UNION combines all results removing duplicates. UNION ALL combines all results keeping duplicates (faster). INTERSECT returns only rows appearing in both results. EXCEPT (or MINUS in Oracle) returns rows in the first result but not the second. All set operations require compatible result sets — same number of columns, compatible data types. Column names come from the first SELECT.
Real-life analogy: Venn diagrams
Set operations are SQL implementations of Venn diagrams. UNION = everything in circle A OR circle B (no duplicates). UNION ALL = everything in A plus everything in B (with duplicates). INTERSECT = only what is in BOTH A and B. EXCEPT = only what is in A but NOT in B. Each operation is a circle-logic question about your data.
UNION and UNION ALL
UNION vs UNION ALL — removing vs keeping duplicates
-- Setup: Two tables with overlapping employees
-- ActiveEmployees(EmpID, Name, Dept) and ContractEmployees(EmpID, Name, Dept)
-- UNION: combines results, REMOVES duplicates (like DISTINCT)
SELECT EmpID, Name, 'Active' AS Type FROM ActiveEmployees
UNION
SELECT EmpID, Name, 'Contract' AS Type FROM ContractEmployees;
-- If same EmpID+Name+Type appears in both → only one row in result
-- Internally sorts and deduplicates → SLOWER than UNION ALL
-- UNION ALL: combines results, KEEPS ALL duplicates
SELECT EmpID, Name FROM ActiveEmployees
UNION ALL
SELECT EmpID, Name FROM ContractEmployees;
-- No deduplication → FASTER, use when duplicates are fine or impossible
-- Real-world use: combining data from multiple years
SELECT OrderID, CustomerID, Amount, '2023' AS Year FROM Orders_2023
UNION ALL
SELECT OrderID, CustomerID, Amount, '2024' AS Year FROM Orders_2024
UNION ALL
SELECT OrderID, CustomerID, Amount, '2025' AS Year FROM Orders_2025
ORDER BY Year, OrderID;
-- Rules for UNION:
-- 1. Both SELECT must have the same number of columns
-- 2. Corresponding columns must have compatible data types
-- 3. Column names come from the FIRST SELECT
-- 4. ORDER BY goes at the END of the last SELECT
-- UNION with different column counts or types → ERROR:
SELECT EmpID, Name FROM Employee -- 2 columns
UNION
SELECT EmpID, Name, Salary FROM Employee; -- 3 columns → ERROR!
-- Fix mismatched columns with NULL placeholders:
SELECT EmpID, Name, NULL AS Salary FROM Employee_Archive
UNION ALL
SELECT EmpID, Name, Salary FROM Employee_Current;INTERSECT and EXCEPT
INTERSECT and EXCEPT with practical use cases
-- INTERSECT: rows that appear in BOTH result sets
-- Find employees who are in BOTH the active list AND the payroll list
SELECT EmpID FROM ActiveEmployees
INTERSECT
SELECT EmpID FROM PayrollEmployees;
-- Only EmpIDs that exist in both tables
-- Practical: find customers who bought in BOTH 2023 AND 2024
SELECT CustomerID FROM Orders WHERE YEAR(OrderDate) = 2023
INTERSECT
SELECT CustomerID FROM Orders WHERE YEAR(OrderDate) = 2024;
-- EXCEPT (PostgreSQL/SQL Server) / MINUS (Oracle):
-- Rows in first result that are NOT in the second result
-- Find employees on payroll but NOT in the active directory (anomaly detection)
SELECT EmpID FROM PayrollEmployees
EXCEPT
SELECT EmpID FROM ActiveEmployees;
-- Find customers who bought last year but NOT this year (churned customers)
SELECT CustomerID FROM Orders WHERE YEAR(OrderDate) = 2023
EXCEPT
SELECT CustomerID FROM Orders WHERE YEAR(OrderDate) = 2024;
-- Alternative to EXCEPT using LEFT JOIN + IS NULL:
SELECT p.EmpID FROM PayrollEmployees p
LEFT JOIN ActiveEmployees a ON p.EmpID = a.EmpID
WHERE a.EmpID IS NULL; -- Payroll records with no matching active directory entry
-- Alternative to INTERSECT using INNER JOIN:
SELECT DISTINCT p.EmpID
FROM PayrollEmployees p
INNER JOIN ActiveEmployees a ON p.EmpID = a.EmpID;| Operation | Returns | Handles duplicates | SQL Server | Oracle | Performance |
|---|---|---|---|---|---|
| UNION | Rows in A OR B (unique) | Removes duplicates | UNION | UNION | Slower (sort + dedup) |
| UNION ALL | All rows from A and B | Keeps duplicates | UNION ALL | UNION ALL | Fastest (no dedup) |
| INTERSECT | Rows in A AND B (unique) | Removes duplicates | INTERSECT | INTERSECT | Medium |
| EXCEPT / MINUS | Rows in A NOT in B (unique) | Removes duplicates | EXCEPT | MINUS | Medium |
UNION ALL is almost always preferred over UNION
UNION requires sorting and deduplicating — O(n log n). UNION ALL is O(n). Unless you genuinely need deduplication (rare), always use UNION ALL. The same applies to EXCEPT ALL and INTERSECT ALL (available in PostgreSQL) — they keep duplicates and are faster than their standard counterparts.
Practice questions
- Table A has 5 rows, Table B has 3 rows (2 identical to rows in A). How many rows does A UNION B return? (Answer: 6 — 5 unique from A + 1 new unique from B. UNION removes the 2 duplicates.)
- Same tables. How many rows does A UNION ALL B return? (Answer: 8 — all 5 from A + all 3 from B, no deduplication.)
- Can you ORDER BY in the middle of a UNION query? (Answer: No — ORDER BY only applies to the final combined result set and must appear at the very end of the last SELECT statement.)
- INTERSECT vs INNER JOIN — what is the key difference? (Answer: INTERSECT works on complete rows (all columns must match), returns distinct rows, and requires same column count/types. INNER JOIN joins on specific column conditions, can return multiple rows per match, and can select specific columns from either table.)
- You have CurrentEmployees and FormerEmployees tables. Write a query to find people who appear in BOTH (boomerang employees). (Answer: SELECT EmpID FROM CurrentEmployees INTERSECT SELECT EmpID FROM FormerEmployees — assuming EmpID identifies the person across both tables.)
On LumiChats
Set operations are commonly used in data pipelines and reporting. LumiChats can help you decide between UNION vs JOIN, write deduplication queries using EXCEPT, and optimise set operation performance for large tables.
Try it free