14. LEFT JOIN & RIGHT JOIN -- Including Unmatched Rows

Level: Beginner

ℹ️ What You'll Learn

• LEFT JOIN syntax -- all from left table
• RIGHT JOIN syntax -- all from right table
• NULL values in unmatched rows
• Difference between INNER and LEFT JOIN
• When to use LEFT vs RIGHT
• LEFT JOIN with WHERE filter
• Multiple LEFT JOINs
• Common LEFT/RIGHT JOIN mistakes

INNER JOIN returns only matching rows. LEFT and RIGHT JOINs include unmatched rows from one table. This article teaches including "missing data" scenarios.

Why LEFT/RIGHT JOINs Matter for Backend Developers

APIs need complete data including empty results:

Frontend asks: "List all teachers and their subjects (even if no subject assigned)"
    v
INNER JOIN: Only teachers with subjects
LEFT JOIN: All teachers (with/without subjects)
    v
Backend returns complete teacher list for dropdown

LEFT JOIN prevents losing data from left table.

LEFT JOIN Syntax

SELECT *
FROM LeftTable
LEFT JOIN RightTable
ON LeftTable.ForeignKey = RightTable.PrimaryKey;

Effect: All rows from LeftTable. Matching rows from RightTable. Non-matching = NULL.

Example 1: All Teachers + Their Subjects

SELECT 
    t.Name AS Teacher,
    s.Name AS Subject
FROM Teacher t
LEFT JOIN Subject s ON t.TeacherId = s.TeacherId;

Result (including teachers without subjects):

Teacher    | Subject
Dr. Mehta  | Maths
Dr. Mehta  | Science
Mrs. Rao   | English
Mr. Sharma | NULL         <- No subjects assigned

Mr. Sharma included even without subjects (NULL).

Comparison: INNER vs LEFT

INNER JOIN:

SELECT * FROM Teacher t
INNER JOIN Subject s ON t.TeacherId = s.TeacherId;
-- Only teachers WITH subjects: Dr. Mehta, Mrs. Rao
-- Mr. Sharma excluded

LEFT JOIN:

SELECT * FROM Teacher t
LEFT JOIN Subject s ON t.TeacherId = s.TeacherId;
-- All teachers: Dr. Mehta, Mrs. Rao, Mr. Sharma
-- Mr. Sharma has NULL for Subject

Example 2: Student + Exam Results (with NULLs)

SELECT 
    s.Name AS Student,
    e.ExamName,
    r.MarksObtained
FROM Student s
LEFT JOIN ExamResult r ON s.StudentId = r.StudentId
LEFT JOIN Exam e ON r.ExamId = e.ExamId;

Result (students without exam results):

Student      | ExamName | MarksObtained
Ravi Kumar   | Maths    | 92
Priya Sharma | NULL     | NULL        <- No exam results
Arjun Reddy  | Science  | 85

Priya included even without results.

RIGHT JOIN Syntax

SELECT *
FROM LeftTable
RIGHT JOIN RightTable
ON LeftTable.ForeignKey = RightTable.PrimaryKey;

Effect: All rows from RightTable. Matching from LeftTable. Non-matching = NULL.

Equivalent to: LEFT JOIN but with tables reversed.

Example 3: RIGHT JOIN (Subject perspective)

SELECT 
    s.Name AS Subject,
    t.Name AS Teacher
FROM Teacher t
RIGHT JOIN Subject s ON t.TeacherId = s.TeacherId;

Result (all subjects, matching teachers):

Subject  | Teacher
Maths    | Dr. Mehta
Science  | Dr. Mehta
English  | Mrs. Rao
Hindi    | NULL        <- Subject without assigned teacher

All subjects shown, even unassigned (NULL teacher).

LEFT JOIN with WHERE Filter

Get teachers WITHOUT subjects:

SELECT 
    t.Name AS Teacher,
    s.Name AS Subject
FROM Teacher t
LEFT JOIN Subject s ON t.TeacherId = s.TeacherId
WHERE s.SubjectId IS NULL;

Result (teachers without subjects):

Teacher   | Subject
Mr. Sharma| NULL

WHERE filters for NULL joins (teachers without subjects).

Multiple LEFT JOINs

Chain LEFT JOINs:

SELECT 
    s.Name AS Student,
    e.ExamName,
    r.MarksObtained,
    sub.Name AS Subject
FROM Student s
LEFT JOIN ExamResult r ON s.StudentId = r.StudentId
LEFT JOIN Exam e ON r.ExamId = e.ExamId
LEFT JOIN Subject sub ON e.SubjectId = sub.SubjectId;

All students included, with NULL for missing exam/subject data.

---

Common LEFT/RIGHT JOIN Mistakes

Mistake 1: Using WHERE to filter LEFT JOIN results

Wrong:

SELECT * FROM Student s
LEFT JOIN Exam e ON s.StudentId = e.StudentId
WHERE e.ExamId IS NOT NULL;
-- Converts to INNER JOIN (filters out NULL)

WHERE after LEFT JOIN defeats its purpose.

Result: Same as INNER JOIN.

Fix: Use LEFT JOIN when you want all left rows:

SELECT * FROM Student s
LEFT JOIN Exam e ON s.StudentId = e.StudentId;
-- Keeps students without exams

Mistake 2: Confusing table order with LEFT vs RIGHT

LEFT JOIN:

FROM Teacher t
LEFT JOIN Subject s
-- All teachers (LEFT table)

RIGHT JOIN:

FROM Teacher t
RIGHT JOIN Subject s
-- All subjects (RIGHT table)

Same result = reverse table order or change JOIN type.

Result: Confusion about which table kept.

Fix: Know: LEFT keeps left table, RIGHT keeps right table.

Mistake 3: Not checking for NULL in results

Wrong:

SELECT * FROM Student s
LEFT JOIN Exam e ON s.StudentId = e.StudentId;
-- Forget: Some students have NULL exams
-- Backend tries: e.ExamName -> ERROR (NULL)

Result: NULL reference errors in backend code.

Fix: Handle NULL explicitly:

SELECT 
    s.Name,
    ISNULL(e.ExamName, 'No Exam') AS ExamName
FROM Student s
LEFT JOIN Exam e ON s.StudentId = e.StudentId;

Mistake 4: Using RIGHT JOIN (confusing)

Wrong:

FROM Teacher t RIGHT JOIN Subject s
-- Hard to read. Is it subjects or teachers?

Better:

FROM Subject s LEFT JOIN Teacher t
-- Clear: All subjects, matching teachers

Result: Readable code.

Fix: Use LEFT JOIN. Reverse table order if needed instead of RIGHT.

---

Best Practices for LEFT/RIGHT JOINs

1. Prefer LEFT JOIN -- More intuitive than RIGHT
2. Reverse table order instead of RIGHT -- Clearer intention
3. Handle NULL in results -- Check with IS NULL or ISNULL
4. Test row counts -- Verify all left rows included
5. Document why LEFT -- Comment explains inclusion of unmatched
6. Use WHERE for post-filter -- Not for JOIN condition
7. Test edge cases -- Empty right table, all NULLs

---

🎯 Q1: What's the difference between INNER and LEFT JOIN?

INNER JOIN:

FROM Student s INNER JOIN Exam e
-- Only students WITH exams
-- Mr. Sharma (no exam) excluded

LEFT JOIN:

FROM Student s LEFT JOIN Exam e
-- All students (with/without exams)
-- Mr. Sharma included with NULL exam

INNER = match required. LEFT = all from left.

🎯 Q2: When should I use LEFT vs RIGHT JOIN?

LEFT JOIN:

FROM Teacher t LEFT JOIN Subject s
-- Get all teachers, with/without subjects

RIGHT JOIN:

FROM Teacher t RIGHT JOIN Subject s
-- Same as: FROM Subject s LEFT JOIN Teacher t

Best practice: Use LEFT JOIN always. Reverse table order instead of RIGHT (clearer).

🎯 Q3: What does NULL mean in a LEFT JOIN result?

NULL = no match in right table:

Teacher    | Subject
Dr. Mehta  | Maths      (matched)
Mr. Sharma | NULL       (no subject assigned)

NULL means "this teacher has no subject".

Handle with:

COALESCE(s.Name, 'Unassigned') AS Subject

🎯 Q4: Can I use WHERE to filter LEFT JOIN?

No, WHERE breaks LEFT JOIN:

SELECT * FROM Student s
LEFT JOIN Exam e
WHERE e.ExamId IS NOT NULL;
-- This is now INNER JOIN (NULLs filtered)

If you want to filter:

SELECT * FROM Student s
LEFT JOIN Exam e
WHERE s.StudentId = 1;
-- Filter before join, not after

Use WHERE to filter left table, not right table results.

🎯 Q5: How do multiple LEFT JOINs work?

Each LEFT JOIN preserves all rows from its left table:

FROM Student s
LEFT JOIN Exam e ON s.StudentId = e.StudentId
LEFT JOIN Subject sub ON e.SubjectId = sub.SubjectId;

Result: All students, with NULL for missing exams/subjects.

Each JOIN adds another left-to-right connection.

🎯 Q6: Should I use LEFT or RIGHT JOIN in APIs?

LEFT JOIN most common in APIs:

-- Get all students (even without exam results)
FROM Student s LEFT JOIN ExamResult r

RIGHT JOIN rare. Easier to understand table order with LEFT.

Rule: If natural table order is left-to-right, use LEFT JOIN.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on LEFT JOIN and RIGHT JOIN in SQL Server. Try these prompts:

• "Explain LEFT JOIN with Teacher/Subject example - include unassigned teachers"
• "What's the difference between INNER JOIN and LEFT JOIN? Show results"
• "When should I use LEFT vs RIGHT? Why prefer LEFT?"
• "How do I find unmatched rows (teachers without subjects)?"
• "Quiz me: ask 5 questions about LEFT JOIN, RIGHT JOIN, NULL, unmatched rows"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

15. FULL OUTER JOIN & CROSS JOIN ->