SQL Set Operations with UNION, INTERSECT, and EXCEPT

Joins combine columns from different tables side by side. But sometimes you need to combine rows from different queries stacked on top of each other. Maybe you want a unified list of all contacts from both a customers table and a suppliers table, or you need to find email addresses that appear in one table but not another. This is where SQL set operations come in.

SQL UNION, INTERSECT, and EXCEPT are set operators that let you combine the result sets of two or more SELECT statements vertically, treating each query's output as a set of rows and applying set theory logic to merge them. This guide explains each operator in detail, walks through the critical difference between UNION and UNION ALL, covers the rules your queries must follow, and provides practical examples with full outputs so you can start using set operations immediately.

What Are Set Operations?

In mathematics, set operations combine groups of items:

• Union produces all items from both sets.
• Intersection produces only items found in both sets.
• Difference produces items in one set but not the other.

SQL applies these same concepts to query result sets. Each SELECT statement produces a set of rows, and the set operator determines how those row sets are combined.

Query A result:    Query B result:
┌───────────┐      ┌───────────┐
│  Alice    │      │  Carol    │
│  Bob      │      │  Alice    │
│  Carol    │      │  Dave     │
└───────────┘      └───────────┘

UNION:      Alice, Bob, Carol, Dave                (all unique)
UNION ALL:  Alice, Bob, Carol, Carol, Alice, Dave  (everything, duplicates kept)
INTERSECT:  Alice, Carol                           (in both)
EXCEPT:     Bob                                    (in A but not B)

The Sample Schema

All examples use these tables:

CREATE TABLE customers (
    id    INT PRIMARY KEY,
    name  VARCHAR(100),
    email VARCHAR(100),
    city  VARCHAR(50)
);

CREATE TABLE suppliers (
    id    INT PRIMARY KEY,
    name  VARCHAR(100),
    email VARCHAR(100),
    city  VARCHAR(50)
);

CREATE TABLE employees (
    id    INT PRIMARY KEY,
    name  VARCHAR(100),
    email VARCHAR(100),
    city  VARCHAR(50)
);

CREATE TABLE current_products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100),
    price        DECIMAL(10,2)
);

CREATE TABLE archived_products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100),
    price        DECIMAL(10,2)
);

-- customers
INSERT INTO customers VALUES (1, 'Alice Martin',  'alice@example.com',  'New York');
INSERT INTO customers VALUES (2, 'Bob Jones',     'bob@example.com',    'Chicago');
INSERT INTO customers VALUES (3, 'Carol Smith',   'carol@example.com',  'Denver');
INSERT INTO customers VALUES (4, 'Dave Wilson',   'dave@example.com',   'New York');

-- suppliers
INSERT INTO suppliers VALUES (1, 'Acme Parts',    'contact@acme.com',   'Chicago');
INSERT INTO suppliers VALUES (2, 'Beta Supply',   'info@betasupply.com','Denver');
INSERT INTO suppliers VALUES (3, 'Carol Smith',   'carol@example.com',  'Denver');

-- employees
INSERT INTO employees VALUES (1, 'Alice Martin',  'alice@corp.com',     'New York');
INSERT INTO employees VALUES (2, 'Eve Turner',    'eve@corp.com',       'Boston');
INSERT INTO employees VALUES (3, 'Frank Lee',     'frank@corp.com',     'Chicago');

-- current_products
INSERT INTO current_products VALUES (101, 'Wireless Mouse',      25.99);
INSERT INTO current_products VALUES (102, 'USB-C Cable',          9.99);
INSERT INTO current_products VALUES (103, 'Mechanical Keyboard', 89.99);
INSERT INTO current_products VALUES (104, 'Desk Lamp',           34.50);

-- archived_products
INSERT INTO archived_products VALUES (102, 'USB-C Cable',          9.99);
INSERT INTO archived_products VALUES (105, 'Wired Mouse',         12.99);
INSERT INTO archived_products VALUES (106, 'VGA Cable',            7.50);
INSERT INTO archived_products VALUES (103, 'Mechanical Keyboard', 79.99);

UNION: Combining Rows from Multiple Queries

The SQL UNION operator takes the results of two SELECT statements and merges them into a single result set, removing duplicate rows in the process.

Basic Syntax

SELECT columns FROM table_a
UNION
SELECT columns FROM table_b;

Example: A Unified Contact List

Suppose you need a single list of all contact names and emails from both customers and suppliers:

SELECT name, email
FROM customers
UNION
SELECT name, email
FROM suppliers;

Output:

Notice that Carol Smith appears in both tables with the same name and email, but she shows up only once in the result. UNION automatically eliminates duplicate rows.

UNION Across Three or More Queries

You can chain multiple UNION operators:

SELECT name, email, 'Customer' AS source FROM customers
UNION
SELECT name, email, 'Supplier' AS source FROM suppliers
UNION
SELECT name, email, 'Employee' AS source FROM employees;

Output:

Notice something important: Alice Martin now appears twice (once as a Customer, once as an Employee) because the source column has a different value, making the full rows distinct. Carol Smith also appears twice for the same reason. The UNION deduplication looks at the entire row, not individual columns.

UNION ALL: Keep Every Row, Including Duplicates

While UNION removes duplicates, UNION ALL keeps every row from every query, even if identical rows appear multiple times.

Syntax

SELECT columns FROM table_a
UNION ALL
SELECT columns FROM table_b;

Example: Compare UNION vs UNION ALL

-- UNION: removes the duplicate Carol Smith row
SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;
-- Result: 6 rows

UNION Output:

-- UNION ALL: keeps everything
SELECT name, email FROM customers
UNION ALL
SELECT name, email FROM suppliers;
-- Result: 7 rows

UNION ALL Output:

Carol Smith appears twice because UNION ALL does not check for or remove duplicates.

When to Use UNION ALL Instead of UNION

UNION ALL should be your default choice unless you specifically need deduplication. Here is why:

Practical Example: Combining Logs

A common real-world use of UNION ALL is combining event logs from partitioned or archived tables:

SELECT order_id, order_date, total_amount, 'current' AS period
FROM orders_2024
UNION ALL
SELECT order_id, order_date, total_amount, 'archive' AS period
FROM orders_2023
UNION ALL
SELECT order_id, order_date, total_amount, 'archive' AS period
FROM orders_2022
ORDER BY order_date DESC;

Since order IDs are unique across years, duplicates are impossible, making UNION ALL the correct and efficient choice.

INTERSECT: Rows Present in Both Queries

INTERSECT returns only rows that appear in the result sets of both queries. It is the SQL equivalent of a mathematical set intersection.

Syntax

SELECT columns FROM table_a
INTERSECT
SELECT columns FROM table_b;

Example: Contacts Who Are Both Customers and Suppliers

SELECT name, email
FROM customers
INTERSECT
SELECT name, email
FROM suppliers;

Output:

Only Carol Smith has identical name and email values in both tables.

Example: Products That Exist in Both Current and Archived Catalogs

SELECT product_id, product_name
FROM current_products
INTERSECT
SELECT product_id, product_name
FROM archived_products;

Output:

Including price changes the result:

SELECT product_id, product_name, price
FROM current_products
INTERSECT
SELECT product_id, product_name, price
FROM archived_products;

Now only the USB-C Cable matches, because its price is 9.99 in both tables.

Example: Cities Where We Have Both Customers and Suppliers

SELECT city FROM customers
INTERSECT
SELECT city FROM suppliers;

Both Chicago and Denver appear in the customers and suppliers tables.

EXCEPT: Rows in One Query but Not the Other

EXCEPT returns rows from the first query that do not appear in the second query. It is the SQL equivalent of set difference (sometimes called set subtraction).

Syntax

SELECT columns FROM table_a
EXCEPT
SELECT columns FROM table_b;

Example: Customers Who Are Not Suppliers

SELECT name, email
FROM customers
EXCEPT
SELECT name, email
FROM suppliers;

Output:

Carol Smith is excluded because her name and email match a row in the suppliers table.

Order Matters with EXCEPT

Unlike UNION and INTERSECT, the order of the two queries in EXCEPT changes the result. A minus B is not the same as B minus A.

Suppliers who are not customers:

SELECT name, email
FROM suppliers
EXCEPT
SELECT name, email
FROM customers;

Completely different result from the previous query because the roles are reversed.

Example: Products Only in the Current Catalog

SELECT product_id, product_name
FROM current_products
EXCEPT
SELECT product_id, product_name
FROM archived_products;

These products exist only in the current catalog and were never archived.

Example: Products Only in the Archive (Discontinued)

SELECT product_id, product_name
FROM archived_products
EXCEPT
SELECT product_id, product_name
FROM current_products;

These products were archived and no longer appear in the current catalog.

Rules: Column Count and Data Type Matching

All set operations follow strict rules about the structure of the queries being combined. Violating these rules produces an error.

Rule 1: Same Number of Columns

Every SELECT in the set operation must return the same number of columns.

Wrong (different column counts):

SELECT name, email, city
FROM customers
UNION
SELECT name, email
FROM suppliers;

ERROR: each UNION query must have the same number of columns

Correct (add a placeholder or remove the extra column):

-- Option A: Add a placeholder column
SELECT name, email, city
FROM customers
UNION
SELECT name, email, NULL AS city
FROM suppliers;

-- Option B: Remove the extra column
SELECT name, email
FROM customers
UNION
SELECT name, email
FROM suppliers;

Rule 2: Compatible Data Types

Corresponding columns (by position) must have compatible data types. You cannot combine a text column with a numeric column in the same position.

Wrong (incompatible types):

SELECT name, email
FROM customers
UNION
SELECT id, email        -- "id" is INT, "name" is VARCHAR
FROM suppliers;

Some databases will raise an error. Others may perform implicit type casting, producing unexpected results. Neither outcome is desirable.

Correct (match the types):

SELECT name, email
FROM customers
UNION
SELECT name, email
FROM suppliers;

Rule 3: Column Names Come from the First Query

The column names in the final result set are determined by the first SELECT statement. The column names in subsequent queries are ignored.

SELECT name AS contact_name, email AS contact_email
FROM customers
UNION
SELECT name AS supplier_name, email AS supplier_email  -- These aliases are ignored
FROM suppliers;

The result columns will be named contact_name and contact_email, not supplier_name and supplier_email.

Rule 4: ORDER BY Applies to the Final Result

You can only use ORDER BY once, at the very end, and it applies to the combined result set.

Wrong (ORDER BY inside individual queries):

SELECT name, email FROM customers ORDER BY name   -- ERROR
UNION
SELECT name, email FROM suppliers ORDER BY name;  -- ERROR

ERROR: ORDER BY clause is not allowed in subqueries used with UNION

Correct (ORDER BY at the end):

SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers
ORDER BY name;

Output:

The ORDER BY sorts the entire combined result, not just one of the individual queries.

Combining Set Operations

You can chain multiple set operations in a single query. The operations are evaluated left to right unless you use parentheses to control precedence.

-- Cities that have customers but neither suppliers nor employees
SELECT city FROM customers
EXCEPT
SELECT city FROM suppliers
EXCEPT
SELECT city FROM employees;

Step-by-step evaluation:

1. Customer cities: {New York, Chicago, Denver}
2. After first EXCEPT (remove supplier cities {Chicago, Denver}): {New York}
3. After second EXCEPT (remove employee cities {New York, Boston, Chicago}): {} (empty)

Output: No rows returned.

If you want cities that have customers but are NOT in either suppliers or employees, you can use parentheses to be more explicit (where supported):

SELECT city FROM customers
EXCEPT
(
    SELECT city FROM suppliers
    UNION
    SELECT city FROM employees
);

1. Supplier + Employee cities: {Chicago, Denver, New York, Boston}
2. Customer cities minus combined set: {New York, Chicago, Denver} minus {Chicago, Denver, New York, Boston} = {} (empty)

Still empty in this dataset. But the logic is clearer with explicit grouping.

Practical Patterns

Pattern 1: Adding a Source Identifier

When combining rows from multiple tables, it is common to add a literal column that identifies the source:

SELECT name, email, 'Customer' AS type FROM customers
UNION ALL
SELECT name, email, 'Supplier' AS type FROM suppliers
UNION ALL
SELECT name, email, 'Employee' AS type FROM employees
ORDER BY name;

Pattern 2: Finding Missing Records

EXCEPT is perfect for identifying gaps:

-- Find product IDs in the archive that are NOT in the current catalog
SELECT product_id FROM archived_products
EXCEPT
SELECT product_id FROM current_products;

Pattern 3: Validating Data Consistency

Use INTERSECT to verify that expected overlaps exist:

-- Verify which archived products are still active
SELECT product_id, product_name FROM current_products
INTERSECT
SELECT product_id, product_name FROM archived_products;

Pattern 4: UNION with Aggregation

You can wrap a UNION in a subquery or CTE and then aggregate the combined result:

SELECT city, COUNT(*) AS total_contacts
FROM (
    SELECT name, city FROM customers
    UNION ALL
    SELECT name, city FROM suppliers
    UNION ALL
    SELECT name, city FROM employees
) AS all_contacts
GROUP BY city
ORDER BY total_contacts DESC;

This pattern is powerful for reporting across multiple data sources.

Common Mistakes

Mistake 1: Using UNION When UNION ALL Is Correct

If you know your data has no duplicates or you intentionally want duplicates preserved, using UNION wastes resources on unnecessary deduplication.

Inefficient:

-- order_id is unique in each table, so no duplicates are possible
SELECT order_id, order_date FROM orders_2024
UNION                                           -- Unnecessary dedup
SELECT order_id, order_date FROM orders_2023;

Efficient:

SELECT order_id, order_date FROM orders_2024
UNION ALL                                       -- Skip dedup
SELECT order_id, order_date FROM orders_2023;

Mistake 2: Mismatched Column Order

The column position determines how values are combined, not the column name. This subtle point can cause logical errors without any syntax error.

Wrong (columns in different order):

SELECT name, email FROM customers
UNION
SELECT email, name FROM suppliers;    -- email and name are swapped!

Output (logically wrong):

The query runs without errors, but supplier emails appear in the name column and vice versa. The database matched columns by position, not by name.

Correct:

SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;

Mistake 3: ORDER BY Referencing Column from the Wrong Query

When using ORDER BY with set operations, you must reference column names or positions from the first SELECT.

Wrong (alias only exists in second query):

SELECT name, email FROM customers
UNION
SELECT name AS supplier_name, email FROM suppliers
ORDER BY supplier_name;    -- ERROR: "supplier_name" is not recognized

Correct:

SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers
ORDER BY name;             -- Uses first query's column name

Or use positional notation:

SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers
ORDER BY 1;               -- First column

Mistake 4: Expecting EXCEPT to Work Like a Join Filter

EXCEPT compares entire rows, not individual columns. If you want to exclude rows based on a single column match while keeping other columns, use a LEFT JOIN ... WHERE IS NULL or NOT EXISTS pattern instead.

EXCEPT filters on all columns (may not be what you want):

SELECT product_id, product_name, price FROM current_products
EXCEPT
SELECT product_id, product_name, price FROM archived_products;

This excludes a row only if product_id, product_name, and price all match. The Mechanical Keyboard (same ID and name but different price) will appear in the result because the full rows are not identical.

If you want to exclude by product_id only, use NOT EXISTS:

SELECT product_id, product_name, price
FROM current_products AS cp
WHERE NOT EXISTS (
    SELECT 1 FROM archived_products AS ap
    WHERE ap.product_id = cp.product_id
);

Database Compatibility

Performance Considerations

• UNION ALL is always faster than UNION because it skips the deduplication step. Use it whenever duplicates are either impossible or acceptable.
• UNION, INTERSECT, and EXCEPT all require the database to compare rows for uniqueness. This typically involves sorting or hashing the combined result set, which consumes CPU and memory proportional to the total number of rows.
• Indexes do not help the set operation itself. They help the individual SELECT queries produce their results faster, but the merge/dedup step operates on the final result sets.
• If you need to filter the combined result, wrap the set operation in a subquery or CTE and apply WHERE on the outside. This prevents the database from doing unnecessary work.

-- Filter after combining
SELECT *
FROM (
    SELECT name, email, city FROM customers
    UNION ALL
    SELECT name, email, city FROM suppliers
) AS all_contacts
WHERE city = 'Chicago';

Quick Reference

Rules checklist:

• Same number of columns in every SELECT statement.
• Compatible data types in corresponding column positions.
• Column names come from the first SELECT.
• ORDER BY goes at the end and applies to the final result.
• EXCEPT is order-dependent: A minus B differs from B minus A.

Summary

SQL UNION and its related set operators give you the ability to combine row sets from multiple queries into a single result. UNION merges results and removes duplicates. UNION ALL merges results and keeps everything, making it the faster choice when deduplication is unnecessary. INTERSECT finds rows common to both queries. EXCEPT (or MINUS in Oracle) finds rows present in the first query but absent from the second.

Key takeaways:

• Default to UNION ALL and switch to UNION only when you explicitly need duplicate removal.
• All queries in a set operation must have the same number of columns with compatible data types in matching positions.
• Column matching is positional, not name-based. Always verify that the column order is consistent across all SELECT statements.
• ORDER BY can only appear once, at the end of the entire set operation.
• EXCEPT is order-sensitive. Swapping the two queries produces a different result.
• For databases that do not support INTERSECT or EXCEPT, you can simulate them using joins and NOT EXISTS.
• Wrap set operations in subqueries or CTEs when you need to aggregate, filter, or further transform the combined result.

Set operations are a clean, declarative way to solve problems that involve comparing, merging, or differentiating between groups of rows across tables or queries.