How to do a full outer join in mysql

Question

I want to do a Full Outer Join in MySQL. Is this possible? Is a Full Outer Join supported by MySQL?

Answer 1

The full outer joins help to combine the results of both the right and the left outer join, which means that the resulted table contains all the records from both the table and invoking nulls to the records that have missing values.

Let’s have look at the structure of full outer join and how it is implemented in Mysql:

Consider two table  Employ and Student:

SELECT * FROM Employ

LEFT JOIN Student ON Employ.age = Student.age

UNION

SELECT * FROM Employ

RIGHT JOIN Student ON Employ.age = Student.age

The query will give you a result implementing full join and provide the joined table with distinct (does not contain duplicate value)records.

If you change the UNION keyword with UNION ALL, then it will return all the records including duplicate values.

Interested in SQL Server? Here is the SQL Training provided by Intellipaat.