SQL INTERSECT

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

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

SELECT
    id
FROM
    A;

And the following statement retrieves the data from the 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.