✏️ Explanatory Question

When Left Join gives result like Right Join?

👁 425 Views
📘 Detailed Answer
No previous question
💡

Answer with Explanation

Left Table

SubjectId

SubjectName

1

C Language

2

Java Language

Right Table

ChapterId

ChapterName

SubjectId

1

Introduction C

1

2

Operator Java

2

3

Operator python

3

4

Operator R

3

For both of the cases Result will be like this

SubjectId

SubjectName

ChapterId

ChapterName

1

C Language

1

Introduction C

2

Java Language

2

Operator Java

NULL

NULL

3

Operator python

NULL

NULL

4

Operator R

Example: Right Join

Code: 


SELECT s.SubjectId as SubjectId, s.SubjectName as SubjectName,
 c.ChapterId as ChapterId, c.ChapterName as ChapterName
FROM Subjects as s 
RIGHT JOIN Chapters as c
ON s.SubjectId = c.SubjectId

Example: Left Join which will give output same as Right Join.

We can say this is as a mirror effect.

Code: 


SELECT s.SubjectId as SubjectId, s.SubjectName as SubjectName,
 c.ChapterId as ChapterId, c.ChapterName as ChapterName
FROM Chapters as c 
LEFT JOIN Subjects as s 
ON s.SubjectId = c.SubjectId

Example: Left Join will give below result without mirror effect.

Code: 


SELECT s.SubjectId as SubjectId, s.SubjectName as SubjectName,
 c.ChapterId as ChapterId, c.ChapterName as ChapterName
FROM Subjects as s
LEFT JOIN Chapters as c
ON s.SubjectId = c.SubjectId

SubjectId

SubjectName

ChapterId

ChapterName

1

C Language

1

Introduction C

2

Java Language

2

Operator Java

Prerequisite Codes

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

Code: 


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,
)
No previous question