SQL Reference ยท Statements and Clauses
SQL INTERSECT
Reference for SQL INTERSECT: return only the rows that appear in both queries, NULL handling, duplicates, and alternatives in databases without it.
INTERSECT returns the rows that appear in the results of both queries. Like UNION, the queries must have the same number of columns with compatible types, and the result has no duplicates.
It is the set-theory "and" for query results. It is often used to answer questions such as "which cities have both customers and suppliers?".
SELECT city FROM customers INTERSECT SELECT city FROM suppliers;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
select_statement INTERSECT [ ALL ] select_statement;
| Part | Description |
|---|---|
INTERSECT |
Rows present in both results, duplicates removed. |
INTERSECT ALL |
Keeps duplicates: a row appearing m times in one and n times in the other appears min(m, n) times. PostgreSQL only. |
Returns: distinct rows common to both queries.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
INTERSECT |
Yes | Yes | 8.0.31+ | Yes | Yes |
INTERSECT ALL |
No | Yes | 8.0.31+ | No | No |
Binds tighter than UNION |
No (left to right) | Yes | Yes | Yes | No (left to right) |
Portable alternative for older MySQL and for INTERSECT-free setups:
SELECT DISTINCT a.customer_id FROM orders AS a INNER JOIN orders AS b ON b.customer_id = a.customer_id WHERE a.status = 'delivered' AND b.status = 'cancelled';
Usage notes
- NULLs match. Unlike
=,INTERSECTtreats two NULLs as the same value. AnINNER JOINon a nullable column would not match them. This is the main reason the two examples can differ. - Column positions, not names, are matched. Reordering the select list changes the meaning.
- Distinct by default, so you rarely need
DISTINCTon top of it. - Performance: equivalent to a semi-join with sort/hash; an
EXISTSorINNER JOINcan sometimes use indexes better. - Mixing operators:
A UNION B INTERSECT CmeansA UNION (B INTERSECT C)in the standard but is read left to right in SQLite and Oracle. Use parentheses where supported.
Related
UNION,EXCEPT,EXISTS,INNER JOIN- Lessons: Set operations, EXISTS