FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both the left and right tables.

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

Where no match exists, missing values are filled 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;

This type of join is the union 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

This result includes matched rows, unmatched students, and unmatched classes.

Why It Matters

Use FULL OUTER JOIN when:

• You need a complete dataset from both tables
• You're checking for missing relationships
• You want 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 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