INTERSECT Operator in SQL
Overview
The INTERSECT operator is a set operation in SQL that combines the result sets of two SELECT queries and returns only the distinct rows that appear in both query outputs ($A \cap B$). It is the relational implementation of set intersection. While conceptually straightforward, understanding how INTERSECT handles NULL values compared to standard INNER JOIN operations and when to prefer alternative join formulations is a staple of intermediate SQL engineering.
What You Will Learn
In this lesson, you will master:
- Set Intersection Theory: Extracting common elements between two result sets ($A \cap B$).
- INTERSECT vs INNER JOIN: When to use a set operator vs a relational join.
- NULL Equality in Sets: How
INTERSECTtreatsNULLvalues identically (unlikeJOIN ... ON col1 = col2). - INTERSECT ALL: Understanding the multiset intersection variant across modern dialects.
- Subquery Optimization: Rewriting
INTERSECTusingEXISTSfor superior query planner optimization.
Basic Syntax & Practical Use Case
Consider an application where users can interact via a Mobile App or a Web Portal. You want to identify power users who actively use both platforms:
-- Query 1: Users active on Web Portal this month
SELECT user_id, email
FROM web_user_activity
WHERE activity_date >= '2026-07-01'
INTERSECT
-- Query 2: Users active on Mobile App this month
SELECT user_id, email
FROM mobile_user_activity
WHERE activity_date >= '2026-07-01'
ORDER BY user_id;
Key Execution Properties:
- Automatic Deduplication: Like
UNION, the standardINTERSECToperator automatically removes duplicates from the final output. Even if a user logged into both platforms 50 times, their ID appears exactly once. - Column Alignment: Just like
UNION, both queries must have identical column counts and compatible data types in corresponding positions.
The Critical NULL Semantics: INTERSECT vs INNER JOIN
One of the most profound differences between INTERSECT and INNER JOIN is how they evaluate NULL:
-- Suppose Table A has: (1, 'Alice'), (2, NULL)
-- Suppose Table B has: (1, 'Alice'), (2, NULL)
-- 1. Using INTERSECT:
SELECT id, code FROM TableA
INTERSECT
SELECT id, code FROM TableB;
-- Output:
-- (1, 'Alice')
-- (2, NULL) <-- INCLUDED! In Set operations, NULL IS EQUAL TO NULL!
-- 2. Using INNER JOIN:
SELECT a.id, a.code
FROM TableA a
JOIN TableB b ON a.id = b.id AND a.code = b.code;
-- Output:
-- (1, 'Alice')
-- (2, NULL) is DROPPED! Because (NULL = NULL) evaluates to UNKNOWN!
Key Architectural Distinction
In standard SQL relational joins, NULL = NULL is falsy (UNKNOWN), dropping the row. In SQL set operations (INTERSECT, UNION, EXCEPT), two NULLs are treated as identical matching values.
Rewriting INTERSECT with INNER JOIN and EXISTS
While INTERSECT is clean and readable, in complex queries involving many columns or large datasets, database query optimizers often create better execution plans when rewritten with EXISTS or INNER JOIN:
-- Alternative 1: Using EXISTS (High Performance with Indexes)
SELECT DISTINCT w.user_id, w.email
FROM web_user_activity w
WHERE w.activity_date >= '2026-07-01'
AND EXISTS (
SELECT 1
FROM mobile_user_activity m
WHERE m.activity_date >= '2026-07-01'
AND m.user_id = w.user_id
AND m.email = w.email
);
-- Alternative 2: Using INNER JOIN
SELECT DISTINCT w.user_id, w.email
FROM web_user_activity w
INNER JOIN mobile_user_activity m
ON w.user_id = m.user_id
AND (w.email = m.email OR (w.email IS NULL AND m.email IS NULL))
WHERE w.activity_date >= '2026-07-01'
AND m.activity_date >= '2026-07-01';
Common Mistakes & Gotchas
- Assuming INTERSECT Keeps Duplicate Multiplicities: Standard
INTERSECTproduces distinct rows. If you need multiset intersection (retaining the minimum count of matching duplicate rows), useINTERSECT ALLin PostgreSQL / Oracle. - Projecting Unnecessary Columns: If you include a
created_attimestamp or autoincrementingidin bothSELECTstatements,INTERSECTchecks uniqueness across the entire row. Two identical users with different timestamps will not intersect!
Interview Perspective
SQL Interview Question: How does INTERSECT treat NULL values compared to an INNER JOIN, and what is its time complexity?
Key Talking Points for Candidates:
- NULL Treatment:
INTERSECTtreats twoNULLvalues as equivalent matching values. AnINNER JOIN ... ON col1 = col2rejects matching rows where the join keys areNULLbecauseNULL = NULLevaluates toUNKNOWN. - Deduplication:
INTERSECTautomatically deduplicates the output. AnINNER JOINmultiplies rows if one table contains multiple matching records (Cartesian expansion on duplicate keys). - Execution Plan: Engines execute
INTERSECTvia Hash Intersection (hashing both streams and outputting bucket matches) or Merge Intersection (sorting both datasets).
Interactive Practice Challenge
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.