Using INTERSECT and EXCEPT in MySQL 8.0.31 and Workarounds for Earlier Versions

MySQL 8.0.31 adds support for the standard SQL set operators INTERSECT (to return the common rows of two result sets) and EXCEPT (to return rows from the left set that are not present in the right set).

Before this version, MySQL did not provide these operators, so developers had to emulate the functionality with joins.

Below is a simple example that creates two tables t1 and t2 and inserts sample data:

<mysql:5.7.34:(ytt)> create table t1(c1 int);
Query OK, 0 rows affected (0.02 sec)
<mysql:5.7.34:(ytt)> create table t2 like t1;
Query OK, 0 rows affected (0.02 sec)
<mysql:5.7.34:(ytt)> insert t1 values (10),(20),(20),(30),(40),(40),(50);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0
<mysql:5.7.34:(ytt)> insert t2 values (10),(30),(30),(50),(50),(70),(90);
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0
<mysql:5.7.34:(ytt)> select * from t1;
+------+
| c1   |
+------+
|   10 |
|   20 |
|   20 |
|   30 |
|   40 |
|   40 |
|   50 |
+------+
7 rows in set (0.00 sec)
<mysql:5.7.34:(ytt)> select * from t2;
+------+
| c1   |
+------+
|   10 |
|   30 |
|   30 |
|   50 |
|   50 |
|   70 |
|   90 |
+------+
7 rows in set (0.00 sec)

To obtain the distinct intersection in MySQL 5.7, use an inner join and DISTINCT :

<mysql:5.7.34:(ytt)> select distinct t1.c1 from t1 join t2 using(c1);
+------+
| c1   |
+------+
|   10 |
|   30 |
|   50 |
+------+
3 rows in set (0.00 sec)

To obtain the distinct difference (rows in t1 that are not in t2 ), use a left join with a NULL filter:

<mysql:5.7.34:(ytt)> select distinct t1.c1 from t1 left join t2 using(c1) where t2.c1 is null;
+------+
| c1   |
+------+
|   20 |
|   40 |
+------+
2 rows in set (0.00 sec)

With MySQL 8.0.31, the same tasks become much simpler using the new operators:

Intersection:

<mysql:8.0.31:(ytt)> table t1 intersect table t2;
+------+
| c1   |
+------+
|   10 |
|   30 |
|   50 |
+------+
3 rows in set (0.00 sec)

Difference:

<mysql:8.0.31:(ytt)> table t1 except table t2;
+------+
| c1   |
+------+
|   20 |
|   40 |
+------+
2 rows in set (0.00 sec)

Both operators remove duplicates by default. If you need to keep all matching rows, add the ALL keyword:

<mysql:8.0.31:(ytt)> table t1 except all table t2;
+------+
| c1   |
+------+
|   20 |
|   20 |
|   40 |
|   40 |
+------+
4 rows in set (0.00 sec)

The article also briefly mentions the open‑source SQL audit tool SQLE from the 爱可生 community, which supports MySQL and can be used for database governance.