beginner 6 min read

Combining Query Results with UNION

Written with a little help from AI, reviewed and edited by a human.

Sometimes you need to combine the results of two or more separate queries into a single result set. UNION lets you stack the results of multiple SELECT statements on top of each other, like merging two lists into one.

Real life example

Imagine you’re organizing a school event. You have two separate lists:

  • List 1: Students from Mrs. Smith’s class
  • List 2: Students from Mr. Johnson’s class

You want to create one combined list of all students attending the event. You’d take both lists and merge them together, making sure not to list anyone twice if they appear on both lists.

That’s what UNION does — it combines the results of multiple queries into one list.

Basic UNION

Let’s say you have two tables:

students_class_a

idname
1Alice
2Bob
3Charlie

students_class_b

idname
4Diana
5Eve
6Frank

You want a list of all students from both classes:

SELECT name FROM students_class_a
UNION
SELECT name FROM students_class_b;

Result:

name
Alice
Bob
Charlie
Diana
Eve
Frank

The results from both queries are combined into one.

UNION removes duplicates

If the same value appears in both queries, UNION automatically removes duplicates:

students_class_a

idname
1Alice
2Bob

students_class_b

idname
3Bob
4Diana
SELECT name FROM students_class_a
UNION
SELECT name FROM students_class_b;

Result:

name
Alice
Bob
Diana

Notice Bob only appears once even though he’s in both tables. UNION removes duplicates by default.

UNION ALL — Keep duplicates

If you want to keep duplicates, use UNION ALL:

SELECT name FROM students_class_a
UNION ALL
SELECT name FROM students_class_b;

Result:

name
Alice
Bob
Bob
Diana

Now Bob appears twice.

UNION ALL is faster than UNION because it doesn’t have to check for and remove duplicates. Use it when you know there are no duplicates or when duplicates are okay.

Column requirements

For UNION to work, both queries must have:

  1. The same number of columns
  2. Matching data types in corresponding columns

This works:

SELECT id, name FROM students_class_a
UNION
SELECT id, name FROM students_class_b;

This does NOT work:

SELECT name FROM students_class_a  -- 1 column
UNION
SELECT id, name FROM students_class_b;  -- 2 columns ❌

You’ll get an error because the number of columns doesn’t match.

Column names come from the first query

The result uses the column names from the first query:

SELECT name AS student_name FROM students_class_a
UNION
SELECT name FROM students_class_b;

The result column will be called student_name, not name, even though the second query uses name.

UNION with WHERE

You can filter each query before combining them:

SELECT name FROM students_class_a WHERE name LIKE 'A%'
UNION
SELECT name FROM students_class_b WHERE name LIKE 'A%';

This gets all names starting with “A” from both classes.

UNION with ORDER BY

You can sort the combined results with ORDER BY, but it goes at the very end, after all UNION clauses:

SELECT name FROM students_class_a
UNION
SELECT name FROM students_class_b
ORDER BY name;

Result (sorted alphabetically):

name
Alice
Bob
Charlie
Diana
Eve
Frank

You cannot put ORDER BY after each individual query (before the UNION) — it only goes at the end.

Combining more than two queries

You can UNION as many queries as you want:

SELECT name FROM students_class_a
UNION
SELECT name FROM students_class_b
UNION
SELECT name FROM students_class_c;

All three results are combined into one.

UNION with different tables

You can UNION queries from completely different tables as long as the columns match:

SELECT name, 'student' AS type FROM students
UNION
SELECT name, 'teacher' AS type FROM teachers;

This combines students and teachers into one list with a type column indicating which they are:

nametype
Alicestudent
Bobstudent
Mr. Leeteacher
Ms. Kimteacher

Real world use cases

1. Searching across multiple tables:

SELECT title, 'blog' AS source FROM blog_posts WHERE title LIKE '%SQL%'
UNION
SELECT title, 'news' AS source FROM news_articles WHERE title LIKE '%SQL%';

This searches for “SQL” in both blog posts and news articles.

2. Combining archived and active data:

SELECT * FROM active_orders
UNION
SELECT * FROM archived_orders;

Get all orders, whether active or archived.

3. Creating reports from multiple sources:

SELECT 'January' AS month, SUM(sales) FROM sales_jan
UNION
SELECT 'February', SUM(sales) FROM sales_feb
UNION
SELECT 'March', SUM(sales) FROM sales_mar;

Get total sales for each month.

INTERSECT and EXCEPT (bonus)

Some databases (not all) support two related operators:

INTERSECT — Returns only rows that appear in both queries:

SELECT name FROM students_class_a
INTERSECT
SELECT name FROM students_class_b;

This returns only students who are in both classes.

EXCEPT (or MINUS in some databases) — Returns rows from the first query that are not in the second:

SELECT name FROM students_class_a
EXCEPT
SELECT name FROM students_class_b;

This returns students in class A who are not in class B.

Not all databases support these (SQLite supports INTERSECT and EXCEPT, but MySQL does not).

Performance considerations

  • UNION is slower than UNION ALL because it checks for duplicates
  • If you know there are no duplicates (or duplicates are okay), use UNION ALL
  • UNIONing many large queries can be slow — consider if there’s a better way (like a single query with JOINs)

Summary

  • UNION combines the results of multiple SELECT statements
  • UNION removes duplicates by default
  • UNION ALL keeps duplicates (and is faster)
  • Both queries must have the same number of columns with matching types
  • Column names come from the first query
  • Use ORDER BY at the very end to sort combined results
  • Use UNION when you need to merge data from multiple sources into one result
Share: