Understanding SQL INTERSECT Operator

Summary: this tutorial explains the SQL INTERSECT operator and shows you how to apply it to get the intersection of two or more queries.

Introduction to SQL INTERSECT operator

The INTERSECT operator is a set operator that returns distinct rows of two or more result sets from SELECT statements.

Suppose, we have two tables: A(1,2) and B(2,3).

The following picture illustrates the intersection of A & B tables.

The purple section is the intersection of the green and blue result sets.

Like the UNION operator, the INTERSECT operator removes the duplicate rows from the final result set.

The following statement illustrates how to use the INTERSECT operator to find the intersection of two result sets.

SELECT
	id
FROM
	a 
INTERSECT
SELECT
	id
FROM
	b;Code language: SQL (Structured Query Language) (sql)

To use the INTERSECT operator, the columns of the SELECT statements must follow the rules:

The data types of columns must be compatible.
The number of columns and their orders in the SELECT statements must be the same.

SQL INTERSECT operator example

The following SELECT statement returns rows from the table A:

SELECT
	id
FROM
	A;Code language: SQL (Structured Query Language) (sql)

And the following statement retrieves the data from the table B:

SELECT
	id
FROM
	B;Code language: SQL (Structured Query Language) (sql)

The following statement uses the INTERSECT operator to get the intersection of both queries.

SELECT
	id
FROM
	a 
INTERSECT
SELECT
	id
FROM
	b;Code language: SQL (Structured Query Language) (sql)

SQL INTERSECT with ORDER BY example

To sort the result set returned by the INTERSECT operator, you place the ORDER BY clause at the end of all statements.

For example, the following statement applies the INTERSECT operator to the A and B tables and sorts the combined result set by the id column in descending order.

SELECT
	id
FROM
	a 
INTERSECT
SELECT
	id
FROM
	b
ORDER BY id DESC;Code language: SQL (Structured Query Language) (sql)

Emulate SQL INTERSECT operator using INNER JOIN clause

Most relational database system supports the INTERSECT operator such as Oracle Database, Microsoft SQL Server, PostgreSQL, etc. However, some database systems do not provide the INTERSECT operator like MySQL.

To emulate the SQL INTERSECT operator, you can use the INNER JOIN clause as follows:

SELECT
	a.id
FROM
	a
INNER JOIN b ON b.id = a.idCode language: SQL (Structured Query Language) (sql)

It returns the rows in the A table that have matching rows in the B table, which produces the same result as the INTERSECT operator.

Now you should have a good understanding of the SQL INTERSECT operator and know how to use it to find the intersections of multiple queries.

Was this tutorial helpful ?