MySQL: Using UNION, INTERSECT, & EXCEPTMySQL 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.

So each table has four rows of data with two rows – (1,10) and (3,30) – appearing in both.

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.

If we only need the rows that are in common in the two tables then we can use INTERSECT to find them.

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.

And to get only the values from a table that are not in table B, we need to be explicit in ordering the tables.

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.

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.

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.

Notify of

Inline Feedbacks
View all comments