MySQL Intersect Except: Common and Different Rows (8.0.31+)

Besides UNION, standard SQL has two more set operators. MySQL supports them since version 8.0.31:

INTERSECT: the rows that appear in both results.
EXCEPT: the rows in the first result that are not in the second.

query.sql

SQL

CREATE TABLE newsletter (email VARCHAR(40));
CREATE TABLE buyers (email VARCHAR(40));
INSERT INTO newsletter VALUES ('asha@example.com'), ('ben@example.com'), ('chitra@example.com'), ('dev@example.com');
INSERT INTO buyers VALUES ('ben@example.com'), ('dev@example.com'), ('esha@example.com');

SELECT email FROM newsletter
INTERSECT
SELECT email FROM buyers
ORDER BY email;

Output

OUTPUT

+-----------------+
| email           |
+-----------------+
| ben@example.com |
| dev@example.com |
+-----------------+
2 rows in set

These people are on the newsletter list and have bought something.

query.sql

SQL

SELECT email FROM newsletter
EXCEPT
SELECT email FROM buyers
ORDER BY email;

Output

OUTPUT

+--------------------+
| email              |
+--------------------+
| asha@example.com   |
| chitra@example.com |
+--------------------+
2 rows in set

These subscribers have not bought yet: good candidates for a discount mail. Order matters for EXCEPT. Swap the queries and you get the buyers who are not subscribed:

query.sql

SQL

1
2
3

SELECT email FROM buyers
EXCEPT
SELECT email FROM newsletter;

Output

OUTPUT

+------------------+
| email            |
+------------------+
| esha@example.com |
+------------------+
1 row in set

ALL

Like UNION, these operators remove duplicates by default (DISTINCT). Add ALL to keep duplicates:

query.sql

SQL

CREATE TABLE a (v INT);
CREATE TABLE b (v INT);
INSERT INTO a VALUES (1), (1), (1), (2);
INSERT INTO b VALUES (1), (1), (3);

SELECT v FROM a INTERSECT ALL SELECT v FROM b;
SELECT v FROM a EXCEPT ALL SELECT v FROM b;

Output

OUTPUT

+------+
| v    |
+------+
|    1 |
|    1 |
+------+
2 rows in set

+------+
| v    |
+------+
|    1 |
|    2 |
+------+
2 rows in set

INTERSECT ALL keeps 1 two times (the smaller count of the two tables). EXCEPT ALL subtracts counts: three 1s minus two leaves one, and 2 stays.

Precedence

INTERSECT binds tighter than UNION and EXCEPT, as in the standard. Use brackets when you mix operators.

On older versions: alternatives

Before 8.0.31 (and in MariaDB before 10.3), write them with joins or EXISTS:

query.sql

SQL

1
2

SELECT DISTINCT n.email FROM newsletter n WHERE EXISTS (SELECT 1 FROM buyers b WHERE b.email = n.email) ORDER BY n.email;
SELECT DISTINCT n.email FROM newsletter n WHERE NOT EXISTS (SELECT 1 FROM buyers b WHERE b.email = n.email) ORDER BY n.email;

Output

OUTPUT

+-----------------+
| email           |
+-----------------+
| ben@example.com |
| dev@example.com |
+-----------------+
2 rows in set

+--------------------+
| email              |
+--------------------+
| asha@example.com   |
| chitra@example.com |
+--------------------+
2 rows in set

The first query is the INTERSECT and the second is the EXCEPT. These forms work on every MySQL version and treat NULL in a specific way: NULL = NULL is not true, so NULL values never match. INTERSECT and EXCEPT treat two NULLs as equal. Keep that in mind if the columns may be NULL.

Which to choose

On MySQL 8.0.31 or newer, use INTERSECT and EXCEPT when they say what you mean. They read like the question.
For code that must run on older servers, use EXISTS and NOT EXISTS (EXISTS).

info

Check your version

SELECT VERSION(); tells you if you have 8.0.31 or newer. On older versions you get a syntax error near INTERSECT.