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

Table 2: Right Table: Chapter

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-

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