INTERSECT and INTERSECT ALL
INTERSECT returns the rows that both of its queries produce, deduplicated. It follows the same column rules as UNION and EXCEPT, and like them compares whole rows, with NULL equal to NULL. Among the 3 set operators it has the highest precedence in the SQL standard: A UNION B INTERSECT C means A UNION (B INTERSECT C) in PostgreSQL and Trino, so parentheses around any mixed chain are worth their 2 characters.
Users who are both known and active
The users table holds 5 registered users. user_sessions holds 4 session rows for users 2, 2, 4 and 6, and user_purchases holds 5 purchase rows for users 2, 2, 2, 4 and 7. User 6 has sessions and no users row, and user 7 has purchases and no row either, which is the kind of orphan the previous section's EXCEPT would surface. The switcher asks 3 questions of those tables.
| user_id | plan |
|---|---|
| 1 | free |
| 2 | team |
| 3 | free |
| 4 | team |
| 5 | enterprise |
| user_id | session_date |
|---|---|
| 2 | 2024-03-11 |
| 2 | 2024-03-13 |
| 4 | 2024-03-12 |
| 6 | 2024-03-14 |
| user_id | amount |
|---|---|
| 2 | 20 |
| 2 | 20 |
| 2 | 45 |
| 4 | 20 |
| 7 | 99 |
Registered
Registered users with at least one session.
SELECT user_id FROM users
INTERSECT
SELECT user_id FROM user_sessions
ORDER BY user_id;| user_id |
|---|
| 2 |
| 4 |
Paying
Users with both a session and a purchase, each listed once.
SELECT user_id FROM user_sessions
INTERSECT
SELECT user_id FROM user_purchases
ORDER BY user_id;| user_id |
|---|
| 2 |
| 4 |
Counted
INTERSECT ALL's result, rebuilt by numbering copies on each side.
WITH s AS (
SELECT user_id,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY session_date) AS n
FROM user_sessions
),
p AS (
SELECT user_id,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount) AS n
FROM user_purchases
)
SELECT user_id
FROM (
SELECT user_id, n FROM s
INTERSECT
SELECT user_id, n FROM p
) AS matched
ORDER BY user_id;| user_id |
|---|
| 2 |
| 2 |
| 4 |
The first tab returns 2 and 4, the registered users with at least one session. User 6 is dropped because the left side never produces it. The second tab returns 2 and 4 again: users with both sessions and purchases. The third tab returns 2, 2 and 4, the result INTERSECT ALL gives on engines that support it. Here it is rebuilt by numbering each user's rows on both sides and intersecting the numbered pairs. INTERSECT ALL keeps each row as many times as the smaller side holds it. User 2 appears 2 times in sessions and 3 times in purchases, so the pairs (2, 1) and (2, 2) match and (2, 3) has no partner. User 4 appears once on each side.
SELECT user_id FROM users
INTERSECT
SELECT user_id FROM user_sessions| user_id |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| user_id |
|---|
| 2 |
| 2 |
| 4 |
| 6 |
The animation plays the first tab. Each of the 5 user rows is checked against the session rows; 2 and 4 find matches and move to the result, and the second session for user 2 adds nothing, since the result holds each value once.
| user_id | session rows | purchase rows | INTERSECT | INTERSECT ALL |
|---|---|---|---|---|
| 2 | 2 | 3 | 1 copy | 2 copies |
| 4 | 1 | 1 | 1 copy | 1 copy |
| 6 | 1 | 0 | absent | absent |
| 7 | 0 | 1 | absent | absent |
user_sessions holds user 2 on 2 rows and user_purchases holds user 2 on 3 rows. How many times does user 2 appear in SELECT user_id FROM user_sessions INTERSECT ALL SELECT user_id FROM user_purchases?
- 2 times: INTERSECT ALL keeps each row up to the count on the side that has fewer copies.
- 3 times: the larger count wins, as it does with UNION ALL.
- 6 times: 2 copies pair with 3 copies, the way an inner join fans out.
- Once: INTERSECT ALL dedupes like INTERSECT, and ALL only changes performance.
INTERSECT against a join
An INNER JOIN on user_id with DISTINCT answers the same question and leaves 2 choices open that INTERSECT closes. The join compares only the columns in its ON, so users.plan and user_sessions.session_date can both come out; INTERSECT can only compare rows whose columns line up, which is why each tab projects to user_id first. The join also fans out: user 2 with 2 sessions and 3 purchases produces 6 joined rows before DISTINCT, while INTERSECT never multiplies. For a key-only overlap check, INTERSECT is the shorter and the safer query; for anything that needs columns from both sides, the join.
Funnel analysis is the usual home: viewers who also purchased, trial accounts that also opened a support ticket, keys present in both of 2 systems before a migration cutover. Chaining is allowed, so sessions INTERSECT purchases INTERSECT tickets finds the users present in all 3, a question that EXCEPT cannot express without nesting.
About this lesson
This section is part of the Query Structure: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.