INTERSECT and EXCEPT
INTERSECT and EXCEPT are SQL set operations that let you compare the results of two SELECT queries.
INTERSECTreturns only rows that exist in both result sets.EXCEPTreturns rows that exist in the first query but not in the second.
Syntax
You can use INTERSECT to compare the results of two queries like this:
INTERSECT syntax
SELECT column1, column2
FROM tableA
INTERSECT
SELECT column1, column2
FROM tableB;
The syntax for EXCEPT is similar, but it returns rows that exist in the first query but not in the second.
EXCEPT syntax
SELECT column1, column2
FROM tableA
EXCEPT
SELECT column1, column2
FROM tableB;
Like
UNION, both queries must return the same number of columns with compatible data types.
Example: CodeFriends Users in Two Years
Suppose we want to compare users from 2023 and 2024.
users_2023
| user_id | name |
|---|---|
| 1 | Sofia |
| 2 | Ethan |
| 3 | Aisha |
users_2024
| user_id | name |
|---|---|
| 1 | Sofia |
| 3 | Aisha |
| 4 | Noah |
INTERSECT Example
INTERSECT query
SELECT user_id, name
FROM users_2023
INTERSECT
SELECT user_id, name
FROM users_2024;
Result:
| user_id | name |
|---|---|
| 1 | Sofia |
| 3 | Aisha |
These are users who were active in both years.
EXCEPT Example
EXCEPT query
SELECT user_id, name
FROM users_2023
EXCEPT
SELECT user_id, name
FROM users_2024;
Result:
| user_id | name |
|---|---|
| 2 | Ethan |
This returns users who were active in 2023 only and didn't return in 2024.
Why Use INTERSECT and EXCEPT?
- To find shared records between datasets (
INTERSECT) - To identify removed or missing entries (
EXCEPT) - Useful for audits, tracking changes, and membership comparisons
Want to learn more?
Join CodeFriends Plus membership or enroll in a course to start your journey.