Level 3 · Customers who came back with INTERSECT

INTERSECT returns the rows that appear in both results. Where UNION answers “in either list”, INTERSECT answers “in both lists”.

The query

SELECT customer_id FROM orders
WHERE status = 'completed' AND order_date >= '2024-01-01' AND order_date < '2025-01-01'
INTERSECT
SELECT customer_id FROM orders
WHERE status = 'completed' AND order_date >= '2025-01-01' AND order_date < '2026-01-01';

Each branch is one row per order, with plenty of repeated ids. INTERSECT compares whole rows, keeps the ones found on both sides and, like UNION, returns each one once. At Harbor & Pine, 997 customers completed an order in 2024 and 1,447 in 2025. The intersection is 752: three out of four 2024 buyers came back in 2025.

Why one WHERE cannot do this

A tempting shortcut is one query with two date conditions. But a single order row has one order_date, so order_date in 2024 and in 2025 is never true. “Bought in both years” is a fact about a customer, spread over several rows. Set operations compare the two lists of customers, which is the right level.

The same answer with EXISTS

SELECT DISTINCT o.customer_id
FROM orders AS o
WHERE o.status = 'completed'
  AND o.order_date >= '2024-01-01' AND o.order_date < '2025-01-01'
  AND EXISTS (
    SELECT 1 FROM orders AS n
    WHERE n.customer_id = o.customer_id
      AND n.status = 'completed'
      AND n.order_date >= '2025-01-01' AND n.order_date < '2026-01-01'
  );

It also returns 752 rows. INTERSECT is shorter and reads like the question. EXISTS is more flexible: it can carry extra columns from the outer row and works in every engine. An IN (subquery) version works the same way here.

Once you have the list, the next question is usually “who are they?”. Joining it to customers shows 592 consumer and 160 business accounts among the 752.

Common mistakes

• Selecting more columns than the comparison needs. SELECT customer_id, channel in both branches means a customer counts only if they used the same channel in both years. Add descriptive columns after the set operation, with a join.
• Forgetting the status filter in one branch. A cancelled 2025 order would then count as coming back. Keep the branches identical apart from the date range.
• Forgetting that INTERSECT dedupes. If you need the number of 2025 orders from returning customers, intersect the ids first, then join back to orders.

In SQL Server

INTERSECT exists in SQL Server with the same meaning, and there is no INTERSECT ALL. MySQL added INTERSECT only in 8.0.31, so older MySQL code uses the EXISTS or IN form shown above.