SQL LEFT JOIN — সম্পূর্ণ ২ ঘন্টার লাইভ ক্লাস নোট

01

গল্প — University Students & Departments

১৫ মিনিট

University চায়: সব student দেখাও এবং তাদের department দেখাও।

Students

student_id	student_name	department_id
101	Rahim	1
102	Karim	2
103	Hasan	3
104	Nila	99
105	Sumi	NULL

Departments

department_id	department_name
1	CSE
2	EEE
3	BBA

জিজ্ঞেস করো

Hasan-এর department আছে? → হ্যাঁ (BBA)

Nila-এর 99 departments-এ আছে? → না

Sumi-এর department_id আছে? → না (NULL)

Nila ও Sumi বাদ দেব? → না — requirement বলে সব student।

এখানেই LEFT JOIN দরকার — সব left rows রাখতে।

02

Interactive Discovery

১০ মিনিট

Left table কোনটি?

সব data রাখতে হবে → Students LEFT SIDE।

Students → LEFT SIDE → সব student থাকবে Departments → RIGHT SIDE → Matching information only Students LEFT JOIN Departments

03

Animated Frames — Row by Row

১৫ মিনিট

Frame 1 — Tables

Frame 2 — Rahim

Dept=1 → found → CSE

Frame 3 — Karim

2 → found → EEE

Frame 4 — Hasan

3 → found → BBA

Frame 5 — Nila

99 → No match → NULL

Frame 6 — Sumi

NULL → No match → NULL

student_id	student_name	department_name
101	Rahim	CSE
102	Karim	EEE
103	Hasan	BBA
104	Nila	NULL
105	Sumi	NULL

Left table-এর কোনো row হারায়নি।

04

LEFT JOIN Concept

১০ মিনিট

LEFT JOIN left table-এর সব row রাখে। Right থেকে match থাকলে data; না থাকলে NULL।

LEFT TABLE RIGHT TABLE A B C D E A B D E ↓ ALL LEFT ↓ ONLY MATCHING \ / RESULT (+ NULL if no match) LEFT → সব row রাখো → RIGHT match → data বা NULL

05

প্রথম LEFT JOIN Query

১০ মিনিট

SELECT * FROM students LEFT JOIN departments ON students.department_id = departments.department_id;

FROM → কোন table দিয়ে শুরু? (LEFT) LEFT JOIN → কোন table connect? ON → কীভাবে related? students.department_id = departments.department_id → matching key

06

INNER JOIN vs LEFT JOIN Animation

১৫ মিনিট

INNER JOIN

101 CSE 102 EEE 103 BBA 104 ❌ 105 ❌

LEFT JOIN

101 CSE 102 EEE 103 BBA 104 NULL 105 NULL

Student	INNER	LEFT
Rahim	CSE	CSE
Karim	EEE	EEE
Hasan	BBA	BBA
Nila	❌	NULL
Sumi	❌	NULL

INNER unmatched বাদ · LEFT unmatched left রাখে (right NULL)।

07

Venn (Conceptual)

৫ মিনিট

INNER ≈ only MATCH LEFT ≈ entire LEFT side + MATCH

Venn শুধু ধারণা — আসল SQL behavior = ON condition দিয়ে row matching।

08

LEFT JOIN + WHERE vs ON

১৫ মিনিট

SELECT s.student_name, d.department_name FROM students s LEFT JOIN departments d ON s.department_id = d.department_id WHERE d.department_name = 'CSE';

সব student থাকবে?

না — WHERE right-side NULL rows কেড়ে নিতে পারে (Nila/Sumi বাদ)।

Unmatched রাখতে চাইলে filter ON-এ

LEFT JOIN departments d ON s.department_id = d.department_id AND d.department_name = 'CSE';

ON = match নিয়ম · WHERE = join পরের filter (NULL কেটে দিতে পারে)।

09

Finding Unmatched Records

১০ মিনিট

যেসব student-এর valid department নেই:

SELECT s.student_id, s.student_name FROM students s LEFT JOIN departments d ON s.department_id = d.department_id WHERE d.department_id IS NULL;

LEFT JOIN → all students → no match → NULL → WHERE right key IS NULL → unmatched

Nila, Sumi

10

E-commerce: All Customers + Order Count

১৫ মিনিট

customers	orders
1 Rahim, 2 Karim, 3 Hasan, 4 Nila, 5 Sumi	101→1, 102→1, 103→2, 104→4

SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.customer_name;

Rahim 2 · Karim 1 · Hasan 0 · Nila 1 · Sumi 0

এখানে COUNT(o.order_id) — COUNT(*) unmatched-এও ১ গুনতে পারে (NULL row)।

11

LEFT JOIN + SUM + COALESCE

১০ মিনিট

SELECT c.customer_name, COALESCE(SUM(o.amount), 0) AS total_purchase FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.customer_name;

LEFT JOIN → no orders remain → SUM NULL → COALESCE → 0

12

ORDER BY + LIMIT

৫ মিনিট

SELECT c.customer_name, COALESCE(SUM(o.amount), 0) AS total_purchase FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.customer_name ORDER BY total_purchase DESC LIMIT 10;

13

৩ টেবিল LEFT JOIN

১০ মিনিট

SELECT c.customer_name, o.order_id, p.product_name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN products p ON o.product_id = p.product_id;

ALL customers remain → Orders (nullable) → Products (nullable) No order → order_id NULL, product NULL

14

Industry Use Cases

৮ মিনিট

Customers with no orders
Employees without managers
Students without departments
Patients without appointments
Accounts without transactions
Products never sold
Data quality / orphan detection

15

Pattern: Find Missing

১০ মিনিট

FROM A
LEFT JOIN B ON A.key = B.key
WHERE B.key IS NULL;

-- Products never ordered SELECT p.product_id, p.product_name FROM products p LEFT JOIN order_items oi ON p.product_id = oi.product_id WHERE oi.product_id IS NULL; -- Customers with no orders SELECT c.customer_id, c.customer_name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.customer_id IS NULL;

16

What Will Happen? Game

১০ মিনিট

আগে predict করো, তারপর কার্ড খোলো।

Predict 1 — A:{1,2,3} B:{1,3,4} LEFT JOIN rows?

৩ (সব A)

Predict 2 — Same data INNER JOIN rows?

২ (শুধু 1 ও 3)

Predict 3 — LEFT unmatched right value?

NULL

Predict 4 — Student 99 no dept LEFT?

Student থাকে, dept NULL

Predict 5 — INNER same student?

Row বাদ

Predict 6 — COUNT(*) vs COUNT(o.id) for no-order customer?

COUNT(*) often 1; COUNT(o.id)=0

Predict 7 — WHERE right IS NULL finds?

Unmatched left

Predict 8 — WHERE amount>5000 after LEFT?

Can drop zero-order customers

Predict 9 — Filter in ON amount>5000?

Customers remain; non-matching orders NULL

Predict 10 — Sumi NULL dept LEFT name?

Sumi + NULL

Predict 11 — One customer 2 orders LEFT rows before GROUP?

২ rows that customer

Predict 12 — After GROUP COUNT(o.id)?

order_count=2

Predict 13 — COALESCE(SUM,0) no orders?

0

Predict 14 — 3-table no order product col?

NULL

Predict 15 — Prefer LEFT or INNER for all customers report?

LEFT

17

Manual Row Matching (৫ datasets)

১০ মিনিট

Drill 1 — L:1 Rahim,2 Karim,3 Hasan,4 Nila | R:1 IT,2 HR,4 Finance

1 IT, 2 HR, 3 NULL, 4 Finance

Drill 2 — L:A,B,C | R:B only

A NULL, B match, C NULL

Drill 3 — L:1,2 | R: empty

দুই left row, right সব NULL

Drill 4 — L:1,2,3 | R:1,2,3 all match

৩ matched — LEFT≈INNER result

Drill 5 — L:NULL key row

সাধারণত no match → right NULL

18

INNER vs LEFT Comparison

৮ মিনিট

Feature	INNER	LEFT
Left rows	Matching only	All
Unmatched left	Removed	Kept
Right unmatched	Removed	NULL
Find missing links	Hard	Excellent
Customers w/o orders	Difficult	Excellent

19

LEFT vs RIGHT

৫ মিনিট

LEFT JOIN → Keep LEFT table RIGHT JOIN → Keep RIGHT table

অনেক developer preserve করতে চাওয়া table-কে LEFT করে LEFT JOIN পছন্দ করে — অপ্রয়োজনীয় RIGHT এড়াও।

20

Common Mistakes (১৫)

১০ মিনিট

LEFT = শুধু match ভাবা → ভুল
ON ভুলে যাওয়া
Wrong join columns
WHERE দিয়ে unintentionally unmatched কাটা
INNER-এর সাথে গুলিয়ে ফেলা
COUNT(*) যেখানে COUNT(right.id) দরকার
NULL ignore
One-to-many না বোঝা
Unexpected duplicates
Relationship না বুঝে join
Ambiguous columns
SELECT * সবসময়
Right table WHERE filter ভুলে
GROUP BY columns ভুলে
Unmatched check না করা

-- Broken: drops customers without orders WHERE o.amount > 5000; -- Often better for preserve-left: put amount filter in ON ON c.customer_id = o.customer_id AND o.amount > 5000;

21

JOIN Decision Tree

৫ মিনিট

Only matching? → INNER JOIN All left rows? → LEFT JOIN All right rows? → RIGHT JOIN (or flip + LEFT) All both? → FULL OUTER (MySQL later/union pattern)

22

Execution Flow

৫ মিনিট

FROM → LEFT JOIN ON → Preserve LEFT → Add RIGHT or NULL → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

23

Workbench Lab (১৫ steps)

২০ মিনিট

CREATE DATABASE left_join_lab; USE left_join_lab; CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(80)); CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2)); INSERT INTO customers VALUES (1,'Rahim'),(2,'Karim'),(3,'Hasan'),(4,'Nila'),(5,'Sumi'); INSERT INTO orders VALUES (101,1,5000),(102,1,3000),(103,2,7000),(104,4,2000);

Basic LEFT JOIN
Specific columns
Aliases
WHERE on right
Filter in ON
Unmatched WHERE o.customer_id IS NULL
GROUP BY
COUNT(o.order_id)
SUM + COALESCE
ORDER BY
LIMIT
Add products + 3-table LEFT
Products never sold pattern
Customers without orders
Mini DQ report

24

Mini Project — Sales Dashboard Dataset

১৫ মিনিট

All customers + order count
All customers + total purchase
Never ordered customers
Products + qty sold
Products never sold
Categories + product counts
Customers + latest order date
Customers with no orders (confirm)
Top customers
Products with zero sales

25

ON vs WHERE Deep Dive

১০ মিনিট

Query A — WHERE

FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.amount > 5000;

Hasan/Sumi (no orders) বাদ যেতে পারে।

Query B — AND in ON

LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.amount > 5000;

সব customer থাকতে পারে; শুধু qualifying orders attach হয়।

26

Performance Basics

৫ মিনিট

Index join keys (PK/FK)
Select needed columns
Avoid useless joins
Watch one-to-many row growth
Millions of rows → JOIN costly

27

Classroom Challenges (২০+)

১৫ মিনিট

Ch 1 — Predict LEFT output students/depts

৫ rows; ২ NULL dept

Ch 2 — Row count A 3 B match 2 LEFT?

3

Ch 3 — Identify unmatched

WHERE right.key IS NULL

Ch 4 — INNER or LEFT all customers?

LEFT

Ch 5 — Customers without orders SQL?

LEFT JOIN … WHERE o.customer_id IS NULL

Ch 6 — Products never sold?

LEFT JOIN order_items WHERE oi.product_id IS NULL

Ch 7 — Emp without dept?

LEFT JOIN depts WHERE d.id IS NULL

Ch 8 — Write ON key customers/orders

c.customer_id = o.customer_id

Ch 9 — Fix JOIN missing ON

Add ON clause

Ch 10 — Why NULL on right?

No matching right row

Ch 11 — Add WHERE CSE effect?

May remove NULL dept students

Ch 12 — Move filter WHERE→ON

Preserves left unmatched

Ch 13 — GROUP BY customer + COUNT

COUNT(o.order_id)

Ch 14 — COUNT tip

Prefer COUNT(right key)

Ch 15 — SUM tip

COALESCE(SUM(...),0)

Ch 16 — COALESCE why?

Show 0 not NULL for dashboards

Ch 17 — ORDER BY total

After aggregate

Ch 18 — LIMIT top 10

ORDER DESC LIMIT 10

Ch 19 — 3-table LEFT

c LEFT o LEFT p

Ch 20 — Business NL all customers + orders

customers LEFT JOIN orders

28

MCQ (২৫+)

১৫ মিনিট

MCQ 1. LEFT JOIN keeps? A) only match B) all left C) all right D) none

B

MCQ 2. No match right side? A) error B) NULL C) 0 D) delete left

B

MCQ 3. Find unmatched? A) INNER B) LEFT + WHERE right IS NULL C) CROSS D) UNION

B

MCQ 4. INNER vs LEFT Nila 99?

INNER drops; LEFT keeps NULL

MCQ 5. COUNT for zero orders?

COUNT(o.order_id) not COUNT(*)

MCQ 6. WHERE after LEFT can?

Remove unmatched left rows

MCQ 7. Filter in ON can?

Preserve left; restrict matches

MCQ 8. COALESCE(SUM,0)?

Replace NULL sum with 0

MCQ 9. LEFT means?

Preserve left table

MCQ 10. Prefer RIGHT often?

Often rewrite as LEFT

MCQ 11. ON purpose?

Join matching condition

MCQ 12. Join key often?

FK = other PK

MCQ 13. One-to-many LEFT?

Multiple rows per left parent

MCQ 14. Venn caveat?

Conceptual only

MCQ 15. Products never sold pattern?

LEFT + WHERE oi.id IS NULL

MCQ 16. Decision: all left?

LEFT JOIN

MCQ 17. Decision: only match?

INNER JOIN

MCQ 18. SUM no orders without COALESCE?

NULL

MCQ 19. Ambiguous id?

Qualify with alias

MCQ 20. SELECT * issue?

Extra cols / less clear

MCQ 21. Index tip?

Index join keys

MCQ 22. 3-table no order?

Later cols NULL

MCQ 23. Core memory LEFT?

All left + match right + else NULL

MCQ 24. Core INNER?

Matching only

MCQ 25. Unmatched detection industry?

DQ / orphans / never ordered

MCQ 26. GROUP BY need?

Non-aggregated left keys

29

Viva

১০ মিনিট

Viva 1. LEFT JOIN কী?

সব left + matching right / else NULL

Viva 2. কেন ব্যবহার?

সব left রাখতে / missing খুঁজতে

Viva 3. LEFT মানে?

Preserve left table

Viva 4. No match?

Right columns NULL

Viva 5. NULL কেন?

Right-এ match নেই

Viva 6. vs INNER?

INNER unmatched বাদ

Viva 7. ON কী?

কীভাবে relate

Viva 8. Join key?

Matching columns

Viva 9. FK?

Reference to other PK

Viva 10. Unmatched find?

LEFT + WHERE right IS NULL

Viva 11. WHERE change LEFT?

Can filter away NULL matches

Viva 12. ON vs WHERE?

Match rule vs post filter

Viva 13. COUNT(right.id)?

Counts real matches; 0 if none

Viva 14. No-order customers?

LEFT JOIN orders WHERE o.id IS NULL

Viva 15. Never sold products?

LEFT order_items WHERE oi.pid IS NULL

Viva 16. COALESCE?

NULL → 0 for reports

Viva 17. vs RIGHT?

Keep right; prefer flip+LEFT

Viva 18. One-to-many?

Duplicates possible — normal

Viva 19. Decision tree?

Need all left → LEFT

Viva 20. Performance?

Index keys; select needed cols

Viva 21. 3-table?

Chain LEFT JOINs

Viva 22. Core trio?

INNER match; LEFT all left; no match NULL

Viva 23. Teaching frames?

Row-by-row match animation

Viva 24. Playground idea?

Pick tables→key→type→see match/NULL

Viva 25. Business NL cue?

সব X দেখাও + related Y

Viva 26. Mistake COUNT(*)?

Inflates zero-match to 1

30

Interview Top 30

১৫ মিনিট

IV 1. Explain LEFT JOIN

Keep all left · SQL LEFT JOIN · ভুল: =INNER · ফলো: NULL? · ইউজ: reporting

IV 2. INNER vs LEFT

match only vs keep left · ভুল: same · ফলো: Nila · ইউজ: interviews

IV 3. LEFT vs RIGHT

which side preserved · ভুল: random RIGHT · ফলো: rewrite LEFT · ইউজ: style

IV 4. Find unmatched

LEFT + WHERE B.key IS NULL · ভুল: NOT IN NULL mess · ফলো: orphans · ইউজ: DQ

IV 5. Customers no orders

c LEFT o WHERE o.cid IS NULL · ভুল: INNER · ফলো: % · ইউজ: CRM

IV 6. Products never sold

p LEFT oi WHERE oi.pid IS NULL · ভুল: NOT EXISTS only · ফলো: index · ইউজ: catalog

IV 7. NULL after LEFT

unmatched right · ভুল: =0 · ফলো: IS NULL · ইউজ: debug

IV 8. ON vs WHERE

match vs filter · ভুল: always WHERE · ফলো: A/B demo · ইউজ: advanced

IV 9. LEFT + GROUP BY

all parents + aggs · ভুল: drop zeros · ফলো: HAVING · ইউজ: BI

IV 10. LEFT + COUNT

COUNT(right key) · ভুল: COUNT(*) · ফলো: example · ইউজ: metrics

IV 11. LEFT + SUM

SUM may NULL · ভুল: forget COALESCE · ফলো: dashboard · ইউজ: finance

IV 12. COALESCE

NULL→0 · ভুল: always update table · ফলো: display only · ইউজ: UI

IV 13. Three-table LEFT

c LEFT o LEFT p · ভুল: INNER mid · ফলো: no order · ইউজ: analytics

IV 14. Duplicate rows

one-to-many · ভুল: always bug · ফলো: aggregate · ইউজ: mentoring

IV 15. One-to-many

1→many children · ভুল: force 1 row · ফলো: grain · ইউজ: modeling

IV 16. Performance

index keys · ভুল: ignore · ফলো: EXPLAIN intro · ইউজ: scale

IV 17. Indexes

PK/FK help · ভুল: function on key · ফলো: covering · ইউজ: DBA

IV 18. Debug LEFT

check ON + sample unmatched · ভুল: DISTINCT blind · ফলো: COUNT · ইউজ: support

IV 19. Decision all left

LEFT · ভুল: INNER · ফলো: story · ইউজ: BA

IV 20. Filter amount preserve customers

AND in ON · ভুল: WHERE · ফলো: show outputs · ইউজ: reports

IV 21. Latest order date all customers

LEFT + MAX(order_date) · ভুল: INNER · ফলো: NULL dates · ইউজ: CRM

IV 22. Category product counts

cat LEFT products COUNT · ভুল: INNER only · ফলো: empty cats · ইউজ: catalog

IV 23. Top customers keep zeros?

LEFT + COALESCE + ORDER · ভুল: INNER top · ফলো: LIMIT · ইউজ: leaderboard

IV 24. Ambiguous columns

alias qualify · ভুল: SELECT id · ফলো: both ids · ইউজ: debugging

IV 25. Core teaching goal

all left + NULL unmatched · ভুল: memorize only · ফলো: frames · ইউজ: training

IV 26. Playground steps

left→right→key→type→animate · ভুল: syntax first · ফলো: predict · ইউজ: class

IV 27. NULL Handling link

IS NULL / COALESCE · ভুল: isolate topics · ফলো: COUNT · ইউজ: curriculum

IV 28. Common WHERE trap

turns LEFT into filter-match · ভুল: surprise empty · ফলো: fix ON · ইউজ: code review

IV 29. Business cue phrase

সব X দেখাও + Y info · ভুল: INNER habit · ফলো: write SQL · ইউজ: daily

IV 30. Final rule

LEFT=all left+match/NULL · ভুল: only matching · ফলো: INNER contrast · ইউজ: memory

31

Homework

বাড়ি

প্রশ্ন (১৫):

All customers with orders
Customers without orders
Products with sold qty
Products never sold
Categories with product counts
Customers total purchase
Replace NULL purchase with 0
Customers with no purchase
LEFT + GROUP BY
LEFT + HAVING
LEFT + ORDER BY
LEFT + LIMIT
3-table LEFT
Explain ON vs WHERE
Explain LEFT vs INNER

উত্তর ১

customers c LEFT JOIN orders o ON c.customer_id=o.customer_id

উত্তর ২

… WHERE o.customer_id IS NULL

উত্তর ৩

products LEFT JOIN order_items … COUNT/SUM qty

উত্তর ৪

… WHERE oi.product_id IS NULL

উত্তর ৫

categories LEFT JOIN products GROUP BY … COUNT

উত্তর ৬

LEFT JOIN orders SUM(amount) GROUP BY customer

উত্তর ৭

COALESCE(SUM(amount),0)

উত্তর ৮

WHERE o.customer_id IS NULL বা total=0

উত্তর ৯

GROUP BY c.customer_id, c.customer_name

উত্তর ১০

HAVING COUNT(o.order_id) = 0 বা > n

উত্তর ১১

ORDER BY total DESC

উত্তর ১২

LIMIT 10

উত্তর ১৩

c LEFT o LEFT p

উত্তর ১৪

ON=match rules; WHERE=post filter may drop NULLs

উত্তর ১৫

INNER matching only; LEFT keeps all left

32

Cheat Sheet + Conceptual Playground

১০ মিনিট

LEFT JOIN

LEFT TABLE → KEEP ALL → MATCH RIGHT
Match? YES→DATA  NO→NULL

SELECT columns
FROM left_table l
LEFT JOIN right_table r
    ON l.key = r.key;

Find unmatched:
FROM A LEFT JOIN B ON A.id=B.id
WHERE B.id IS NULL;

Aggregate:
SELECT a.name, COUNT(b.id)
FROM A a LEFT JOIN B b ON a.id=b.a_id
GROUP BY a.id, a.name;

Interactive playground steps (ক্লাসে মানসিক/বোর্ড)

Select LEFT table → Select RIGHT table → Select JOIN key → Choose JOIN type (INNER / LEFT) → Run JOIN → Animate row matching → Highlight matched / unmatched → Show NULL → Display final result INNER → Only matched rows LEFT → All left + matched right + NULL unmatched right

সব customer দেখাও + order info → Customer LEFT → LEFT JOIN Orders → no order → NULL

INNER = matching only · LEFT = all left + matching right · No match = right NULL

সব বাম দিকের রো রাখো — SQL LEFT JOIN

বিষয়সূচী

গল্প — University Students & Departments

Students

Departments

Interactive Discovery

Animated Frames — Row by Row

LEFT JOIN Concept

প্রথম LEFT JOIN Query

INNER JOIN vs LEFT JOIN Animation

INNER JOIN

LEFT JOIN

Venn (Conceptual)

LEFT JOIN + WHERE vs ON

Finding Unmatched Records

E-commerce: All Customers + Order Count

LEFT JOIN + SUM + COALESCE

ORDER BY + LIMIT

৩ টেবিল LEFT JOIN

Industry Use Cases

Pattern: Find Missing

What Will Happen? Game

Manual Row Matching (৫ datasets)

INNER vs LEFT Comparison

LEFT vs RIGHT

Common Mistakes (১৫)

JOIN Decision Tree

Execution Flow

Workbench Lab (১৫ steps)

Mini Project — Sales Dashboard Dataset

ON vs WHERE Deep Dive

Performance Basics

Classroom Challenges (২০+)

MCQ (২৫+)

Viva

Interview Top 30

Homework

Cheat Sheet + Conceptual Playground

Interactive playground steps (ক্লাসে মানসিক/বোর্ড)