How to do Nested Joins or Intersect in MySQL

3 min readOct 17, 2021

Since you found this article, you should already know that these operations are not supported in MySQL. Stop short of migrating entirely over to another database, what can we do to achieve the same results we want?

Here are some tricks.

How to do Intersect in MySQL

Say we wanted to do INTERSECT in MySQL for 2 different queries and find the overlapping entries. If MySQL has supported INTERSECT we would do it as such:

SELECT * FROM table1
INTERSECT
   SELECT * FROM table2

But since it’s not supported, one of the workarounds to this is to use UNION ALL / GROUP BY as suggested by this post on Stackoverflow.

So we would change the query into this:

SELECT DISTINCT * FROM table1
UNION ALL
   SELECT DISTINCT * FROM table2
GROUP BY id
HAVING count(id) >= 2

So far so good…

But what if the 2 subqueries themselves are also a UNION ? Wouldn’t we hit the nested joins issue?

Yup! And here’s how we can work around it.

How to do Nested Joins in MySQL

Continuing with our example, say our original query is supposed to be as such:

(SELECT * FROM table1 WHERE condition = A
   UNION
   SELECT * FROM table2 WHERE condition = A)
INTERSECT
   (SELECT * FROM table1 WHERE condition = B
      UNION
         SELECT * FROM table2 WHERE condition = B)

To change this form into UNION ALL / GROUP BY, we would now hit the nested joins issue:

(SELECT * FROM table1 WHERE condition = A
   UNION
   SELECT * FROM table2 WHERE condition = A)
UNION ALL
   (SELECT * FROM table1 WHERE condition = B
     UNION
        SELECT * FROM table2 WHERE condition = B)
GROUP BY id
HAVING count(id) >= 2

In this particular case, we cannot flatten the nested joins as then the count(id) logics would be wrong. So what can we do?

To solve this, we can use WITH.

How to do Nested Joins with WITH in MySQL?

What is WITH? Think of it as a temporary table holding the results of your queries that allows for further querying. You can find a more professionally defined answer in the documentation.

The queries are quite intuitive. Here we would just convert our previous queries into this:

WITH
   q1 AS (SELECT * FROM table1 WHERE condition = A
      UNION
         SELECT * FROM table2 WHERE condition = A),
   q2 AS (SELECT * FROM table1 WHERE condition = B
      UNION
         SELECT * FROM table2 WHERE condition = B)SELECT DISTINCT * FROM q1
UNION ALL
   SELECT DISTINCT * FROM q2
GROUP BY id
HAVING count(id) >= 2

And there you have it!

Alternatively…

If you do these queries very frequently, you may consider creating a View in MySQL to store the first level UNIONs instead. In the end that was the approach I took.

I have not tested these queries performance wise. So it would be up to you to find out if either approach is more efficient in your use case. 🤓

Was it helpful?

If you find this article helpful, do give me a little clap on Medium. 😄 It really makes my day to know that I have helped a fellow developer out there. Thanks!