Mastering RIGHT JOIN in SQL: A Comprehensive Guide

Rumman Ansari   Software Engineer   2024-07-19 02:04:39   5734  Share
Subject Syllabus DetailsSubject Details 2 Questions
☰ TContent
☰Fullscreen

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.

join in sql

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: 

<span class="pln">
SELECT </span><span class="pun">*</span><span class="pln">
FROM </span><span class="typ">Subjects</span><span class="pln">
RIGHT 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: Right Join

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">Right</span><span class="pln"> </span><span class="typ">Join</span><span class="pln"> </span><span class="typ">Example</span><span class="pln"> </span><span class="lit">2</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">

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>

Code: Right Join 

<span class="pln">
SELECT </span><span class="pun">*</span><span class="pln">
FROM </span><span class="typ">Subjects</span><span class="pln">
RIGHT 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

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: 

<span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Right</span><span class="pln"> </span><span class="typ">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>

No Program Data.

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