Mastering RIGHT JOIN in SQL: A Comprehensive Guide

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in the left table; the join will still return a row in the result, but with NULL in each column from the left table.

This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

Table 1: Left Table: Subject

SubjectId	SubjectName
1	C
2	Java
3	Python
4	PHP

Table 2: Right Table: Chapter

ChapterId	ChapterName	SubjectId
1	Introduction C	1
2	Datatypes C	1
3	Introduction Java	2
4	Datatypes Java	2

Example: Right Join

Code:

SELECT *
FROM Subjects
RIGHT 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

Example 2: Right Join

For this example we will change the data of the tables, lets understand it.

Code:

-- Right Join Example 2
 DELETE FROM Subjects
 DELETE FROM Chapters

 INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java')

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)
 
SELECT * FROM Subjects
SELECT * FROM Chapters

Code: Right Join

SELECT *
FROM Subjects
RIGHT 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
NULL	NULL	5	Introduction Python	3
NULL	NULL	6	Datatypes Python	3
NULL	NULL	7	Introduction PHP	4
NULL	NULL	8	Datatypes PHP	4

Prerequisite Codes

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

Code:

-- Right Join

 DELETE FROM Subjects
 DELETE FROM Chapters

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

INSERT INTO Chapters VALUES
(1, 'Introduction C', 1),
(2, 'Datatypes C', 1), 
(3, 'Introduction Java', 2),
(4, 'Datatypes Java', 2)
 
SELECT * FROM Subjects
SELECT * FROM Chapters