Skip to content
Tutorials Center
My learning

Topics

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?".

SQL
SELECT city FROM customers
INTERSECT
SELECT city FROM suppliers;

Try it

SQL Customers with both delivered and cancelled orders ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL INTERSECT compares NULLs as equal ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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:

SQL
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 =, INTERSECT treats two NULLs as the same value. An INNER JOIN on 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 DISTINCT on top of it.
  • Performance: equivalent to a semi-join with sort/hash; an EXISTS or INNER JOIN can sometimes use indexes better.
  • Mixing operators: A UNION B INTERSECT C means A UNION (B INTERSECT C) in the standard but is read left to right in SQLite and Oracle. Use parentheses where supported.