SQL INTERSECT Operator – Compare Common Rows Across Queries | DbSchema

Table of Contents

1. What the SQL INTERSECT operator does
2. INTERSECT syntax and rules
3. INTERSECT examples
4. How INTERSECT handles duplicates
5. INTERSECT vs INNER JOIN
6. INTERSECT vs UNION and EXCEPT
7. Database support and workarounds
8. Run INTERSECT queries in DbSchema
9. FAQ
10. Conclusion

The SQL INTERSECT operator returns only the rows that appear in the result sets of all participating SELECT statements. It is a set operator, which means it compares whole rows rather than joining tables column by column.

This makes INTERSECT ideal for “show me what these queries have in common” problems. It is also one of the most misunderstood SQL operators, so this guide focuses on the practical differences between INTERSECT, INNER JOIN, and other set operators, with examples you can inspect in DbSchema.

What the SQL INTERSECT operator does

Think of INTERSECT as the overlap between query results.

If query A returns:

and query B returns:

then:

SELECT customer_id FROM query_a
INTERSECT
SELECT customer_id FROM query_b;

returns:

If you are just starting with SQL query structure, review SQL SELECT first.

INTERSECT syntax and rules

SELECT column1, column2
FROM table1

INTERSECT

SELECT column1, column2
FROM table2;

Rules:

1. each query must return the same number of columns
2. matching column positions must use compatible data types
3. column names in the final result come from the first SELECT
4. standard INTERSECT removes duplicates automatically

INTERSECT examples

Customers who are also suppliers

SELECT email
FROM customers

INTERSECT

SELECT email
FROM suppliers;

Students enrolled in both courses

SELECT student_id
FROM course_enrollments
WHERE course_id = 101

INTERSECT

SELECT student_id
FROM course_enrollments
WHERE course_id = 202;

Products sold in every region listed

SELECT product_id
FROM regional_sales
WHERE region = 'North'

INTERSECT

SELECT product_id
FROM regional_sales
WHERE region = 'South'

INTERSECT

SELECT product_id
FROM regional_sales
WHERE region = 'East';

Chaining multiple INTERSECT operators is valid SQL and is a clean way to find records shared across several filtered data sets.

How INTERSECT handles duplicates

Standard INTERSECT behaves like UNION in one important way: it removes duplicate rows from the final result.

SELECT product_id FROM online_orders
INTERSECT
SELECT product_id FROM store_orders;

If product_id = 10 appears many times in both source queries, the standard result still returns 10 once.

What about INTERSECT ALL?

INTERSECT ALL preserves duplicate counts, but support depends on the database and version. PostgreSQL supports it, while many teams still rely only on standard INTERSECT for portability.

If duplicate preservation matters, always check your engine and version first.

INTERSECT vs INNER JOIN

This is the comparison many competitor pages miss or explain poorly.

Side-by-side example

-- INTERSECT: shared product IDs
SELECT product_id
FROM orders
INTERSECT
SELECT product_id
FROM inventory;

-- INNER JOIN: combine rows from both tables
SELECT DISTINCT o.product_id
FROM orders o
INNER JOIN inventory i
  ON o.product_id = i.product_id;

These queries may return similar values, but they solve different problems:

• use INTERSECT when you care about shared row values
• use INNER JOIN when you need to combine attributes from related rows

If you need a deeper join refresher, read SQL Joins Explained.

INTERSECT vs UNION and EXCEPT

SELECT customer_id
FROM orders
EXCEPT
SELECT customer_id
FROM returns;

That query returns customers who ordered something but never appeared in the returns list.

For the related set-operator explanation, see SQL UNION Operator.

Database support and workarounds

Workaround when INTERSECT is unavailable

On older MySQL versions, use EXISTS, IN, or a join-based workaround:

SELECT DISTINCT c.email
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM suppliers s
  WHERE s.email = c.email
);

Or:

SELECT DISTINCT c.email
FROM customers c
INNER JOIN suppliers s
  ON s.email = c.email;

Both patterns solve the same “shared rows” problem without native INTERSECT.

Run INTERSECT queries in DbSchema

DbSchema helps you test set operators safely because you can preview each branch separately before you compare them.

1. Connect through the PostgreSQL JDBC driver, MySQL JDBC driver, or another supported driver.
2. Run each SELECT branch in the SQL editor to verify column order, data type compatibility, and row counts.
3. Add the INTERSECT operator and compare the final result.
4. Use the Relational Data Explorer to inspect the underlying rows.
5. Review related keys and table structure in the schema documentation when debugging why two result sets do or do not overlap.

This workflow is especially useful for reconciliation, migration validation, and data quality checks.

FAQ

What is the difference between INTERSECT and INNER JOIN?
INTERSECT finds common rows across query results. INNER JOIN combines related rows from tables based on a join condition and can return many more columns.

Does INTERSECT remove duplicates? Yes. Standard INTERSECT returns distinct common rows only.

Can I use INTERSECT with more than two queries? Yes. Chain multiple INTERSECT operators to keep only rows present in every result set.

Does MySQL support INTERSECT? Recent MySQL versions do. Native support was added in MySQL 8.0.31. Older versions need workarounds using EXISTS, IN, or joins.

When should I use INTERSECT instead of EXISTS? Use INTERSECT when both branches naturally return the same columns and you want shared rows clearly expressed. Use EXISTS when you are checking row existence against a related table.

Conclusion

SQL INTERSECT is a clean, readable way to ask “what do these queries have in common?” Once you separate that idea from join logic, the operator becomes much easier to use correctly.

For related reading, continue with SQL UNION Operator, SQL Joins Explained, and SQL EXISTS Operator.