Skip to content
Tutorials Center
My learning

Topics

Lesson 42 of 125 ยท Subqueries and Set Operations

UNION, INTERSECT and EXCEPT

Combine query results with SQL set operations. Learn UNION vs UNION ALL, INTERSECT, EXCEPT, column matching rules, sorting, precedence and dialect differences.

Joins combine tables side by side: they add columns from another table to each row. Set operations combine whole query results on top of each other: they take the rows of one SELECT and the rows of another and merge them into a single result with the same columns.

SQL has three set operators, named after the set operations you may remember from math class:

  • UNION returns the rows that appear in either query.
  • INTERSECT returns the rows that appear in both queries.
  • EXCEPT returns the rows of the first query that don't appear in the second.

Each SELECT in the combination is called a member of a compound query. This lesson shows when each operator is the right tool, the rules the members must follow, and a few surprises: duplicate removal, how NULL behaves, and how SQLite's operator precedence differs from the SQL standard.

UNION: rows from either query

The school database stores teachers and students in separate tables. To print one directory of everyone at the school, stack the two lists with UNION ALL. The extra role column, a constant text value, records where each row came from:

SQL One directory from two tables ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

The two members produce different columns from different tables, but each returns two columns, a name and a role, so they can be stacked. The column names of the result come from the first member (name and role); the aliases in later members are ignored, which is why the student half doesn't need any.

UNION vs UNION ALL

UNION ALL simply appends one result to the other. Plain UNION (which means UNION DISTINCT) also removes duplicate rows from the combined result. In the shop database, which customers ordered in January or February 2025?

SQL UNION removes duplicates ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

There were 19 orders in the two months, so UNION ALL gives 19 rows. UNION gives 15 distinct customers: two customers ordered twice in the same month, and two more (customers 15 and 21) ordered in both months. Note that UNION removes all duplicates, including repeats inside a single member, not only rows that appear in both.

Default to UNION ALL. Removing duplicates means the database must sort or hash the entire result, which costs time on large data. Use UNION only when you actually want distinct rows. If the members can't overlap (for example teachers vs students with a role column), UNION ALL gives the same rows faster.

INTERSECT: rows in both

INTERSECT keeps only the rows that both members return. Which customers ordered in January and in February?

SQL Customers in both months ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Customers 15 and 21. Like UNION, INTERSECT returns distinct rows. In the company database the same idea finds people who work on both engineering projects (project 1, the Mobile App Rewrite, and project 2, the Payments Platform):

SQL On both projects ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Six people work on both. Each member here is a full query with its own joins and WHERE clause; set operators work on any queries, however complex, as long as their columns line up.

EXCEPT: rows in the first but not the second

EXCEPT starts with the first result and removes every row that also appears in the second. Order matters: A EXCEPT B is not the same as B EXCEPT A. Which January customers did not come back in February?

SQL January customers who didn't return ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Seven of January's nine customers placed no order in February. Swap the two members to find February's newcomers instead. Oracle calls this operator MINUS; most other databases use EXCEPT.

Including c.id in both members matters. Rows are compared on all their columns, so if you selected only names, two different customers who share a first and last name would count as the same row.

The rules members must follow

  1. Same number of columns. Every member must return the same number of columns, or the query fails.
  2. Columns match by position, not by name. The first column of each member is stacked with the first column of the others, and so on. Swapping two columns in one member silently mixes values up.
  3. Compatible types. Strict databases (PostgreSQL, SQL Server) reject a text column stacked on an integer column; SQLite accepts it but keeps each value's own type.
  4. Names come from the first member. Alias the columns there.
  5. One ORDER BY and one LIMIT, at the very end. They apply to the combined result.

Here are rules 1 and 5 broken on purpose, one at a time. Both examples are expected to fail:

SQL Mismatched column count (fails)

Press Run (or Ctrl + Enter) to see the result here.

SQLite stops with SELECTs to the left and right of UNION do not have the same number of result columns. Add a second column to the first member (for example 'Portugal' AS country) and it runs.

SQL LIMIT inside a member (fails)

Press Run (or Ctrl + Enter) to see the result here.

The error is LIMIT clause should come after UNION not before. To sort or limit one member on its own, wrap it in a subquery, which turns it into an ordinary table the compound can read:

SQL Top 3 of each, combined ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The final ORDER BY sorts all six rows together. In a compound query, ORDER BY may use the first member's column names (salary) or positions (ORDER BY 3), but not arbitrary expressions on tables from inside a member.

How duplicates and NULL are compared

Set operators decide whether two rows are "the same" by comparing every column. Two rules surprise people:

  • NULL counts as equal to NULL. In a WHERE clause, NULL = NULL is unknown (see Working with NULL), but UNION, INTERSECT and EXCEPT treat two NULLs as duplicates, the same way DISTINCT does.
  • In SQLite, values of different types are different. The integer 1 and the text '1' are two distinct values, so UNION keeps both.
SQL NULLs and types in set operations

Press Run (or Ctrl + Enter) to see the result here.

The first query returns a single NULL row: the two NULLs were merged. The second returns two rows that both display as 1: one is the integer 1 and the other the text '1', and different types never count as duplicates in SQLite. The third also returns two rows, because SQLite compares text case-sensitively by default. Other databases may differ on the last two (PostgreSQL rejects mixing integer and text; some MySQL and SQL Server collations ignore case), so don't rely on them in portable code.

Precedence: SQLite reads left to right

When a compound query mixes operators, which one runs first? The SQL standard, PostgreSQL, SQL Server and MySQL give INTERSECT higher precedence, like multiplication before addition. SQLite gives all set operators the same precedence and simply evaluates them left to right. The same query can therefore return different results:

SQL Left to right in SQLite

Press Run (or Ctrl + Enter) to see the result here.

SQLite's first query returns only 4: it unions a and b into 1, 2, 3, 4, then intersects with c. PostgreSQL would run the INTERSECT first and return 1, 2, 3 and 4, which is what the second query produces in SQLite.

PostgreSQL, SQL Server and MySQL let you add parentheses around members to make the grouping explicit, but SQLite doesn't allow parentheses around compound members: (SELECT 1) UNION (SELECT 2) is a syntax error. The subquery wrapper shown above works everywhere, so it's the portable choice whenever you mix operators.

Set operations vs joins and other tools

Some questions can be answered either way. "Customers who ordered in both months" could also be written with IN or EXISTS, and "January but not February" is a classic anti-join. Some guidance:

  • Use UNION ALL to stack similar data from different tables or different queries: archive and current tables, several event types into one timeline, report rows plus a totals row.
  • Use INTERSECT and EXCEPT to compare whole rows, such as reconciling two lists or checking that a rewritten query returns exactly the same rows as the old one (old EXCEPT new and new EXCEPT old should both be empty).
  • Use joins, IN or EXISTS when you need extra columns from the matching rows, or when duplicates must be preserved: set operators return distinct rows (except UNION ALL).

Dialect support

Database UNION / UNION ALL INTERSECT EXCEPT ALL variants of INTERSECT/EXCEPT
SQLite yes yes yes no
PostgreSQL yes yes yes yes
SQL Server yes yes yes no
MySQL yes 8.0.31+ 8.0.31+ 8.0.31+
Oracle yes yes MINUS (also EXCEPT from 21c) 21c+

The ALL variants keep duplicates according to how many times a row appears in each input. For example, in PostgreSQL:

SQL
-- PostgreSQL: a row that appears 3 times in the first query and once
-- in the second appears 2 times in the result
SELECT customer_id FROM orders WHERE status = 'delivered'
EXCEPT ALL
SELECT customer_id FROM orders WHERE status = 'cancelled';

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. The first query returns 5 rows and the second returns 4. Two rows appear in both, and all rows are otherwise distinct. How many rows does UNION ALL return?

    UNION ALL just appends the results: 5 + 4 = 9. UNION would return 7, INTERSECT 2 and EXCEPT 3.
  2. 2. Where do the column names of a compound query come from?

    The first SELECT names the columns; aliases in later members are ignored.
  3. 3. What does SQLite return for SELECT NULL UNION SELECT NULL?

    Set operations treat NULLs as duplicates of each other, just like DISTINCT, so the two rows merge into one.
  4. 4. In SQLite, SELECT n FROM a UNION SELECT n FROM b INTERSECT SELECT n FROM c is evaluated as:

    SQLite gives all compound operators equal precedence. The standard and PostgreSQL run INTERSECT first, so wrap members in subqueries when you mix operators.
  5. 5. Which query lists products that were never ordered?

    EXCEPT keeps rows of the first query that are missing from the second: product ids with no order item. Swapping the members would find order items pointing at missing products.

Summary

  • Set operators stack the results of whole queries: UNION (either), INTERSECT (both), EXCEPT (first but not second; MINUS in Oracle).
  • UNION, INTERSECT and EXCEPT return distinct rows; UNION ALL keeps every row and is faster.
  • Members need the same number of columns, matched by position; names come from the first member.
  • ORDER BY and LIMIT go once, at the end; wrap a member in SELECT * FROM (...) to sort or limit it separately.
  • Set operators treat NULLs as equal; SQLite treats values of different types as different.
  • SQLite evaluates mixed set operators left to right and doesn't allow parentheses around members; the standard gives INTERSECT higher precedence.

Next: Subqueries, where you'll put one query inside another.