MySQL INTERSECT

Summary: in this tutorial, you will learn about the MySQL INTERSECT operator and how to use it to find the common rows of multiple queries.

Notice that MySQL supported INTERSECT operator in MySQL 8.0.31.

Introduction to the MySQL INTERSECT operator

The INTERSECT operator is a set operator that returns the common rows of two or more queries.

Here’s the syntax of the INTERSECT operator:

query1
INTERSECT [ALL | DISTINCT]
query2;Code language: SQL (Structured Query Language) (sql)

The INTERSECT operator compares the result sets of two queries and returns the common rows.

To use the INTERSECT operator for the queries, follow these rules:

1. The order and the number of columns in the select list of the queries must be the same.
2. The data types of the corresponding columns must be compatible.

The INTERSECT operator uses the DISTINCT by default. This means that the DISTINCT removes duplicates from either side of the intersection. If you want to retain duplicates, you explicitly specify the ALL option.

The following diagram illustrates the INTERSECT operator.

In this diagram, the left query produces a result set of (1,2,3) and the right query returns a result set of (2,3,4). The INTERSECT operator returns the common rows, which in this case are (2,3).

Unlike the UNION operator, which combines the rows of two queries, the INTERSECT operator returns the common rows between queries.

MySQL INTERSECT operator examples

Let’s take an example of using the INTERSECT operator.

Setting up sample tables

First, create tables t1 and t2, and insert data into both tables:

CREATE TABLE t1 (
    id INT PRIMARY KEY
);

CREATE TABLE t2 LIKE t1;

INSERT INTO t1(id) VALUES(1),(2),(3);

INSERT INTO t2(id) VALUES(2),(3),(4);Code language: SQL (Structured Query Language) (sql)

Second, query the data from the t1 table:

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

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

Third, query the data from the t2 table:

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

+----+
| id |
+----+
|  2 |
|  3 |
|  4 |
+----+

1) MySQL INTERSECT operator example

The following example uses the INTERSECT operator to get a result set that contains the rows common to both t1 and t2 tables:

SELECT id FROM t1
INTERSECT
SELECT id FROM t2;Code language: SQL (Structured Query Language) (sql)

+----+
| id |
+----+
|  2 |
|  3 |
+----+

The query is equivalent to the following query that uses the DISTINCT explicitly:

SELECT id FROM t1
INTERSECT DISTINCT
SELECT id FROM t2;Code language: SQL (Structured Query Language) (sql)

2) A practical MySQL INTERSECT example

We’ll use the customers and employees tables from the sample database for the demonstration:

The following query uses the INTERSECT operator to find the common first names of customers and employees:

SELECT 
    firstName
FROM
    employees 
INTERSECT
SELECT 
    contactFirstname
FROM
    customers;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------+
| firstName |
+-----------+
| Mary      |
| Jeff      |
| William   |
| Leslie    |
| Julie     |
| Steve     |
| Peter     |
| Martín    |
+-----------+

3) Using INTERSECT operator with ORDER BY clause

To sort the result set returned by the INTERSECT operator, you use the ORDER BY clause. MySQL uses the columns of the first query as the column name of the result set.

For example, the following query uses the INTERSECT operator to find the common first names of employees and customers and sort them in ascending order:

SELECT 
    firstName
FROM
    employees 
INTERSECT
SELECT 
    contactFirstname
FROM
    customers
ORDER BY firstName;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------+
| firstName |
+-----------+
| Jeff      |
| Julie     |
| Leslie    |
| Martín    |
| Mary      |
| Peter     |
| Steve     |
| William   |
+-----------+

4) Using INTERSECT operator with ALL option example

The following example uses the INTERSECT operator with the ALL option that finds the common first names of employees and customers and retains the duplicates:

SELECT 
    firstName
FROM
    employees 
INTERSECT ALL
SELECT 
    contactFirstname
FROM
    customers
ORDER BY firstName;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------+
| firstName |
+-----------+
| Jeff      |
| Julie     |
| Leslie    |
| Leslie    |
| Martín    |
| Mary      |
| Peter     |
| Steve     |
| William   |
+-----------+

Summary

• Use the MySQL INTERSECT operator to find the rows that are common to multiple query results.
• Use INTERSECT DISTINCT to remove the duplicates from the result sets and INTERSECT ALL to retain the duplicates.
• The INTERSECT operator uses DISTINCT by default.