MySQL - FULL OUTER JOIN

How to do a FULL OUTER JOIN in MySQL?

MySQL does not support FULL JOIN, so you have to combine RIGHT JOIN, UNION, and LEFT JOIN

SELECT
	*
FROM
	table_a
	LEFT JOIN table_b ON table_a.id = table_b.id
UNION
SELECT
	*
FROM
	table_a
	RIGHT JOIN table_b ON table_a.id = table_b.id;

Example

We have table city1:

id	city_name
1	New York
3	Boston

And table city2:

id	city_name
1	New York
2	Chicago

Now emulate the FULL OUTER JOIN:

SELECT
	*
FROM
	city1
	LEFT JOIN city2 ON city1.id = city2.id
UNION
SELECT
	*
FROM
	city1
	RIGHT JOIN city2 ON city1.id = city2.id;

The result is:

id	city_name	id	city_name
1	New York	1	New York
3	Boston
		2	Chicago

UNION already removed the dupplicates, but if you want to also include the duplicates, use UNION ALL:

SELECT
	*
FROM
	city1
	LEFT JOIN city2 ON city1.id = city2.id
UNION ALL
SELECT
	*
FROM
	city1
	RIGHT JOIN city2 ON city1.id = city2.id;

The result now is:

id	city_name	id	city_name
1	New York	1	New York
3	Boston
1	New York	1	New York
		2	Chicago

---

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.


Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.