Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 27 of 49

UNION, INTERSECT and EXCEPT

Stack query results with UNION and UNION ALL, find rows two queries share with INTERSECT, and find rows only one has with EXCEPT, with verified examples.

15 minCombining tablesUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonCombine the results of two queries with UNION, INTERSECT and EXCEPT, and choose between UNION and UNION ALL.

After this lesson you can stack the results of two queries into one list, find what two lists have in common, and find what is in one list but not the other.

A join widens a result by adding columns from another table. These operators work the other way: they put the rows of one query on top of the rows of another. They are called set operators, because they treat each result as a set of rows.

UNION and UNION ALL

Suppose you need a mailing list of two groups: the Engineering employees earning at least 100000, and everyone hired from 2024 onwards. Each group is one simple query. UNION joins them:

sql
SELECT id, first_name, 'senior engineering' AS reason
FROM employees
WHERE department_id = 1 AND salary >= 100000
UNION
SELECT id, first_name, 'hired since 2024'
FROM employees
WHERE hire_date >= '2024-01-01'
ORDER BY id;
result
+----+------------+--------------------+
| id | first_name |       reason       |
+----+------------+--------------------+
|  1 | Meera      | senior engineering |
|  2 | Arjun      | senior engineering |
|  3 | Kavya      | senior engineering |
| 18 | Ravi       | hired since 2024   |
| 19 | Diya       | hired since 2024   |
| 20 | Aman       | hired since 2024   |
+----+------------+--------------------+

The result has three columns, named by the first query. The second query supplies its own text for the third column. Rows come out in whatever order the database likes unless you add an ORDER BY at the very end.

Duplicates: UNION versus UNION ALL

UNION removes duplicate rows from the combined result. UNION ALL does not. To see the difference, ask for the department ids from two overlapping queries and count what comes back:

sql
SELECT COUNT(*) AS with_union FROM (
  SELECT department_id FROM employees WHERE salary >= 100000
  UNION
  SELECT department_id FROM employees WHERE job_title LIKE '%Director%'
) AS t;

SELECT COUNT(*) AS with_union_all FROM (
  SELECT department_id FROM employees WHERE salary >= 100000
  UNION ALL
  SELECT department_id FROM employees WHERE job_title LIKE '%Director%'
) AS t;
result
+------------+
| with_union |
+------------+
|          4 |
+------------+
+----------------+
| with_union_all |
+----------------+
|              8 |
+----------------+

Each query returns the department of every matching employee: the first gives six rows (three from Engineering and one each from Sales, Marketing and Finance) and the second gives two (Engineering and Sales). UNION ALL just appends, so it returns all 8 rows. UNION also removes repeats and returns only the 4 distinct departments, so it has to sort or hash the combined rows to find them, which costs time. If you know the two parts cannot overlap, or you want the repeats, use UNION ALL. Use plain UNION when you need a list of distinct values.

Note that duplicates are compared across all selected columns, and two NULLs count as the same value for this purpose.

Rules for the columns

The two queries must agree on shape:

  • The same number of columns.
  • Compatible types in each position: numbers with numbers, text with text.
  • Columns are matched by position, not by name. The first column of query one lines up with the first column of query two, whatever they are called.
  • The result takes its column names from the first query.

A mismatch is an error, and the message wording varies by database. The next statement has two columns on one side and three on the other, so it fails:

sql
SELECT id, first_name FROM employees
UNION
SELECT id, first_name, last_name FROM employees;

Matching by position is a classic way to produce silently wrong data. If one query selects (first_name, last_name) and the other (last_name, first_name), the database happily stacks them without complaint.

INTERSECT: rows in both

INTERSECT returns only the rows that appear in both results. Which employees are both managers of someone and assigned to a project?

sql
SELECT manager_id AS id FROM employees WHERE manager_id IS NOT NULL
INTERSECT
SELECT employee_id FROM assignments
ORDER BY id;
result
+----+
| id |
+----+
|  2 |
|  3 |
|  6 |
|  7 |
| 10 |
| 13 |
+----+

The first part lists the ids that appear as someone's manager. The second lists ids with an assignment. Only ids in both lists survive: Arjun (2), Kavya (3), Vikram (6), Neha (7), Dev (10) and Lata (13). Those are the managers who also work on a project. Meera (1) and Pooja (15) manage people but have no assignment, so they are missing.

EXCEPT: rows in the first but not the second

EXCEPT returns rows from the first query that are not in the second. The order matters, because A EXCEPT B is not the same as B EXCEPT A. To list the employees who have no assignments, remember that both sides of EXCEPT must have the same columns, so the simplest form compares ids only. You can look the names up afterwards with a join:

sql
SELECT id FROM employees
EXCEPT
SELECT employee_id FROM assignments
ORDER BY id;
result
+----+
| id |
+----+
|  1 |
| 15 |
| 16 |
| 17 |
| 18 |
+----+

These are the same five people you found with a LEFT JOIN ... IS NULL earlier. Several roads lead to the same answer, and EXCEPT is often the one that reads most clearly when the two sides are simple lists. Like UNION, EXCEPT and INTERSECT remove duplicates from their result.

Combining three or more queries

You can chain operators. Evaluation goes from top to bottom unless parentheses say otherwise, and in the SQL standard INTERSECT binds more tightly than UNION and EXCEPT. When you mix operators, add parentheses to make the order unmistakable, and test with a small example.

A common real use is a single list from several sources, for example all the city names the company works with. UNION removes the repetition:

sql
SELECT city FROM departments
UNION
SELECT 'Hyderabad'
ORDER BY city;
result
+-----------+
|   city    |
+-----------+
| Bengaluru |
| Delhi     |
| Hyderabad |
| Mumbai    |
| Pune      |
+-----------+

The second query has no FROM, only a constant. That is allowed in MySQL, PostgreSQL, SQLite and SQL Server, but Oracle traditionally needs FROM dual.

Differences between databases

  • UNION and UNION ALL work everywhere.
  • INTERSECT and EXCEPT work in PostgreSQL, SQL Server and SQLite. MySQL added them in version 8.0.31, so older MySQL needs a join or IN instead.
  • Oracle spells EXCEPT as MINUS.
  • INTERSECT ALL and EXCEPT ALL exist in the standard, but not every database supports them. PostgreSQL does.

For UNION in MySQL, see UNION and UNION ALL in MySQL.

Common mistakes

  • Using UNION when UNION ALL is meant. You pay for duplicate removal and may lose genuine repeated rows.
  • Columns in a different order. They are matched by position, so the data ends up under the wrong heading.
  • Adding ORDER BY to each part. The ordering goes once at the end, and refers to the first query's column names.
  • Reversing EXCEPT. A EXCEPT B and B EXCEPT A answer different questions.
  • Expecting INTERSECT and EXCEPT on old MySQL. They need 8.0.31 or later.

Exercise

Make one list of the ids of employees who are either in department 3 or on project 3, without duplicates. Then list the ids of employees in department 1 who are NOT assigned to any project.

Show one possible answer
sql
SELECT id FROM employees WHERE department_id = 3
UNION
SELECT employee_id FROM assignments WHERE project_id = 3
ORDER BY id;

SELECT id FROM employees WHERE department_id = 1
EXCEPT
SELECT employee_id FROM assignments
ORDER BY id;
result
+----+
| id |
+----+
| 10 |
| 11 |
| 12 |
+----+
+----+
| id |
+----+
|  1 |
+----+

Key points

  • UNION stacks the rows of two queries and removes duplicates. UNION ALL stacks them and keeps every row, and is faster.
  • INTERSECT returns rows found in both queries. EXCEPT returns rows from the first query that are not in the second.
  • Both queries must return the same number of columns, with compatible types, matched by position. Column names come from the first query.
  • ORDER BY goes once, at the very end, and refers to the column names of the first query.
  • Joins put tables side by side. These operators put result sets on top of each other.

Check yourself

1. What is the difference between UNION and UNION ALL?
2. Which operator returns the employees who are NOT assigned to any project, given a list of all employee ids and a list of assigned ids?
3. Two queries in a UNION return 2 and 3 columns. What happens?
4. Where does ORDER BY go in a UNION of two SELECT statements?