FULL OUTER JOIN
FULL OUTER JOIN returns all records from both tables, including matching and non-matching records.
When a matching record exists, data from both tables is displayed. When there is no matching record, NULL values are returned for the missing side.
What is FULL OUTER JOIN?
A FULL OUTER JOIN combines the behavior of LEFT JOIN and RIGHT JOIN.
It returns:
- All matching records from both tables.
- All non-matching records from the left table.
- All non-matching records from the right table.
For a missing match, the columns belonging to the table without a matching row contain NULL.
FULL OUTER JOIN Syntax
SELECT columns
FROM Table1
FULL OUTER JOIN Table2
ON Table1.CommonColumn = Table2.CommonColumn;
Example Tables
Students Table
| StudentId | StudentName | CourseId |
|---|---|---|
| 101 | Rahul | 1 |
| 102 | Priya | 2 |
| 103 | Amit | 1 |
| 104 | Neha | 3 |
| 105 | Rohan | 5 |
Courses Table
| CourseId | CourseName |
|---|---|
| 1 | .NET Full Stack |
| 2 | Java Full Stack |
| 3 | Python Full Stack |
| 4 | Software Testing |
Basic FULL OUTER JOIN Example
We can combine Students and Courses using CourseId.
SELECT
Students.StudentName,
Courses.CourseName
FROM Students
FULL OUTER JOIN Courses
ON Students.CourseId = Courses.CourseId;
Result
FULL OUTER JOIN returns matching records as well as unmatched records from both tables.
| StudentName | CourseName |
|---|---|
| Rahul | .NET Full Stack |
| Amit | .NET Full Stack |
| Priya | Java Full Stack |
| Neha | Python Full Stack |
| Rohan | NULL |
| NULL | Software Testing |
Matching Records
When CourseId exists in both tables, the values from both tables are combined into one result row.
Students.CourseId = Courses.CourseId
For example, CourseId 1 exists in both tables, so Rahul and Amit can be matched with .NET Full Stack.
Non-Matching Records from Left Table
FULL OUTER JOIN also keeps records from the left table that do not have a matching record in the right table.
SELECT
S.StudentName,
C.CourseName
FROM Students AS S
FULL OUTER JOIN Courses AS C
ON S.CourseId = C.CourseId
WHERE C.CourseId IS NULL;
This can be used to identify students whose CourseId does not exist in the Courses table.
Non-Matching Records from Right Table
FULL OUTER JOIN can also be used to identify records from the right table that have no matching record in the left table.
SELECT
S.StudentName,
C.CourseName
FROM Students AS S
FULL OUTER JOIN Courses AS C
ON S.CourseId = C.CourseId
WHERE S.StudentId IS NULL;
This returns courses that do not have a matching student.
FULL OUTER JOIN using Aliases
SELECT
S.StudentName,
C.CourseName
FROM Students AS S
FULL OUTER JOIN Courses AS C
ON S.CourseId = C.CourseId;
Aliases make the query shorter and easier to read.
FULL OUTER JOIN with WHERE
We can use WHERE conditions to filter the result of a FULL OUTER JOIN.
SELECT
S.StudentName,
C.CourseName
FROM Students AS S
FULL OUTER JOIN Courses AS C
ON S.CourseId = C.CourseId
WHERE S.StudentName IS NOT NULL
OR C.CourseName IS NOT NULL;
JOIN Comparison
| JOIN | Records Returned |
|---|---|
| INNER JOIN | Matching records only. |
| LEFT JOIN | All left records + matching right records. |
| RIGHT JOIN | All right records + matching left records. |
| FULL OUTER JOIN | All records from both tables. |
Important Points
- FULL OUTER JOIN returns all records from both tables.
- Matching records are combined.
- Non-matching records are also included.
- NULL values appear for the side where no matching record exists.
- The ON clause defines how the tables are related.
Summary
FULL OUTER JOIN returns all matching and non-matching records from both tables. It is useful when we need complete information from both sides of a relationship, including records that do not have a match.