INTERSECT and EXCEPT
INTERSECT and EXCEPT are SQL set operations used to compare the results of two SELECT queries.
INTERSECTreturns only rows that exist in both result sets.EXCEPTreturns rows that appear 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 appear in the first result set 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, and their data types must be compatible.
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 |
This result shows 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 result shows users who were active in 2023 only and did not return in 2024.
Why Use INTERSECT and EXCEPT?
- To find shared records between datasets use
INTERSECT - To identify removed or missing entries use
EXCEPT - Helpful for audits, tracking changes, and membership comparisons
Want to learn more?
Join CodeFriends Plus membership or enroll in a course to start your journey.