Set Operations

Set operations combine the results of two or more SELECT statements vertically (stacking rows), whereas joins combine tables horizontally (adding columns).

Concept

Set operations combine the results of two or more SELECT statements vertically (stacking rows), whereas joins combine tables horizontally (adding columns).

Rows in either query Rows in both queries Removed (INTERSECT ALL keeps, in PostgreSQL) EXCEPT (MINUS in Oracle) Rows in the first but not the second Removed (EXCEPT ALL in PostgreSQL)

A UNION B        A INTERSECT B      A EXCEPT B
 (####(###)####)   (    (###)    )   (####(   )    )

Why It Matters

Combine data from similar tables (current + archived orders, multiple regional databases).

Compare datasets during testing and migration ("which rows differ between source and target?").

Build lists from different sources (all contact emails from customers and suppliers).

Syntax

Same number of columns in each SELECT.

Compatible data types column by column (position-based, not name-based).

Column names in the result come from the first SELECT.

ORDER BY appears once, after the last SELECT, and applies to the combined result.

Precedence: INTERSECT binds tighter than UNION and EXCEPT in standard SQL (PostgreSQL, SQL Server, Db2); Oracle evaluates all set operators left to right with equal precedence. Use parentheses to be explicit.

SELECT col1, col2 FROM table_a
{UNION | UNION ALL | INTERSECT | EXCEPT | MINUS}
SELECT col1, col2 FROM table_b
[ORDER BY col1];    -- one ORDER BY at the very end

Example

Two sets of cities:

7 rows: duplicates (Mumbai, Pune) appear once.

15.2 UNION ALL 9 rows (5 + 4), duplicates kept. UNION ALL is faster because it does not need to sort or hash to remove duplicates. Use it whenever duplicates are impossible or desired.

MySQL supports INTERSECT and EXCEPT from 8.0.31. Alternative: WHERE EXISTS / IN.

15.4 EXCEPT / MINUS Order matters: A EXCEPT B ≠ B EXCEPT A. The reverse gives Bangalore, Delhi.

15.5 Column compatibility and data types 15.6 Duplicate handling and NULL

Set operations treat NULLs as equal for duplicate elimination (unlike =):

15.7 Practical: data reconciliation Compare a source table and a migrated target table:

Both queries returning zero rows means the tables contain identical distinct rows (checks for duplicate counts need EXCEPT ALL or GROUP BY counts).

customers.city: Mumbai, London, Singapore, Pune, Milan
departments.location: Bangalore, Mumbai, Pune, Delhi

SELECT city AS place FROM customers
UNION
SELECT location FROM departments
ORDER BY place;

SELECT city AS place FROM customers
UNION ALL
SELECT location FROM departments;

SELECT city FROM customers
INTERSECT
SELECT location FROM departments
ORDER BY city;

-- PostgreSQL, SQL Server, SQLite, MySQL 8.0.31+, Oracle 21c+
SELECT city FROM customers
EXCEPT
SELECT location FROM departments
ORDER BY city;

-- Oracle (all versions)
SELECT city FROM customers
MINUS
SELECT location FROM departments
ORDER BY city;

-- ERROR: different number of columns
SELECT name, city FROM customers
UNION
SELECT dept_name FROM departments;

-- Works, but meaningless: types are compatible, meanings are not
SELECT customer_id, name FROM customers
UNION
SELECT dept_id, location FROM departments;

-- Good: add a label column and cast explicitly if needed
SELECT 'Customer' AS source, name AS label, city FROM customers
UNION ALL
SELECT 'Department', dept_name, location FROM departments
ORDER BY source, label;

SELECT NULL AS x UNION SELECT NULL;    -- 1 row
SELECT NULL AS x INTERSECT SELECT NULL; -- 1 row

-- Rows in source missing or different in target
SELECT customer_id, name, email FROM customers
EXCEPT
SELECT customer_id, name, email FROM customers_migrated;

-- Rows in target not in source
SELECT customer_id, name, email FROM customers_migrated
EXCEPT
SELECT customer_id, name, email FROM customers;

Expected Result

Cities that host a department but no customer:

SELECT location FROM departments
EXCEPT
SELECT city FROM customers
ORDER BY location;

Common Mistakes

Using UNION when UNION ALL is correct (unnecessary sort, and it silently removes legitimate duplicates such as two identical transactions).

ORDER BY inside each SELECT (not allowed without parentheses/subqueries in most DBMSs).

Mismatched column order (SELECT name, city UNION SELECT city, name).

Relying on column names from the second query.

Using EXCEPT in Oracle before 21c (use MINUS).

Best Practices

Default to UNION ALL; use UNION only when you need de-duplication.

Add a "source" literal column when combining different origins.

Cast columns explicitly when types differ (CAST(x AS VARCHAR(20))).

Use EXCEPT-based checks in data migration and ETL testing.

Interview Relevance

High: "UNION vs UNION ALL" is one of the top 10 SQL interview questions. Also "UNION vs JOIN", "How to find rows in one table but not another" (EXCEPT, NOT EXISTS, LEFT JOIN anti-join).

Practice Questions

Combine the emails of customers and employees into one list without duplicates.

Find product IDs that exist in products but not in order_items using EXCEPT.

Find customer IDs that ordered in both January and March 2026 using INTERSECT.

Explain why SELECT 1 UNION SELECT 1 UNION ALL SELECT 1 returns 2 rows.

Write a MySQL 5.7-compatible alternative to INTERSECT.