intermediate

INTERSECT Operator in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-208 min readRuntime Verified

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 INTERSECT treats NULL values identically (unlike JOIN ... ON col1 = col2).
  • INTERSECT ALL: Understanding the multiset intersection variant across modern dialects.
  • Subquery Optimization: Rewriting INTERSECT using EXISTS for 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:

sql
-- 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:

  1. Automatic Deduplication: Like UNION, the standard INTERSECT operator automatically removes duplicates from the final output. Even if a user logged into both platforms 50 times, their ID appears exactly once.
  2. 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:

sql
-- 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:

sql
-- 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 INTERSECT produces distinct rows. If you need multiset intersection (retaining the minimum count of matching duplicate rows), use INTERSECT ALL in PostgreSQL / Oracle.
  • Projecting Unnecessary Columns: If you include a created_at timestamp or autoincrementing id in both SELECT statements, INTERSECT checks 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:

  1. NULL Treatment: INTERSECT treats two NULL values as equivalent matching values. An INNER JOIN ... ON col1 = col2 rejects matching rows where the join keys are NULL because NULL = NULL evaluates to UNKNOWN.
  2. Deduplication: INTERSECT automatically deduplicates the output. An INNER JOIN multiplies rows if one table contains multiple matching records (Cartesian expansion on duplicate keys).
  3. Execution Plan: Engines execute INTERSECT via Hash Intersection (hashing both streams and outputting bucket matches) or Merge Intersection (sorting both datasets).

Interactive Practice Challenge

Challenge: Identify Cross-Department Contractors

You have an engineering_contractors table and an operations_contractors table, both containing contractor_id and tax_id. Write a query using INTERSECT that returns all contractors who are actively contracted to work across both departments simultaneously.

DPA

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.