Full Outer Join in MySQL

A FULL OUTER JOIN returns all rows from both tables, including matching and non-matching rows.

• Returns all records from both tables.
• Returns matching records from both tables.
• Shows NULL for unmatched records.
• Useful for finding matched and unmatched records between two tables.

Syntax

MySQL does not directly support FULL OUTER JOIN. It can be achieved using LEFT JOIN, RIGHT JOIN and UNION.

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name

UNION

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example

Consider the following two tables: Students and Courses.

Students Table:

Courses Table:

FULL OUTER JOIN using UNION

SELECT Students.student_name,
       Students.course_id,
       Courses.course_name
FROM Students
LEFT JOIN Courses
ON Students.course_id = Courses.course_id

UNION

SELECT Students.student_name,
       Students.course_id,
       Courses.course_name
FROM Students
RIGHT JOIN Courses
ON Students.course_id = Courses.course_id;

Output:

• The LEFT JOIN returns all students, while the RIGHT JOIN returns all courses. UNION combines both results, giving the effect of a FULL OUTER JOIN in MySQL.