UNION, INTERSECT, and EXCEPT

Every join covered so far combines two tables side by side, adding columns, matched row by row. A set operation does something different: it stacks the results of two separate SELECT queries on top of each other, combining rows, not columns, the same way you'd merge two lists into one.

Learning Objectives

• Combine two queries' results with UNION and UNION ALL, and explain the difference.
• Find rows common to two queries with INTERSECT, and rows unique to one with EXCEPT.
• Know what has to match between two queries for a set operation to work at all.

The Rule: Both Queries Need Matching Columns

Before any of these operators make sense, there's one rule to understand: both queries being combined must return the same number of columns, in a compatible order and type. SELECT first_name, salary FROM employees combined with SELECT name FROM departments would fail, two columns on one side, one on the other. Think of it like stacking two spreadsheets, the column headers have to line up.

UNION vs UNION ALL

Picture two separate lists of names, one for Engineering, one for high earners. UNION merges them into a single list with no repeats, like running a duplicate-removal pass after combining. UNION ALL just pastes both lists together, anyone who appears on both source lists appears twice in the result. UNION ALL is also meaningfully faster, since the database skips the extra work of checking for and removing duplicates.

SELECT first_name FROM employees WHERE department = 'Engineering'
UNION
SELECT first_name FROM employees WHERE salary > 90000;

-- If Grace is in Engineering AND earns over 90000,
-- UNION lists her once. UNION ALL would list her twice.

INTERSECT: Only Rows in Both

INTERSECT keeps only the rows that show up in both queries' results, the overlap between the two lists. This is a genuinely useful operator that's easy to miss, since the same result can always be rewritten as a join or a WHERE ... IN (...) subquery instead, INTERSECT is just the more direct way to say "only what's common to both."

SELECT first_name FROM employees WHERE department = 'Engineering'
INTERSECT
SELECT first_name FROM employees WHERE salary > 90000;

-- Only employees who are BOTH in Engineering AND earn over 90000

SELECT first_name FROM employees WHERE department = 'Engineering'
INTERSECT
SELECT first_name FROM employees WHERE salary > 90000;

EXCEPT: Only Rows in the First, Not the Second

EXCEPT keeps rows from the first query that don't also appear in the second, subtracting one list from another. Order matters here, unlike UNION and INTERSECT: query A EXCEPT query B is not the same as query B EXCEPT query A.

SELECT first_name FROM employees WHERE department = 'Engineering'
EXCEPT
SELECT first_name FROM employees WHERE salary > 90000;

-- Engineering employees who do NOT earn over 90000

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Default to UNION ALL unless you specifically need duplicates removed, it's faster and avoids silently losing legitimate repeated rows.
• Use INTERSECT or EXCEPT when they directly express the intent, rather than reaching for a more roundabout join or subquery, on databases where they're supported.
• Check your MySQL version before relying on INTERSECT or EXCEPT, both require 8.0.31 or newer.
• Put ORDER BY only once, at the very end of the combined query, not after each individual SELECT.

Interview Questions

Summary

This lesson covered combining two queries' results by stacking rows rather than joining columns: UNION (de-duplicated) and UNION ALL (everything kept), INTERSECT (only rows common to both), and EXCEPT (rows in the first query but not the second), plus MySQL's comparatively recent addition of INTERSECT and EXCEPT support in 8.0.31.

What's Next?

The final lesson in this module covers views, saving a query under a name so it can be queried again like a regular table.