SQL 41 🛢️ Result Set Intersection with INTERSECT

Two queries. Two result sets. You need the rows that appear in both. This is set intersection, and SQL provides a dedicated operator for it: INTERSECT. It is one of the three set operators in standard SQL, alongside UNION and EXCEPT. Where UNION combines results and EXCEPT subtracts one from another, INTERSECT returns only the rows that both queries produce. It is the mathematical AND of result sets.

This chapter covers INTERSECT in full. You will learn its syntax, the rules it enforces on column compatibility, how it handles duplicates differently from UNION ALL, and how it differs from an inner join despite producing sometimes similar-looking results. You will also see the practical cases where INTERSECT expresses a query more clearly than any join or subquery could.

By the end, you will understand not just how to write INTERSECT but when it is the right tool—and when a join or an EXISTS subquery is a better choice.

Key point: INTERSECT compares entire rows, not individual columns. Both queries must return the same number of columns, in the same order, with compatible data types. The result contains only rows that appear identically in both result sets, with duplicates removed.

---

Why INTERSECT exists

The set logic problem. SQL is built on relational algebra, and relational algebra includes set operations. When you need “rows in A and also in B,” there is a direct set-theoretic answer: intersection. Without INTERSECT, you would need to express this with joins or IN subqueries, which work but obscure the intent. INTERSECT states the logic directly: give me the common rows.

The join confusion problem. An inner join can produce a similar result to INTERSECT, but it does so by matching on specific columns and then projecting whatever columns you select. INTERSECT compares the full row of each result set. This distinction matters when the two queries produce different column lists from the same tables, or when you want the comparison to consider all columns, not just a join key.

The duplicate handling problem. INTERSECT removes duplicates by default. It treats the result sets as mathematical sets, where each distinct row appears once. This is different from UNION ALL, which preserves duplicates, and it is different from a join, which can multiply rows when keys are not unique. When you want distinct common rows, INTERSECT gives you exactly that.

The readability problem. Consider a query that finds customers who have both placed an order and submitted a support ticket. You could write this with two EXISTS subqueries, or with a join, or with INTERSECT. All three work. But INTERSECT reads as a direct statement of the requirement: the intersection of the set of customers who ordered and the set of customers who submitted tickets. Readability is not a trivial concern in SQL, where queries are read far more often than they are written.

The standard problem. INTERSECT is part of the SQL standard and is supported by PostgreSQL, SQL Server, Oracle, and SQLite. MySQL is the notable exception: it does not support INTERSECT directly, and MySQL users must emulate it with joins or IN subqueries . Knowing which databases support the operator is part of using it correctly.

---

a. Basic syntax and column rules

The syntax of INTERSECT mirrors UNION. You write two SELECT statements with INTERSECT between them.

SELECT column1, column2
FROM table1
INTERSECT
SELECT column1, column2
FROM table2;

The result contains rows that appear in both result sets. The order of the final result is not guaranteed unless you add an ORDER BY clause at the end, which applies to the entire intersected result.

The column rules are strict:

• Both queries must return the same number of columns.
• The columns must be in the same order.
• The data types must be compatible.

-- Valid: same column count and types
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM support_tickets;

-- Invalid: different column counts
SELECT customer_id, order_date FROM orders
INTERSECT
SELECT customer_id FROM support_tickets;  -- error

Unlike joins, INTERSECT does not use ON clauses or join keys. The comparison is positional: the first column of the first query is compared to the first column of the second query, the second to the second, and so on. Column names in the result come from the first query .

---

b. Duplicates and the difference from UNION ALL

INTERSECT removes duplicates. If a row appears multiple times in both result sets, it appears once in the output. This is the default behavior and matches the mathematical definition of set intersection.

-- orders has customer_id 1 three times
-- support_tickets has customer_id 1 twice
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM support_tickets;
-- Result: customer_id 1 appears once

To preserve duplicates, standard SQL provides INTERSECT ALL, though support varies by database. PostgreSQL and Oracle support it; SQL Server does not . INTERSECT ALL returns the minimum number of occurrences of each row across the two result sets. If a row appears three times in the first result and twice in the second, INTERSECT ALL returns it twice.

-- PostgreSQL
SELECT customer_id FROM orders
INTERSECT ALL
SELECT customer_id FROM support_tickets;

This is a subtle but important distinction. INTERSECT answers “which distinct rows are common?” INTERSECT ALL answers “how many times is each common row present, limited by the smaller count?” Most use cases want the former, which is why the default removes duplicates.

---

c. INTERSECT versus joins and EXISTS

The most common question about INTERSECT is why to use it instead of a join or an EXISTS subquery. The answer depends on what you are comparing.

An inner join matches rows on a specified key and then projects columns from both tables. INTERSECT compares complete rows from two independent queries. If the two queries select only the key column, the results can be identical. But they diverge as soon as the column lists differ.

-- Using INTERSECT: customers who both ordered and opened tickets
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM support_tickets;

-- Using a join: same result, but requires DISTINCT to remove duplicates
SELECT DISTINCT o.customer_id
FROM orders o
INNER JOIN support_tickets s ON o.customer_id = s.customer_id;

Both produce the same distinct customer IDs. The join version needs DISTINCT because a customer with three orders and two tickets would appear six times without it. INTERSECT removes duplicates automatically.

The EXISTS approach is often the most performant for large tables:

SELECT DISTINCT customer_id
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM support_tickets s
  WHERE s.customer_id = o.customer_id
);

This pattern is frequently the fastest because the database can stop scanning the subquery as soon as it finds a match. But it is also the most verbose and the least direct statement of the set-intersection intent.

The choice among these forms is a matter of clarity versus performance. INTERSECT is clearest when the logic is genuinely “rows common to both queries.” Joins are clearest when you need columns from both tables. EXISTS is often fastest when the tables are large and the comparison is on an indexed key.

---

Complete Example Session

-- ============================================
-- PART 1: SAMPLE DATA
-- ============================================
CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE
);

CREATE TABLE support_tickets (
  ticket_id INT,
  customer_id INT,
  created_date DATE
);

INSERT INTO orders VALUES
  (1, 101, '2026-01-05'),
  (2, 102, '2026-01-07'),
  (3, 101, '2026-01-09'),
  (4, 103, '2026-01-11');

INSERT INTO support_tickets VALUES
  (1, 101, '2026-01-06'),
  (2, 103, '2026-01-12'),
  (3, 104, '2026-01-15');

-- ============================================
-- PART 2: BASIC INTERSECT
-- ============================================
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM support_tickets;
-- Result: 101, 103

-- ============================================
-- PART 3: INTERSECT WITH DUPLICATES IN SOURCE
-- ============================================
-- orders contains 101 twice, support_tickets contains 101 once
-- INTERSECT still returns 101 once
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM support_tickets;

-- ============================================
-- PART 4: COMPARISON WITH JOIN
-- ============================================
SELECT DISTINCT o.customer_id
FROM orders o
INNER JOIN support_tickets s ON o.customer_id = s.customer_id;
-- Same result: 101, 103

-- ============================================
-- PART 5: COMPARISON WITH EXISTS
-- ============================================
SELECT DISTINCT customer_id
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM support_tickets s
  WHERE s.customer_id = o.customer_id
);
-- Same result: 101, 103

-- ============================================
-- PART 6: MULTI-COLUMN INTERSECT
-- ============================================
SELECT customer_id, order_date FROM orders
INTERSECT
SELECT customer_id, created_date FROM support_tickets;
-- Compares both columns positionally
-- Result: rows where both columns match exactly

-- ============================================
-- PART 7: INTERSECT WITH ORDER BY
-- ============================================
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM support_tickets
ORDER BY customer_id DESC;
-- Result: 103, 101

-- ============================================
-- PART 8: THREE-WAY INTERSECT
-- ============================================
CREATE TABLE newsletter_subscribers (customer_id INT);
INSERT INTO newsletter_subscribers VALUES (101), (105);

SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM support_tickets
INTERSECT
SELECT customer_id FROM newsletter_subscribers;
-- Result: 101

-- ============================================
-- PART 9: INTERSECT ALL (PostgreSQL)
-- ============================================
SELECT customer_id FROM orders
INTERSECT ALL
SELECT customer_id FROM support_tickets;
-- Returns 101 twice (min of 2 occurrences in orders, 1 in tickets)
-- Actually returns 101 once because min(2,1) = 1

-- ============================================
-- PART 10: VERIFICATION
-- ============================================
SELECT customer_id, 'in orders' AS source FROM orders
WHERE customer_id IN (SELECT customer_id FROM support_tickets)
UNION ALL
SELECT customer_id, 'in tickets' FROM support_tickets
WHERE customer_id IN (SELECT customer_id FROM orders)
ORDER BY customer_id;

The ten parts covered the essential behavior of INTERSECT: sample data setup, basic intersection, duplicate handling, comparison with joins, comparison with EXISTS, multi-column comparison, ordering, three-way intersection, INTERSECT ALL, and verification.

---

Quick Reference

Set Operators

Operator	Returns	Duplicates
UNION	Rows from both queries	Removed
UNION ALL	Rows from both queries	Preserved
INTERSECT	Rows in both queries	Removed
INTERSECT ALL	Rows in both queries	Min count preserved
EXCEPT	Rows in first, not second	Removed

INTERSECT Rules

Rule	Requirement
Column count	Must match
Column order	Must match
Data types	Must be compatible
Column names	Taken from first query
Join keys	Not used; positional comparison
Order	Undefined unless ORDER BY added

Database Support

Database	INTERSECT	INTERSECT ALL
PostgreSQL	Yes	Yes
SQL Server	Yes	No
Oracle	Yes	Yes
SQLite	Yes	No
MySQL	No (emulate)	No

---

Best Practices

✅ Do This:

-- Use INTERSECT when the intent is "rows in both"
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM support_tickets;                   -- ✅

-- Add ORDER BY for deterministic output
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM support_tickets
ORDER BY customer_id;                                      -- ✅

-- Keep column lists aligned and minimal
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM support_tickets;                   -- ✅

-- Use EXISTS when performance matters on large tables
SELECT customer_id FROM orders o
WHERE EXISTS (SELECT 1 FROM support_tickets s
              WHERE s.customer_id = o.customer_id);        -- ✅

❌ Don’t Do This:

-- Mismatched column counts
SELECT customer_id, order_date FROM orders
INTERSECT
SELECT customer_id FROM support_tickets;                   -- ❌

-- Mismatched column order
SELECT customer_id, order_date FROM orders
INTERSECT
SELECT order_date, customer_id FROM orders;                -- ❌

-- Expecting duplicates in output
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM support_tickets;                   -- ❌ (no duplicates)

-- Using INTERSECT in MySQL without emulation
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM support_tickets;                   -- ❌ (syntax error)

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Syntax error in MySQL	INTERSECT not supported	Emulate with JOIN or EXISTS
Unexpected empty result	Column types incompatible	Cast columns to matching types
Wrong comparison	Column order differs	Align column order in both queries
Duplicates expected	INTERSECT removes them	Use INTERSECT ALL if supported
Slow on large tables	Full row comparison	Use indexed EXISTS instead
No output order	No ORDER BY clause	Add ORDER BY at the end

---

Real-World Examples

1. Customers Who Ordered and Complained

SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM complaints;

2. Users in Two Roles

SELECT user_id FROM admins
INTERSECT
SELECT user_id FROM developers;

3. Products in Two Categories

SELECT product_id FROM category_a
INTERSECT
SELECT product_id FROM category_b;

4. Students in Two Courses

SELECT student_id FROM enrollment_cs101
INTERSECT
SELECT student_id FROM enrollment_math201;

5. Emails in Two Lists

SELECT email FROM newsletter_list
INTERSECT
SELECT email FROM customer_list;

6. Multi-Column Match

SELECT first_name, last_name FROM employees
INTERSECT
SELECT first_name, last_name FROM contractors;

7. Three-Way Intersection

SELECT user_id FROM logins
INTERSECT
SELECT user_id FROM purchases
INTERSECT
SELECT user_id FROM reviews;

8. With Ordering

SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM tickets
ORDER BY customer_id;

9. MySQL Emulation with JOIN

SELECT DISTINCT o.customer_id
FROM orders o
INNER JOIN tickets t ON o.customer_id = t.customer_id;

10. MySQL Emulation with EXISTS

SELECT DISTINCT customer_id FROM orders o
WHERE EXISTS (SELECT 1 FROM tickets t
              WHERE t.customer_id = o.customer_id);

---

Visual

Set Operations Compared

┌─────────────────────────────────────────────────────────────┐
│  SET OPERATIONS ON TWO RESULT SETS                          │
│                                                             │
│  Set A: {1, 2, 3, 4}                                        │
│  Set B: {3, 4, 5, 6}                                        │
│                                                             │
│  UNION:          {1, 2, 3, 4, 5, 6}   (all rows)            │
│  UNION ALL:      {1, 2, 3, 4, 3, 4, 5, 6}  (with dups)      │
│  INTERSECT:      {3, 4}               (common rows)         │
│  EXCEPT:         {1, 2}               (A minus B)           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

INTERSECT vs JOIN

┌─────────────────────────────────────────────────────────────┐
│  INTERSECT vs INNER JOIN                                    │
│                                                             │
│  INTERSECT                                                  │
│  ┌──────────────────────────────────────────────────────┐   │
│  │ Compares entire rows positionally.                   │   │
│  │ Removes duplicates automatically.                     │   │
│  │ No join keys.                                         │   │
│  │ Result columns from first query only.                 │   │
│  └──────────────────────────────────────────────────────┘   │
│                                                             │
│  INNER JOIN                                                 │
│  ┌──────────────────────────────────────────────────────┐   │
│  │ Matches on specified key columns.                    │   │
│  │ Can multiply rows.                                    │   │
│  │ Requires ON clause.                                   │   │
│  │ Result columns from both tables.                      │   │
│  └──────────────────────────────────────────────────────┘   │
│                                                             │
└─────────────────────────────────────────────────────────────┘

INTERSECT Processing Steps

┌─────────────────────────────────────────────────────────────┐
│  HOW INTERSECT EXECUTES                                     │
│                                                             │
│  1. Execute first SELECT                                    │
│     → Result set A                                          │
│                                                             │
│  2. Execute second SELECT                                   │
│     → Result set B                                          │
│                                                             │
│  3. Remove duplicates from A and B                          │
│     → Distinct sets                                        │
│                                                             │
│  4. Find rows present in both distinct sets                 │
│     → Intersection                                         │
│                                                             │
│  5. Apply ORDER BY if present                               │
│     → Final result                                          │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Duplicate Handling

┌─────────────────────────────────────────────────────────────┐
│  DUPLICATE HANDLING IN INTERSECT                            │
│                                                             │
│  Query A returns:  [1, 1, 2, 2, 2, 3]                       │
│  Query B returns:  [1, 2, 2, 4]                             │
│                                                             │
│  INTERSECT result: [1, 2]                                   │
│  → Each common row once                                    │
│                                                             │
│  INTERSECT ALL result: [1, 2, 2]                            │
│  → min(count in A, count in B) for each row                │
│  → 1 appears min(2, 1) = 1 time                            │
│  → 2 appears min(3, 2) = 2 times                           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

---

Summary

Item	Value
Operator	INTERSECT returns rows in both result sets
Column rule	Same count, order, compatible types
Duplicates	Removed by default
INTERSECT ALL	Preserves min occurrence count
Comparison	Positional, not by join key
Result columns	From first query
Ordering	Undefined unless ORDER BY added
MySQL support	Not supported; emulate with join or EXISTS
Related operators	UNION, UNION ALL, EXCEPT
Best use	“Rows common to both queries”

Key takeaways:

• INTERSECT returns only rows that appear in both result sets. It is the set-theoretic AND of two queries, stated directly in SQL.
• Column compatibility is strict. Both queries must return the same number of columns, in the same order, with compatible types. Comparison is positional, not by name.
• Duplicates are removed by default. INTERSECT treats results as sets. Each distinct common row appears once. INTERSECT ALL preserves the minimum count, where supported.
• INTERSECT is not a join. A join matches on keys and can project columns from both tables. INTERSECT compares complete rows from two independent queries and projects columns from the first.
• EXISTS is often faster on large tables. When the comparison is on an indexed key, EXISTS can short-circuit. INTERSECT may require full row comparison. Choose based on performance testing, not assumption.
• MySQL does not support INTERSECT. MySQL users must emulate it with INNER JOIN ... DISTINCT or WHERE EXISTS. The result is equivalent; the syntax differs.
• Add ORDER BY for deterministic output. Without it, the order of intersected rows is undefined and may vary between executions.

Remember: INTERSECT is the clearest way to express “rows in both” when that is genuinely the question. It reads as set logic because it is set logic. But clarity is not the only consideration. On large tables, an EXISTS subquery with an index can outperform INTERSECT because the database can stop scanning as soon as it finds a match. The right choice depends on the data volume, the indexes, and the readability needs of the codebase. Know both, and choose deliberately.

---

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!