FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both the left and right tables, combining them into a single result set.

Where there is a match, the result combines the rows.

Where no match exists, the missing values are represented with NULL.

Syntax

Here is the syntax for a FULL OUTER JOIN:

FULL OUTER JOIN Syntax

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

Conceptually, this join behaves like a combination of LEFT JOIN and RIGHT JOIN.

Example: Students and Enrollments

Below is an example of a FULL OUTER JOIN between the students and enrollments tables.

students

student_id	name
1	John Miller
2	Lisa Brown
3	Michael Johnson
4	Emily Davis
5	Sarah Thompson

enrollments

student_id	class_name
1	Math
1	Science
2	History
3	Art

FULL OUTER JOIN example

SELECT students.name, enrollments.class_name
FROM students
FULL OUTER JOIN enrollments
ON students.student_id = enrollments.student_id;

Result:

name	class_name
John Miller	Math
John Miller	Science
Lisa Brown	History
Emily Davis	NULL
NULL	Art

The result set includes matched rows as well as unmatched students and classes.

When is FULL OUTER JOIN the right choice?

Use FULL OUTER JOIN when:

• You need a complete dataset from both tables
• You're checking for missing relationships
• You need to audit or reconcile records across tables

Keep in mind: SQLite and MySQL do not support FULL OUTER JOIN directly. You can emulate it using a UNION of a LEFT JOIN and a RIGHT JOIN.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In