Mastering LEFT JOIN in SQL: A Detailed Guide

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

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

Syntax:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Table 1: Left Table: Subject

SubjectId	SubjectName
1	C
2	Java

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

Example: Left Join

Code:

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

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

Code:

-- Example 2 - left 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

Code: Left Join

SELECT *
FROM Subjects
LEFT 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	NULL	NULL	NULL
4	PHP	NULL	NULL	NULL

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)
)



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



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