FULL OUTER JOIN in SQL

🔄 The FULL OUTER JOIN (often shortened to FULL JOIN) returns all rows from both tables. When a matching row exists in both tables, the data is combined into a single result. If no match exists, SQL returns NULL for the missing columns from the opposite table.

📖 What is a FULL OUTER JOIN?

A FULL OUTER JOIN combines the behavior of LEFT JOIN and RIGHT JOIN. It includes:

  • ✅ All matching rows from both tables.
  • ✅ All unmatched rows from the left table.
  • ✅ All unmatched rows from the right table.

Information

If there is no matching row in one table, the corresponding columns from that table contain NULL.

🎯 Why Use FULL OUTER JOIN?

Use a FULL OUTER JOIN when you need a complete view of data from both tables, including records that do not have matching values.

  • 📌 Compare two datasets.
  • 📌 Identify missing relationships.
  • 📌 Merge information from multiple sources.
  • 📌 Perform data auditing and validation.

📊 Sample Tables

Consider the following Students table:

StudentIDNameDepartment
101AliceComputer Science
102BobMathematics
103CharliePhysics
104DavidComputer Science

Consider the following Courses table:

CourseIDStudentIDCourseName
1101Database Systems
2101Operating Systems
3103Physics Lab
4104Computer Networks
5105Artificial Intelligence

📝 Basic Syntax

FULL OUTER JOIN Syntax

SELECT table1.column_name,
       table2.column_name
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

💡 Basic FULL OUTER JOIN Example

Retrieve every student and every course, whether they match or not.

Basic FULL OUTER JOIN

SELECT Students.Name,
       Courses.CourseName
FROM Students
FULL OUTER JOIN Courses
ON Students.StudentID = Courses.StudentID;

Result:

NameCourseName
AliceDatabase Systems
AliceOperating Systems
BobNULL
CharliePhysics Lab
DavidComputer Networks
NULLArtificial Intelligence

Important

Student Bob appears even though no course is assigned, and the course Artificial Intelligence appears even though no matching student exists.

🏷️ Using Table Aliases

Aliases improve readability and reduce typing in complex queries.

FULL OUTER JOIN with Aliases

SELECT s.Name,
       c.CourseName
FROM Students AS s
FULL OUTER JOIN Courses AS c
ON s.StudentID = c.StudentID;

🔍 Finding Unmatched Rows

You can use FULL OUTER JOIN together with IS NULL to identify records that exist in only one table.

Find Unmatched Records

SELECT s.Name,
       c.CourseName
FROM Students s
FULL OUTER JOIN Courses c
ON s.StudentID = c.StudentID
WHERE s.StudentID IS NULL
   OR c.StudentID IS NULL;

This query returns:

  • Students without any course.
  • Courses without any student.

📊 FULL OUTER JOIN with ORDER BY

Sort the combined results using the ORDER BY clause.

Sort FULL OUTER JOIN Results

SELECT s.Name,
       c.CourseName
FROM Students s
FULL OUTER JOIN Courses c
ON s.StudentID = c.StudentID
ORDER BY s.Name;

🔗 FULL OUTER JOIN Multiple Tables

You can combine multiple tables by performing additional joins.

FULL OUTER JOIN Three Tables

SELECT s.Name,
       c.CourseName,
       d.DepartmentName
FROM Students s
FULL OUTER JOIN Courses c
ON s.StudentID = c.StudentID
FULL OUTER JOIN Departments d
ON s.DepartmentID = d.DepartmentID;

📊 How FULL OUTER JOIN Works

Students TableCourses TableIncluded?
Matching StudentIDMatching StudentID✅ Yes
No Matching CourseNo Match✅ Yes (Course columns are NULL)
No Matching StudentCourse Exists✅ Yes (Student columns are NULL)

⚖️ Comparison of SQL Joins

Join TypeRows Returned
INNER JOINOnly matching rows.
LEFT JOINAll left rows and matching right rows.
RIGHT JOINAll right rows and matching left rows.
FULL OUTER JOINAll rows from both tables.

⚠️ Database Compatibility

Important

FULL OUTER JOIN is supported by databases such as PostgreSQL, SQL Server, and Oracle. However, MySQL and SQLite do not support it directly. In those databases, similar results can be achieved by combining a LEFT JOIN and a RIGHT JOIN (or two LEFT JOIN queries) with UNION.

🛠️ Simulating FULL OUTER JOIN in MySQL

FULL OUTER JOIN Alternative for MySQL

SELECT s.Name,
       c.CourseName
FROM Students s
LEFT JOIN Courses c
ON s.StudentID = c.StudentID

UNION

SELECT s.Name,
       c.CourseName
FROM Students s
RIGHT JOIN Courses c
ON s.StudentID = c.StudentID;

⚠️ Common Mistakes

  • ❌ Assuming every database supports FULL OUTER JOIN.
  • ❌ Forgetting to handle NULL values in unmatched rows.
  • ❌ Joining tables using incorrect columns.
  • ❌ Using = NULL instead of IS NULL.

Warning

Always check whether your database supports FULL OUTER JOIN. If not, use a combination of joins with UNION to achieve the same result.

💼 Real-World Example

A university administrator wants a report showing every student and every course, including students without enrollments and courses without assigned students.

Complete Enrollment Report

SELECT s.StudentID,
       s.Name,
       c.CourseName
FROM Students s
FULL OUTER JOIN Courses c
ON s.StudentID = c.StudentID
ORDER BY s.StudentID;

This query provides a complete picture of student-course relationships, including missing or unmatched records.

⚠️ Best Practices

Best Practice

Use FULL OUTER JOIN when you need all records from both tables. Handle NULL values appropriately, use table aliases for readability, join on related keys, and verify database support before using this join type in production.

🚀 Key Points to Remember

  • 📌 FULL OUTER JOIN returns all rows from both tables.
  • 📌 Matching rows are merged into a single result.
  • 📌 Unmatched rows contain NULL for missing columns.
  • 📌 It combines the behavior of LEFT JOIN and RIGHT JOIN.
  • 📌 It is useful for comparing datasets and finding missing relationships.
  • 📌 Some databases require a UNION-based alternative.
>>"A FULL OUTER JOIN leaves nothing behind—it includes every record from both tables, matched or unmatched."

Summary

✅ The FULL OUTER JOIN is a powerful SQL join that returns every row from both tables, regardless of whether a match exists. It is ideal for complete data analysis, reconciliation, auditing, and identifying unmatched records across related datasets.