Mastering LEFT JOIN in SQL: A Detailed Guide

Rumman Ansari   Software Engineer   2024-07-19 02:03:26   6036  Share
Subject Syllabus DetailsSubject Details 2 Questions
☰ TContent
☰Fullscreen

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.

join in sql

Syntax: 

<span class="pln">
SELECT column_name</span><span class="pun">(</span><span class="pln">s</span><span class="pun">)</span><span class="pln">
FROM table1
LEFT JOIN table2
ON table1</span><span class="pun">.</span><span class="pln">column_name </span><span class="pun">=</span><span class="pln"> table2</span><span class="pun">.</span><span class="pln">column_name</span><span class="pun">;</span><span class="pln">
</span>

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: 

<span class="pln">
SELECT </span><span class="pun">*</span><span class="pln">
FROM </span><span class="typ">Subjects</span><span class="pln">
LEFT JOIN </span><span class="typ">Chapters</span><span class="pln">
ON </span><span class="typ">Subjects</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Chapters</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pun">;</span><span class="pln">
</span>

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: 

<span class="pln">
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Example</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="pun">-</span><span class="pln"> left </span><span class="kwd">join</span><span class="pln">
 DELETE FROM </span><span class="typ">Subjects</span><span class="pln">
 DELETE FROM </span><span class="typ">Chapters</span><span class="pln">

 INSERT INTO </span><span class="typ">Subjects</span><span class="pln"> VALUES 
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'C'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Java'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Python'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'PHP'</span><span class="pun">)</span><span class="pln">

INSERT INTO </span><span class="typ">Chapters</span><span class="pln"> VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Introduction C'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes C'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln"> 
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Introduction Java'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes Java'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">)</span><span class="pln"> 

SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Subjects</span><span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Chapters</span><span class="pln">

</span>

Code: Left Join 

<span class="pln">
SELECT </span><span class="pun">*</span><span class="pln">
FROM </span><span class="typ">Subjects</span><span class="pln">
LEFT JOIN </span><span class="typ">Chapters</span><span class="pln">
ON </span><span class="typ">Subjects</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Chapters</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pun">;</span><span class="pln">
</span>

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: 

<span class="pln">
</span><span class="pun">--</span><span class="pln"> USE </span><span class="typ">Database</span><span class="pln">
USE </span><span class="typ">SQLExamples</span><span class="pln">

DROP TABLE </span><span class="typ">Subjects</span><span class="pln">
DROP TABLE </span><span class="typ">Chapters</span><span class="pln">


CREATE TABLE </span><span class="typ">Subjects</span><span class="pun">(</span><span class="pln">
	</span><span class="typ">SubjectId</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln">	
	</span><span class="typ">SubjectName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">30</span><span class="pun">)</span><span class="pln">
</span><span class="pun">)</span><span class="pln">



CREATE TABLE </span><span class="typ">Chapters</span><span class="pun">(</span><span class="pln">
	</span><span class="typ">ChapterId</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln">	
	</span><span class="typ">ChapterName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln">
	</span><span class="typ">SubjectId</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln">
</span><span class="pun">)</span><span class="pln">



INSERT INTO </span><span class="typ">Subjects</span><span class="pln"> VALUES 
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'C'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Java'</span><span class="pun">)</span><span class="pln"> 

INSERT INTO </span><span class="typ">Chapters</span><span class="pln"> VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Introduction C'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes C'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln"> 
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Introduction Java'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes Java'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln"> 
</span><span class="pun">(</span><span class="lit">5</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Introduction Python'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">6</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes Python'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pun">),</span><span class="pln"> 
</span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Introduction PHP'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">4</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">8</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes PHP'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">4</span><span class="pun">)</span><span class="pln">


SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Subjects</span><span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Chapters</span><span class="pln">


</span>

No Program Data.

Stay Ahead of the Curve! Check out these trending topics and sharpen your skills.