In MariaDB, the INTERSECT operator intersects two queries and returns only those rows that are returned in both queries.

It returns all rows from the left SELECT result set that are also present in the right SELECT result set.

Syntax

The official syntax goes like this:

SELECT ...
(INTERSECT [ALL | DISTINCT] | EXCEPT [ALL | DISTINCT] | UNION [ALL | DISTINCT]) SELECT ...
[(INTERSECT [ALL | DISTINCT] | EXCEPT [ALL | DISTINCT] | UNION [ALL | DISTINCT]) SELECT ...]
[ORDER BY [column [, column ...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

The above also includes the EXCEPT and UNION operators in the syntax, as the same syntax applies to those operators.

From MariaDB 10.4.0, parentheses can be used to specify precedence.

Example

Suppose we have the following tables:

SELECT * FROM Employees;
SELECT * FROM Customers;

Result:

+------------+--------------+
| EmployeeId | EmployeeName |
+------------+--------------+
|          1 | Bart         |
|          2 | Jan          |
|          3 | Ava          |
|          4 | Rohit        |
|          5 | Monish       |
|          6 | Monish       |
|          7 | Monish       |
+------------+--------------+

+------------+--------------+
| CustomerId | CustomerName |
+------------+--------------+
|          1 | Mia          |
|          2 | Rohit        |
|          3 | Peter        |
|          4 | Ava          |
|          5 | Monish       |
|          6 | Monish       |
+------------+--------------+

We can use the INTERSECT operator to return employees that are also customers:

SELECT EmployeeName FROM Employees
INTERSECT
SELECT CustomerName FROM Customers;

Result:

+--------------+
| EmployeeName |
+--------------+
| Ava          |
| Rohit        |
| Monish       |
+--------------+

So we only get values that appear in the Employees table that also appear in the Customers table.

By default, it returns distinct rows, so only one row is returned for Monish, even though there are multiple employees and multiple customers with that name. We can change this though.

Include Duplicates

By default, the INTERSECT operator implicitly applies a DISTINCT operation. In other words, it returns only distinct values by default.

Prior to MariaDB 10.5.0, the implicit DISTINCT was our only option – we were unable to specify ALL. However, MariaDB 10.5.0 introduced the INTERSECT ALL and INTERSECT DISTINCT syntax.

This means that we can now do queries like this:

SELECT EmployeeName FROM Employees
INTERSECT ALL
SELECT CustomerName FROM Customers;

Result:

+--------------+
| EmployeeName |
+--------------+
| Monish       |
| Ava          |
| Rohit        |
| Monish       |
+--------------+

This time we got four rows, instead of the three that we got in our first example.

We can see that two rows containing the name Monish were returned instead of just one like in our first example. There are actually three customers called Monish, but only two employees with that name. Therefore, the operation only intersects two of them.

And for the sake of completeness, here’s an example that explicitly uses the DISTINCT operator:

SELECT EmployeeName FROM Employees
INTERSECT DISTINCT
SELECT CustomerName FROM Customers;

Result:

+--------------+
| EmployeeName |
+--------------+
| Ava          |
| Rohit        |
| Monish       |
+--------------+

This is the same result that we would get if we were to remove the DISTINCT operator.

An Alternative Query

It’s possible to get the same result without using the INTERSECT operator. For example, we could rewrite our first example to this:

SELECT 
    DISTINCT EmployeeName
FROM Employees e 
WHERE EXISTS (SELECT CustomerName FROM Customers c
WHERE e.EmployeeName = c.CustomerName);

Result:

+--------------+
| EmployeeName |
+--------------+
| Ava          |
| Rohit        |
| Monish       |
+--------------+

Mind you, the INTERSECT operator helps to simplify the code.