MySQL 8.0.31 added INTERSECT and EXCEPT to augment the long-lived UNION operator. That is the good news. The bad news is that you have to be careful using the EXCEPT operator as there is a trick.
Let’s start with some simple tables and load some simple data.
1 2 3 4 5 6 7 8 9 10 11 12 | SQL > create table a (id int, nbr int); Query OK, 0 rows affected (0.0180 sec) SQL > create table b (id int, nbr int); Query OK, 0 rows affected (0.0199 sec) SQL > insert into a (id,nbr) values (1,10),(3,30),(5,50),(7,70); Query OK, 4 rows affected (0.0076 sec) Records: 4 Duplicates: 0 Warnings: 0 SQL > insert into b (id,nbr) values (1,10),(2,20),(3,30),(4,40); Query OK, 4 rows affected (0.0159 sec) Records: 4 Duplicates: 0 Warnings: 0 |
So each table has four rows of data with two rows – (1,10) and (3,30) – appearing in both.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL > select id,nbr from a; +----+-----+ | id | nbr | +----+-----+ | 1 | 10 | | 3 | 30 | | 5 | 50 | | 7 | 70 | +----+-----+ 4 rows in set (0.0011 sec) SQL > select id,nbr from b; +----+-----+ | id | nbr | +----+-----+ | 1 | 10 | | 2 | 20 | | 3 | 30 | | 4 | 40 | +----+-----+ 4 rows in set (0.0010 sec) |
Since the two tables have identical structures — both have the same column id and nbr — we can use UNION to combine the contents of both. The two rows that are identical are not duplicated in the results which is why we go from two tables with four rows each to one table of six rows. If you need the duplicates to be displayed, use UNION ALL instead of UNION. UNION DISTINCT is the default choice and you do not have to specify the DISTINCT.
1 2 3 4 5 6 7 8 9 10 11 12 | SQL > select * from a union select * from b; +----+-----+ | id | nbr | +----+-----+ | 1 | 10 | | 3 | 30 | | 5 | 50 | | 7 | 70 | | 2 | 20 | | 4 | 40 | +----+-----+ 6 rows in set (0.0010 sec) |
If we only need the rows that are in common in the two tables then we can use INTERSECT to find them.
1 2 3 4 5 6 7 8 | SQL > select * from a intersect select * from b; +----+-----+ | id | nbr | +----+-----+ | 1 | 10 | | 3 | 30 | +----+-----+ 2 rows in set (0.0010 sec) |
Bonus: try the above and switch the order of the table names to see if there is a difference in the output. The answer is below.
What if we want the data from one of the tables that is not in the other table? Well, here we have to be careful. If we want the data from table B that is not in table A, we use EXCEPT and phrase it in that order of the columns.
1 2 3 4 5 6 7 8 | SQL > select * from b except select * from a; +----+-----+ | id | nbr | +----+-----+ | 2 | 20 | | 4 | 40 | +----+-----+ 2 rows in set (0.0012 sec) |
And to get only the values from a table that are not in table B, we need to be explicit in ordering the tables.
1 2 3 4 5 6 7 8 | SQL > select * from a except select * from b; +----+-----+ | id | nbr | +----+-----+ | 5 | 50 | | 7 | 70 | +----+-----+ 2 rows in set (0.0011 sec) |
And be careful because if we exclude the values in table A that are in table A, we will get an empty set. If this seems to be a contrived example that could never happen, please be advised that this type of logic bomb does explode all the time. This is a syntactically valid query even if the logic is not up to par.
1 2 | SQL > select * from a except select * from a; Empty set (0.0015 sec) |
Ideally, I would like to put parens around the two select statements when using UNION, INTERSECT, and EXCEPT, as seen in the following example, to make the query more explicit.
1 2 3 4 5 6 7 8 9 10 11 12 | SQL > (select * from b order by id) union (select * from a order by id); +----+-----+ | id | nbr | +----+-----+ | 1 | 10 | | 2 | 20 | | 3 | 30 | | 4 | 40 | | 5 | 50 | | 7 | 70 | +----+-----+ 6 rows in set (0.0010 sec) |
You especially want to use the parens if you need a transaction or really want to make sure the parser knows what you desire to do with your query.
Bonus answer: The UNION operator does not care if we match A with B or B with A.
It takes a little time for the Percona engineers to get all the enterprise-level features Percona Server for MySQL is known for after a new release is made (Set Theory in MySQL 8.0: UNION and Now INTERSECT and EXCEPT) so please be patient for release 8.0.31 to appear on the Percona repos. It is on the way!
And be sure to read Set Theory in MySQL for more examples plus a guide for using these operators in CTEs.
