SQL UNION and Set Operations

Stack the results of several queries with UNION and UNION ALL, and find overlaps and differences with INTERSECT and EXCEPT.

Lesson 14 of 102Intermediate17 min read

A join puts tables side by side, adding columns from one table to rows of another. Sometimes you need the opposite: to put the results of two queries on top of each other, as one longer list.

Perhaps the shop has a customers table and a separate newsletter subscribers table, and marketing wants one list of everyone to contact. Or you want a single "stock alerts" report that combines out-of-stock items, low-stock items and items nobody has ordered, each found by a different query. These are jobs for set operations.

In this lesson you'll combine queries with UNION and UNION ALL, label where each row came from, sort the combined result, and use INTERSECT and EXCEPT to find what two lists have in common and what's in one but not the other.

UNION and UNION ALL

The examples use a small subscribers table alongside customers:

idnamecity
1AnaLisbon
2FarahLeeds
3EmaPorto
4GilFaro
SQL
SELECT city FROM customers
UNION
SELECT city FROM subscribers;
  • UNION stacks the results and removes duplicate rows, so each city appears once.
  • UNION ALL keeps every row, including duplicates.

UNION ALL is faster, because the database doesn't have to look for duplicates. Use it whenever duplicates are impossible or you actually want them, such as when counting.

The rules for combining

Every query in a set operation must return:

  1. The same number of columns.
  2. Columns in the same order, with compatible types: text with text, numbers with numbers.

The column names come from the first query, so put your aliases there. Columns are matched by position, not by name, so SELECT name, city stacked on SELECT city, name happily mixes names and cities together without any error.

Labels and sorting

Add a fixed value as an extra column to show where each row came from:

SQL
SELECT name, 'customer' AS source FROM customers
UNION ALL
SELECT name, 'subscriber' FROM subscribers;

To sort the combined result, write one ORDER BY at the very end. It applies to the whole result, and it can only use column names or aliases from the first query. A WHERE clause, on the other hand, belongs to each individual query.

INTERSECT and EXCEPT

OperationReturns
A UNION BRows in A or B (or both), once each
A INTERSECT BRows in both A and B
A EXCEPT BRows in A that aren't in B

Oracle calls EXCEPT MINUS. PostgreSQL, SQLite and SQL Server support both INTERSECT and EXCEPT; MySQL added them in version 8.0.31. Like UNION, both remove duplicates. The same questions can also be answered with joins or subqueries, but set operations often read more clearly.

SQL UNION Syntax

Set operations

SELECT col1, col2 FROM a
UNION              -- or UNION ALL, INTERSECT, EXCEPT
SELECT col1, col2 FROM b
ORDER BY col1;

SQL UNION Summary

  • UNION stacks query results and removes duplicates; UNION ALL keeps every row and is faster.
  • Each query must have the same number of columns, in the same order, with compatible types.
  • Column names come from the first query; one ORDER BY at the end sorts everything.
  • INTERSECT returns rows found in both queries; EXCEPT returns rows in the first but not the second.
  • A literal column such as 'customer' AS source labels where each row came from.

SQL UNION Examples

Each example shows the code, the output it produces and what each part does.

Example 1

One contact list with sources

SQL
SELECT name, 'customer' AS source FROM customers
UNION ALL
SELECT name, 'subscriber' FROM subscribers
ORDER BY name, source;
Output
name  | source
------+-----------
Ana   | customer
Ana   | subscriber
Ben   | customer
Chloe | customer
Dev   | customer
Ema   | customer
Ema   | subscriber
Farah | subscriber
Gil   | subscriber
Line-by-line explanation
  1. Line 1The alias source is set in the first query and used for the whole result.
  2. Line 2UNION ALL keeps both Ana rows. With UNION they'd still both appear, because the source values differ.
  3. Line 4A single ORDER BY sorts the combined list.
Example 2

Overlaps and differences

SQL
SELECT name FROM customers
INTERSECT
SELECT name FROM subscribers;

SELECT name FROM subscribers
EXCEPT
SELECT name FROM customers;
Output
name
-----
Ana
Ema

name
------
Farah
Gil
Line-by-line explanation
  1. Lines 1 to 3Ana and Ema are both customers and subscribers.
  2. Lines 5 to 7Farah and Gil subscribe but have never become customers, a list marketing would love to have.
Example 3

A stock alerts report

SQL
SELECT name, 'Out of stock' AS alert FROM products WHERE stock = 0
UNION ALL
SELECT name, 'Low stock' FROM products WHERE stock BETWEEN 1 AND 20
UNION ALL
SELECT name, 'Never ordered' FROM products
WHERE id NOT IN (SELECT product_id FROM orders)
ORDER BY name, alert;
Output
name      | alert
----------+--------------
Backpack  | Low stock
Lunch box | Never ordered
Lunch box | Out of stock
Line-by-line explanation
  1. Line 1Each query has its own WHERE clause.
  2. Line 3The backpack has 15 in stock.
  3. Lines 5 to 6The lunch box appears twice because it matches two different alerts.
  4. Line 7Sorting by name, then alert, puts each product's alerts together.

Common SQL UNION Mistakes

Different numbers of columns

Don't
SELECT name, city FROM customers
UNION
SELECT name FROM subscribers;      -- error: different column counts
Do
SELECT name, city FROM customers
UNION
SELECT name, city FROM subscribers;

ORDER BY inside each query

Don't
SELECT name FROM customers ORDER BY name
UNION
SELECT name FROM subscribers ORDER BY name;
Do
SELECT name FROM customers
UNION
SELECT name FROM subscribers
ORDER BY name;

Using UNION when you need every row

Don't
-- counting cities: duplicates removed, so Leeds counts once
SELECT city FROM customers UNION SELECT city FROM subscribers;
Do
SELECT city FROM customers UNION ALL SELECT city FROM subscribers;

SQL UNION in Real Projects

Set operations at work

Analysts use UNION ALL to stack monthly tables such as sales_2023 and sales_2024 into one dataset, and to combine several checks into a single exceptions report like the stock alerts above. EXCEPT is a quick way to compare two versions of a list, for example to see which products disappeared after an import.

SQL UNION FAQ

What is the difference between UNION and JOIN?

UNION stacks the rows of two queries on top of each other, so the result has more rows and the same columns. A JOIN places matching rows side by side, so the result has more columns. Use UNION to combine similar lists, and JOIN to combine related information about the same thing.

Is UNION ALL faster than UNION?

Yes, usually. UNION has to find and remove duplicate rows, which often means sorting or hashing the whole result. UNION ALL just appends the rows. Use UNION ALL whenever duplicates are impossible or should be kept.

Which column names does a UNION result use?

The names come from the first query. Later queries only need the same number of columns in compatible types; their own column names and aliases are ignored. Put any aliases you want to show in the first SELECT.

Does MySQL support INTERSECT and EXCEPT?

MySQL added INTERSECT and EXCEPT in version 8.0.31. Older versions need workarounds: INNER JOIN or IN for an intersection, and LEFT JOIN ... IS NULL or NOT EXISTS for a difference. PostgreSQL, SQLite and SQL Server support both. Oracle traditionally uses MINUS for EXCEPT.

Can I UNION more than two queries?

Yes. Chain as many as you need, query1 UNION ALL query2 UNION ALL query3, as long as every query has the same number of compatible columns. A single ORDER BY at the very end sorts the combined result.

SQL UNION Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the query so every row from both tables is kept, including duplicates.

SELECT city FROM customers
UNION 
SELECT city FROM subscribers;
Multiple choiceeasy

What does UNION do with duplicate rows?

Predict the outputmedium

customers has 5 rows and subscribers has 4. How many rows does SELECT name FROM customers UNION ALL SELECT name FROM subscribers return?

Multiple choicemedium

Which operation returns names that appear in both lists?

Multiple choicehard

Where do the column names of a UNION result come from?

SQL UNION Quiz

5 questions to check what you learned.

  1. What must every query in a UNION have in common?

    • The same table
    • The same WHERE clause
    • The same number of columns, in compatible types and order
    • The same number of rows

  2. Why is UNION ALL usually faster than UNION?

    • It uses an index
    • It skips the second query
    • It doesn't need to check for and remove duplicates
    • It returns fewer rows

  3. What does A EXCEPT B return?

    • Rows in A that aren't in B
    • Rows in both A and B
    • Rows in B that aren't in A
    • All rows except duplicates

  4. How many ORDER BY clauses does a sorted UNION need?

    • One, at the end
    • One per query
    • None, it sorts automatically
    • Two

  5. Will UNION treat "Leeds" and "Leeds " (with a space) as duplicates?

    • Yes
    • No, rows must match exactly
    • Only in MySQL
    • Only with UNION ALL

Related SQL topics

13More JoinsGo beyond INNER and LEFT JOIN: find unmatched rows, join a table to itself, combine everything with FULL OUTER and CROSS JOIN, and avoid the ON versus WHERE trap.15Subqueries & CTEsUse the result of one query inside another with subqueries, IN, EXISTS, derived tables and WITH clauses.02SELECTRead exactly the columns you need, rename them, calculate new values and remove duplicates.
Esc
↑↓ to navigateEnter to open