Intersect and Except in MySQL 8.0

With the latest MySQL release (8.0.31), MySQL adds support for the SQL standard INTERSECT and EXCEPT table operators.

Let’s have a look how to use them.

We will use the following table:

CREATE TABLE `new` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `tacos` int DEFAULT NULL,
  `sushis` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

For our team meeting, we will order tacos and sushi’s.

Each record represent the order of each team member:

select * from new;
+----+-------------+-------+--------+
| id | name        | tacos | sushis |
+----+-------------+-------+--------+
|  1 | Kenny       |  NULL |     10 |
|  2 | Miguel      |     5 |      0 |
|  3 | lefred      |     4 |      5 |
|  4 | Kajiyamasan |  NULL |     10 |
|  5 | Scott       |    10 |   NULL |
|  6 | Lenka       |  NULL |   NULL |
+----+-------------+-------+--------+

Intersect

The manual says that INTERSECT limits the result from multiple SELECT statements to those rows which are common to all. INTERSECT operator is part of the ANSI/ISO SQL standard (ISO/IEC 9075-2:2016(E))

We want to run two queries, the first one will list all records where the team member chose tacos and the second one will return all records where the person chose sushi’s.

The two separate queries are:

(query 1) select * from new where tacos>0;

(query 2) select * from new where sushis>0;

The only record that is present in both results is the one with id=3.

Let’s use INTERSECT to confirm that:

select * from new where tacos > 0 
intersect 
select * from new where sushis > 0;
+----+--------+-------+--------+
| id | name   | tacos | sushis |
+----+--------+-------+--------+
|  3 | lefred |     4 |      5 |
+----+--------+-------+--------+

Excellent, on previous versions of MySQL, the result of such query would have been:

ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near 
'intersect select * from new where sushis > 0' at line 1

Except

In the manual, we can read that EXCEPT limits the result from the first SELECT statement to those rows which are (also) not found in the second.

Let’s find out all team members that will only eat tacos using EXCEPT:

select * from new where tacos > 0 
except 
select * from new where sushis > 0;
+----+--------+-------+--------+
| id | name   | tacos | sushis |
+----+--------+-------+--------+
|  2 | Miguel |     5 |      0 |
|  5 | Scott  |    10 |   NULL |
+----+--------+-------+--------+

And if we want to perform the reverse and get all those that will only eat sushi’s we inverse the queries order like this:

select * from new where sushis > 0 
except 
select * from new where tacos > 0;
+----+-------------+-------+--------+
| id | name        | tacos | sushis |
+----+-------------+-------+--------+
|  1 | Kenny       |  NULL |     10 |
|  4 | Kajiyamasan |  NULL |     10 |
+----+-------------+-------+--------+

Conclusion

MySQL 8.0.31 continues the legacy of 8.0 to include support for SQL standards such as Window Functions, Common Table Expressions, Lateral Derived Tables, JSON_TABLES, JSON_VALUE, …

Enjoy MySQL !