FULL OUTER JOIN

FULL OUTER JOIN is a join that combines and returns all data from both the left and right tables.

• LEFT JOIN returns all rows from the left table along with matching rows from the right table.

• RIGHT JOIN returns all rows from the right table along with matching rows from the left table.

• Combining both and removing duplicates gives us a FULL OUTER JOIN.

In other words, it is similar to the union of the two tables.

Union:

How to Implement FULL OUTER JOIN

Some DBMSs (such as Oracle and PostgreSQL) provide the FULL OUTER JOIN keyword directly:

-- FULL OUTER JOIN supported DB
SELECT *
FROM computer
FULL OUTER JOIN software
ON computer.software_id = software.software_id;

However, in MySQL, which does not support FULL OUTER JOIN natively, you can implement it using a combination of LEFT JOIN and RIGHT JOIN with a UNION:

-- FULL OUTER JOIN in MySQL
SELECT *
FROM computer
LEFT JOIN software
ON computer.software_id = software.software_id

UNION

SELECT *
FROM computer
RIGHT JOIN software
ON computer.software_id = software.software_id;

---

Example

Here is an example using the computer and software tables.

1. LEFT JOIN + RIGHT JOIN result

   Combining both joins shows all data, but duplicate rows also appear.

2. After removing duplicates

   The final result is the FULL OUTER JOIN.

---

Summary

• FULL OUTER JOIN combines and returns all rows from both tables.

• It is equivalent to merging LEFT JOIN and RIGHT JOIN.

• MySQL does not support the FULL OUTER JOIN keyword, so it must be implemented with UNION.

• In practice, it is used less frequently than INNER JOIN or LEFT JOIN, but it can be very useful when all data from both tables needs to be reviewed.

👉Check out the JOIN SQL queries example here

👉Check out the spreadsheet JOIN example here