Understanding FULL OUTER JOIN in SQL: A Complete Guide

The SQL FULL JOIN combines the results of both left and right outer joins.

The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.

Table 1: Left Table: Subject

SubjectId	SubjectName
1	C
2	Java
3	Python
4	PHP
6	Perl

Table 2: Right Table: Chapter

ChapterId	ChapterName	SubjectId
1	Introduction C	1
2	Datatypes C	1
3	Introduction Java	2
4	Datatypes Java	2
5	Introduction Python	3
6	Datatypes Python	3
7	Introduction PHP	4
8	Datatypes PHP	4
9	Datatypes Ruby	5
10	Datatypes Ruby	5

Example: Full Outer Join

Code:

SELECT * 
FROM Subjects
FULL OUTER JOIN Chapters
ON Subjects.SubjectId = Chapters.SubjectId

Output:

The above code will produce the following result-

SubjectId	SubjectName	ChapterId	ChapterName	SubjectId
1	C	1	Introduction C	1
1	C	2	Datatypes C	1
2	Java	3	Introduction Java	2
2	Java	4	Datatypes Java	2
3	Python	5	Introduction Python	3
3	Python	6	Datatypes Python	3
4	PHP	7	Introduction PHP	4
4	PHP	8	Datatypes PHP	4
6	Perl	NULL	NULL	NULL
NULL	NULL	9	Datatypes Ruby	5
NULL	NULL	10	Datatypes Ruby	5

 

Prerequisite Codes

If you don't have the above tables create those table from the below code and let practice

Code:

-- USE Database
USE SQLExamples

DROP TABLE Subjects
DROP TABLE Chapters

CREATE TABLE Subjects(
	SubjectId INT,	
	SubjectName VARCHAR(30)
)

SELECT * FROM Subjects

CREATE TABLE Chapters(
	ChapterId INT,	
	ChapterName VARCHAR(30),
	SubjectId int,
)

INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java'),
(3, 'Python'),
(4, 'PHP'),
(6, 'Perl')

INSERT INTO Chapters VALUES
(1, 'Introduction C', 1),
(2, 'Datatypes C', 1), 
(3, 'Introduction Java', 2),
(4, 'Datatypes Java', 2), 
(5, 'Introduction Python', 3),
(6, 'Datatypes Python', 3), 
(7, 'Introduction PHP', 4),
(8, 'Datatypes PHP', 4),
(9, 'Datatypes Ruby', 5),
(10, 'Datatypes Ruby', 5)

SELECT * FROM Subjects
SELECT * FROM Chapters