INTERSECT Operator in SQL

Sometimes you need to find what two datasets have in common. Which customers bought from both the online store and the physical store? Which students are enrolled in both Mathematics and Computer Science? Which products appear in both the Mumbai warehouse and the Delhi warehouse?

The INTERSECT operator returns only the rows that appear in both result sets. Think of it as the overlap in a Venn diagram — only the data that exists in both queries makes it to the final result.

Finding commonality between datasets is a frequent analytical need. Marketing teams want to know which customers engage across multiple channels. HR needs to identify employees who hold multiple certifications. Product managers want to see which features are requested by both enterprise and startup customers. INTERSECT gives you a clean, declarative way to answer these intersection questions without complex JOIN conditions or correlated subqueries.

Simple Example

OnlineBuyers:

StoreBuyers:

Result:

Only Priya and Sneha appear in both tables, so only they appear in the result.

INTERSECT with a Single Table

You can use INTERSECT on the same table with different conditions to find rows satisfying multiple criteria:

Using INTERSECT on the same table is particularly useful when you need to verify that rows satisfy multiple independent criteria that are each complex enough to warrant their own query. While a simple AND in the WHERE clause works for basic conditions, INTERSECT becomes clearer when each condition involves its own set of JOINs, subqueries, or aggregations. It decomposes the problem into independently understandable parts.

This is equivalent to:

While the AND version is simpler for this case, INTERSECT becomes powerful when the conditions come from different tables or complex subqueries.

How INTERSECT Determines Matches

INTERSECT compares entire rows. Two rows are considered the same only if ALL columns match:

Real-World Example: Students in Multiple Courses

Find students enrolled in both Database Systems and Operating Systems:

This returns only students taking both courses simultaneously.

Real-World Example: Products in Multiple Warehouses

Find products available in both the Mumbai and Bangalore warehouses:

Real-World Example: Common Skills in Job Applicants

Find skills that multiple candidates share:

INTERSECT vs INNER JOIN

Both INTERSECT and INNER JOIN find common data, but they work differently:

Example showing the difference:

Use INTERSECT when you want to find identical rows. Use INNER JOIN when you need to match on specific columns and possibly retrieve additional columns.

INTERSECT and NULL Values

Unlike regular comparisons where NULL equals nothing (not even another NULL), INTERSECT treats two NULLs as equal:

This is an important difference from regular WHERE comparisons where NULL = NULL is UNKNOWN.

Multiple INTERSECT Operations

You can chain multiple INTERSECT operations:

This returns only customers who appear in all three tables.

INTERSECT with ORDER BY

ORDER BY can only appear at the end:

Database Support

For older MySQL versions, you can simulate INTERSECT using INNER JOIN or EXISTS:

Common Mistakes to Avoid

Mistake 1: Expecting partial matches. INTERSECT requires ALL columns to match. If you want to match on just some columns, use INNER JOIN.

Mistake 2: Forgetting column count must match. Both queries must return exactly the same number of columns.

Mistake 3: Using INTERSECT in MySQL 5.x. Older MySQL versions do not support INTERSECT. Use a JOIN-based alternative.

Mistake 4: Confusing with INNER JOIN behavior. INTERSECT removes duplicates automatically. INNER JOIN does not.

Best Practices

1. Use INTERSECT for finding overlapping data between two result sets
2. Prefer INNER JOIN when you need columns from both tables in the result
3. Chain INTERSECT to find data common across three or more sources
4. Check database version for MySQL compatibility
5. Use meaningful aliases in complex INTERSECT queries for readability
6. Consider performance — INTERSECT requires sorting/hashing to find common rows

INTERSECT is a clean, readable way to find common elements between datasets. It is particularly useful in analytical queries where you need to identify overlap between different groups, time periods, or categories.