How To Do Full Outer Join in MySQL

Last updated on June 11th, 2024 at 10:21 am

MySQL is a popular database system used by many database programmers and software developers. It provides several simple ways to easily retrieve data from multiple tables using JOINS. MySQL supports INNER JOIN, LEFT JOIN and RIGHT JOIN. But it does not support full outer join out of the box, unlike other databases such as PostgreSQL, and SQL Server. Often database programmers need to get query result based on FULL OUTER JOIN. So we will need to devise a work around for this. In this article, we will look at how to a full outer join in MySQL.

What is Full Outer Join

Full Outer Join (also called Full Join) returns all records when there is a match in left or right table. If there is no match, then the result will contain Null values for the columns of the table that does not have a match. If you imagine two tables as two mathematical sets, a full outer join is a union of the two sets. As per SQL standards, every database system must support Full Outer Join, in addition to Inner Join, Left Join and Right Join. But MySQL does not support Full Outer Join. So you will need to do a full outer join using a combination of other join types such as LEFT JOIN and RIGHT JOIN, that are supported in MySQL.

How To Do Full Outer Join in MySQL

Here are the steps to do a full outer join in MySQL. Let us say you have the following two tables sales and orders.

mysql> select * from sales;
 +------+---------------------+--------+
 | id   | order_date          | amount |
 +------+---------------------+--------+
 |    1 | 2021-02-02 08:15:00 |    250 |
 |    2 | 2021-02-02 08:30:00 |    200 |
 |    3 | 2021-02-02 08:55:00 |    150 |
 |    4 | 2021-02-02 09:15:00 |    125 |
 |    5 | 2021-02-02 09:30:00 |    250 |
 |    6 | 2021-02-02 09:45:00 |    200 |
 |    7 | 2021-02-02 10:15:00 |    180 |
 |    8 | 2021-02-02 10:30:00 |    125 |
 |    9 | 2021-02-02 10:45:00 |    200 |
 |   10 | 2021-02-02 11:15:00 |    250 |
 |   11 | 2021-02-02 11:30:00 |    150 |
 |   12 | 2021-02-02 11:45:00 |    200 |
 +------+---------------------+--------+

 mysql> select * from orders;
 +------+------------+--------+
 | id   | order_date | amount |
 +------+------------+--------+
 |    5 | 2021-01-28 |    250 |
 |    6 | 2021-01-29 |    250 |
 |    7 | 2021-01-30 |    250 |
 |    8 | 2021-01-31 |    250 |
 |    9 | 2021-02-01 |    250 |
 +------+------------+--------+

Full Join With Duplicates

Here is the general syntax to do a full outer join in MySQL between tables t1 and t2 based on JOIN field id. You can update the table names and join field as per your requirement.

SELECT * FROM t1 
LEFT JOIN t2 ON t1.id = t2.id 
UNION ALL 
SELECT * FROM t1 
RIGHT JOIN t2 ON t1.id = t2.id 
WHERE t1.id IS NULL

In the above query, the LEFT JOIN returns rows from the left table and matching rows from right table. The RIGHT JOIN will return all rows from right table and matching rows from left table. The result of right join is appended to that of left join using UNION ALL statement.

The above query will also return duplicate rows, if any, since UNION ALL returns duplicates also.

Full Join Without Duplicates

If you don’t want duplicate records in full outer join, use JOIN instead of using JOIN ALL, in the above query.

SELECT * FROM t1 
LEFT JOIN t2 ON t1.id = t2.id 
UNION 
SELECT * FROM t1 
RIGHT JOIN t2 ON t1.id = t2.id

Here is the SQL query to do a full outer join between tables sales and orders.

mysql> SELECT * FROM sales
       LEFT JOIN orders ON sales.id = orders.id
       UNION ALL
       SELECT * FROM sales
       RIGHT JOIN orders ON sales.id = orders.id
       WHERE sales.id IS NULL ;
 +------+---------------------+--------+------+------------+--------+
 | id   | order_date          | amount | id   | order_date | amount |
 +------+---------------------+--------+------+------------+--------+
 |    5 | 2021-02-02 09:30:00 |    250 |    5 | 2021-01-28 |    250 |
 |    6 | 2021-02-02 09:45:00 |    200 |    6 | 2021-01-29 |    250 |
 |    7 | 2021-02-02 10:15:00 |    180 |    7 | 2021-01-30 |    250 |
 |    8 | 2021-02-02 10:30:00 |    125 |    8 | 2021-01-31 |    250 |
 |    9 | 2021-02-02 10:45:00 |    200 |    9 | 2021-02-01 |    250 |
 |    1 | 2021-02-02 08:15:00 |    250 | NULL | NULL       |   NULL |
 |    2 | 2021-02-02 08:30:00 |    200 | NULL | NULL       |   NULL |
 |    3 | 2021-02-02 08:55:00 |    150 | NULL | NULL       |   NULL |
 |    4 | 2021-02-02 09:15:00 |    125 | NULL | NULL       |   NULL |
 |   10 | 2021-02-02 11:15:00 |    250 | NULL | NULL       |   NULL |
 |   11 | 2021-02-02 11:30:00 |    150 | NULL | NULL       |   NULL |
 |   12 | 2021-02-02 11:45:00 |    200 | NULL | NULL       |   NULL |
 +------+---------------------+--------+------+------------+--------+

Here is the SQL query to do full outer join without any duplicate rows in result.

mysql> SELECT * FROM sales
       LEFT JOIN orders ON sales.id = orders.id
       UNION
       SELECT * FROM sales
       RIGHT JOIN orders ON sales.id = orders.id;

Conclusion

In this article, we have learnt how to do Full Joins in MySQL. Since MySQL does not support them out of the box, we need to use a combination of LEFT JOIN and RIGHT JOIN to obtain the same result as you would with a FULL JOIN, in other databases. The key is to remember to use UNION statement if you do not want duplicates in your result and use UNION ALL, if you want them.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Also read :
How to Get Row Number in MySQL
How to get data for every hour in MySQL
How to Get Last 1 Hour Data in MySQL
How to Show Rows Not Present in Other Table