MySQL does not support full outer join out of the box, unlike other databases such as PostgreSQL, and SQL Server. So you will need to do a full outer join using a combination of other join types such as LEFT JOIN ad RIGHT JOIN that are supported in MySQL. In this article, we will look at how to a full outer join in MySQL.
How To Do a 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 | +------+------------+--------+
Also read : How to Get Row Number in MySQL
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
The above query will also return duplicate rows, if any. If you don’t want duplicate records in full outer join, use the following query instead.
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
Also read: How to get data for every hour in MySQL
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;
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!