SQL INTERSECT

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 the 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;

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 table A:

SELECT
	id
FROM
	A;

And the following statement retrieves the data from table B:

SELECT
	id
FROM
	B;

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

SELECT
	id
FROM
	a 
INTERSECT
SELECT
	id
FROM
	b;

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;

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.id

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.