Emulating Outer Joins In MySQL

Oct 24, 2022 by Robert Gravelle

Last week’s article shed some light on the Outer Joins in SELECT queries. It’s a JOIN type that returns both matched and unmatched rows from related tables. Unfortunately, it is not supported by all database (DB) vendors, including MySQL. But that’s OK, because Outer Joins can be emulated by combining three other JOIN types, namely LEFT, INNER, and RIGHT joins. In this article, we’ll learn more about LEFT and RIGHT joins and how, when combined with an INNER JOIN, they create an OUTER JOIN.

The LEFT JOIN returns all rows from the left table and the matching rows from the right table. If no matching rows are found in the right table, a NULL is returned. Here’s the syntax:

SELECT
    select_list
FROM
    T1
LEFT JOIN T2 ON
    join_predicate;

The following VEN diagram illustrates what data is fetched from two tables T1 and T2 using the LEFT JOIN clause:

The RIGHT JOIN returns all rows from the right table and the matching rows from the left table. If no matching rows are found in the left table, a NULL is returned. Here’s the syntax for that:

SELECT 
    select_list
FROM 
    T1
RIGHT JOIN T2 ON join_predicate;

The following VEN diagram illustrates what data is fetched from two tables T1 and T2 using the RIGHT JOIN clause:

It is common knowledge throughout the database community that MySQL lacks support for FULL OUTER JOIN. One common workaround to this short-coming is to use a UNION ALL to combine three result sets from a LEFT JOIN, an INNER JOIN, and a RIGHT JOIN of two tables, where a join_column IS NULL condition is added to the LEFT and RIGHT joins.

To demonstrate how to emulate an OUTER JOIN as described above, we’ll write a query against the same Project Management database as last week in the Understanding SQL Outer Joins article, but in MySQL this time.

Finding Unmatched Records in the Left Table

This first query will return rows that are found only in the left table. The query below achieves this effect by using LEFT join with a WHERE clause that specifies that the common (joining) column in the right table is null:

Finding Unmatched Records in the Second Table

The second query will return rows that are found only in the right table. To do that, we’ll use a RIGHT join with a WHERE clause that specifies that the common (joining) column in the left table is null:

Finding Matched Records in Both Tables

To find records that appear in both tables, we can use a standard (INNER) JOIN like the following:

When combined using UNION ALL, the three separate queries produce the same results as an OUTER JOIN:

In this article, we learned more about LEFT and RIGHT joins and how, when combined with an INNER JOIN, they create an OUTER JOIN. Like last week, there is again a caveat. This technique can be quite inefficient on large tables when used with ORDER BY and/or LIMIT queries, as these utilize a filesort. In such cases, you may want to employ another approach.