SQL FULL OUTER JOIN

8
0

Introduction

Combining related tables often requires more than retrieving matching records. A report may also need records that exist in one table without a corresponding record in the other.

A FULL OUTER JOIN includes both matching and unmatched records. This makes it useful for complete reports, comparing datasets, and identifying missing relationships between tables.

What Is a FULL OUTER JOIN?

A FULL OUTER JOIN combines rows that satisfy a join condition and preserves unmatched rows from both tables.

Its result contains:

  • Matching rows: Related values from both tables appear together.

  • Unmatched left rows: Values from the left table appear with NULL in the right table’s columns.

  • Unmatched right rows: Values from the right table appear with NULL in the left table’s columns.

Here, NULL indicates a missing value. The join does not change the stored records; it produces a result table.

PostgreSQL supports FULL OUTER JOIN directly. The shorter form, FULL JOIN, has the same meaning. MySQL 8.4 does not provide this join directly, but an equivalent result can be produced using outer joins and UNION ALL.

Example Tables

Consider a college database with two tables.

The Student table stores each student’s identifier, name, department assignment, and semester.

Student

StudentID

Name

DeptID

Semester

101

Asha

10

2

102

Ravi

20

4

103

Meera

10

2

104

Neha

NULL

2

The Department table stores each department’s identifier and name. A department can exist even when no students belong to it.

Department

DeptID

DeptName

10

Computer Science

20

Electronics

30

Mathematics

Assume that Student.StudentID and Department.DeptID are primary keys. Student.DeptID may contain NULL when a student has not been assigned to a department.

In this data:

  • Asha and Meera belong to Computer Science.

  • Ravi belongs to Electronics.

  • Neha has no department assignment.

  • Mathematics has no students.

These last two cases show why preserving unmatched rows from both tables can be useful.

HOW FULL OUTER JOIN WORKS

HOW FULL OUTER JOIN WORKS

Retrieving Matching and Unmatched Records

Objective: Display every student with their department details, while also including departments that have no students.

Consider the following queries to retrieve matching records and unmatched records from both tables.

MySQL Query:

SELECT StudentID, Name, DeptID, DeptName
FROM (
SELECT s.StudentID, s.Name, d.DeptID, d.DeptName
FROM Student AS s
LEFT JOIN Department AS d
ON s.DeptID = d.DeptID
UNION ALL
SELECT s.StudentID, s.Name, d.DeptID, d.DeptName
FROM Student AS s
RIGHT JOIN Department AS d
ON s.DeptID = d.DeptID
WHERE s.StudentID IS NULL
) AS combined
ORDER BY StudentID IS NULL, StudentID, DeptID;

Here, s and d are table aliases. AS combined names the result inside the parentheses so that the outer query can select and sort it.

The query builds the result in three parts:

  1. LEFT JOIN returns matching records and students without a matching department.

  2. RIGHT JOIN with WHERE s.StudentID IS NULL returns only departments without a matching student.

  3. UNION ALL places these two sets of rows into one result.

The second part excludes matching student records because they already appear in the first part.

UNION ALL preserves repeated result rows when they legitimately occur. Plain UNION removes identical result rows, which can change the result of a join.

FULL JOIN IN MYSQL

FULL JOIN IN MYSQL

PostgreSQL Query:

SELECT s.StudentID, s.Name, d.DeptID, d.DeptName
FROM Student AS s
FULL OUTER JOIN Department AS d
ON s.DeptID = d.DeptID
ORDER BY s.StudentID IS NULL, s.StudentID, d.DeptID;

AS s and AS d give the tables shorter names called aliases. For example, s.DeptID refers to the department identifier stored in Student.

The ON condition matches rows whose department identifiers are equal.

In ORDER BY, s.StudentID IS NULL places rows with a student ID before rows without one. The remaining expressions sort by student ID and then department ID. This controls the display order; it does not remove rows.

Expected Result

Both queries return:

StudentID

Name

DeptID

DeptName

101

Asha

10

Computer Science

102

Ravi

20

Electronics

103

Meera

10

Computer Science

104

Neha

NULL

NULL

NULL

NULL

30

Mathematics

Asha, Ravi, and Meera have matching departments, so their rows contain information from both tables.

Neha remains in the result despite having no matching department. Mathematics also remains, even though it has no matching student.

The displayed DeptID comes from the Department table.

Understanding NULL Values and Multiple Matches

A full outer join does not create a match simply because both compared values are NULL. With an ordinary equality condition such as s.DeptID = d.DeptID, a match requires the comparison to be true.

To check for a missing value, use IS NULL, not = NULL.

When identifying an unmatched row, test a column that cannot be NULL in a real record. In this example, the primary keys provide reliable checks:

Condition

What it identifies in the joined result

s.StudentID IS NULL

A department without a matching student

d.DeptID IS NULL

A student without a matching department

A full outer join can also produce several rows for one record. Computer Science appears twice because it matches two different students: Asha and Meera. Each matching pair produces a result row.

Preserving every record therefore does not mean that each record appears exactly once.

Filtering a FULL OUTER JOIN with WHERE

The ON clause determines which rows match. The WHERE clause filters the joined result.

This distinction matters because a WHERE condition can remove unmatched rows that the full outer join initially preserved.

Objective: Display semester 2 students, including those without a department, and retain departments that have no students at all.

Consider the following queries to filter students by semester while keeping departments without students.

MySQL Query:

SELECT StudentID, Name, DeptID, DeptName
FROM (
SELECT s.StudentID, s.Name, s.Semester,
d.DeptID, d.DeptName
FROM Student AS s
LEFT JOIN Department AS d
ON s.DeptID = d.DeptID
UNION ALL
SELECT s.StudentID, s.Name, s.Semester,
d.DeptID, d.DeptName
FROM Student AS s
RIGHT JOIN Department AS d
ON s.DeptID = d.DeptID
WHERE s.StudentID IS NULL
) AS combined
WHERE Semester = 2
OR StudentID IS NULL
ORDER BY StudentID IS NULL, StudentID, DeptID;

The aliases s and d identify the source tables. combined names their combined result.

Semester is included inside combined so that the outer WHERE clause can filter it. It does not need to appear in the final selected columns.

PostgreSQL Query:

SELECT s.StudentID, s.Name, d.DeptID, d.DeptName
FROM Student AS s
FULL OUTER JOIN Department AS d
ON s.DeptID = d.DeptID
WHERE s.Semester = 2
OR s.StudentID IS NULL
ORDER BY s.StudentID IS NULL, s.StudentID, d.DeptID;

Here, s refers to Student, and d refers to Department.

The condition keeps a row when the student is in semester 2 or the row represents a department without a matching student.

Expected Result

StudentID

Name

DeptID

DeptName

101

Asha

10

Computer Science

103

Meera

10

Computer Science

104

Neha

NULL

NULL

NULL

NULL

30

Mathematics

Asha, Meera, and Neha remain because their semester is 2. Mathematics remains because its row has no student ID.

Ravi is removed because his semester is 4. Electronics does not reappear as an unmatched department: it already matched Ravi, and WHERE filters that joined row afterward.

Without OR s.StudentID IS NULL in PostgreSQL, or its equivalent in MySQL, Mathematics would also be removed.

Moving the semester condition into ON would have a different meaning. It would change which students match departments, but a full outer join would still preserve students such as Ravi as unmatched rows.

Summary

A FULL OUTER JOIN returns matching rows and preserves unmatched rows from both tables. Missing-side columns contain NULL, and multiple matches can produce several result rows for one source record.

PostgreSQL supports full outer joins directly. MySQL can produce the equivalent result by combining a left join with unmatched rows from a right join using UNION ALL. Correct join conditions, careful WHERE filtering, and primary-key checks with IS NULL make the results easier to interpret.

CS Core

Read Similar Blogs

Comments0