01
গল্প — Departments & Employees
১৫ মিনিট
Manager: সব department দেখাও, আর যেখানে employee আছে তাদের info দেখাও।
| employee_id | name | department_id |
|---|---|---|
| 101 | Rahim | 1 |
| 102 | Karim | 2 |
| 103 | Hasan | 1 |
| department_id | department_name |
|---|---|
| 1 | IT |
| 2 | HR |
| 3 | Finance |
| 4 | Marketing |
জিজ্ঞেস
Finance বাদ? Marketing বাদ? → না — সব department চাই।
Departments → RIGHT SIDE → সব department রাখো → Employees সাথে match
RIGHT JOIN = Right side-এর সবাই VIP — সবাই result-এ থাকবে।
02
Interactive Discovery
১০ মিনিট
কোন table-এর সব row রাখব?
Departments → RIGHT।
Employees = LEFT (optional data)
Departments = RIGHT (must keep ALL)
→ FROM employees RIGHT JOIN departments
RIGHT JOIN-এ right-side table protected।
03
Animated Frames
১৫ মিনিট
Frame 1
EMP: 101 Rahim→1, 102 Karim→2, 103 Hasan→1
DEPT: 1 IT, 2 HR, 3 Finance, 4 Marketing
Frame 2 — IT
Dept 1 → employees → Rahim (+Hasan) MATCH
Frame 3 — HR
Dept 2 → Karim MATCH
Frame 4 — Finance
Dept 3 → no employee → NULL
Frame 5 — Marketing
Dept 4 → no employee → NULL
| employee_name | department_name |
|---|---|
| Rahim | IT |
| Hasan | IT |
| Karim | HR |
| NULL | Finance |
| NULL | Marketing |
Department table-এর কোনো row হারায়নি।
04
RIGHT JOIN কী?
১০ মিনিট
RIGHT JOIN right table-এর সব row রাখে; left থেকে match হলে data, না হলে left NULL।
LEFT: A B C RIGHT: A B D E
RIGHT JOIN → A-A, B-B, NULL-D, NULL-E
RIGHT JOIN → RIGHT = ALL · LEFT = MATCHING
05
প্রথম RIGHT JOIN Query
১০ মিনিট
SELECT *
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;
FROM → start table
RIGHT JOIN → কোন table সব রাখবে?
ON → matching key
employees.department_id = departments.department_id
06
Expected Output
৫ মিনিট
| employee_id | employee_name | department_id | department_name |
|---|---|---|---|
| 101 | Rahim | 1 | IT |
| 103 | Hasan | 1 | IT |
| 102 | Karim | 2 | HR |
| NULL | NULL | 3 | Finance |
| NULL | NULL | 4 | Marketing |
IT/HR = match · Finance/Marketing = employee columns NULL।
07
LEFT vs RIGHT Animation
১৫ মিনিট
LEFT JOIN (emp LEFT dept)
Rahim→IT
Karim→HR
Hasan→IT
(Finance/Marketing বাদ)
RIGHT JOIN (emp RIGHT dept)
Rahim→IT
Hasan→IT
Karim→HR
NULL→Finance
NULL→Marketing
| Question | LEFT | RIGHT |
|---|---|---|
| Preserved table | Left | Right |
| All left rows? | Yes | No |
| All right rows? | No | Yes |
| Missing side | NULL | NULL |
08
Memory Boxes
৫ মিনিট
LEFT JOIN → LEFT = ALL · RIGHT = MATCH
RIGHT JOIN → RIGHT = ALL · LEFT = MATCH
INNER JOIN → Only MATCH
LEFT JOIN → Keep LEFT
RIGHT JOIN → Keep RIGHT
09
RIGHT JOIN + WHERE
১০ মিনিট
SELECT e.employee_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Finance';
NULL | Finance
Finance থাকে কারণ right preserve; employee নেই → NULL। WHERE join পর filter করে।
10
Unmatched — Departments without employees
১০ মিনিট
SELECT d.department_id, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;
3 Finance · 4 Marketing
RIGHT JOIN → all depts → no emp → left NULL → WHERE e.id IS NULL
11
Equivalent LEFT JOIN
১০ মিনিট
RIGHT
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;
LEFT (same idea)
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id;
একই result — table position বদল। যে table রাখতে চাই তাকে LEFT করলে পড়া সহজ।
12
Flip the Tables (৫+)
১০ মিনিট
Flip 1 — emp RIGHT JOIN dept → LEFT?
FROM departments d LEFT JOIN employees e ON …
Flip 2 — order_items RIGHT JOIN products → LEFT?
FROM products p LEFT JOIN order_items oi ON …
Flip 3 — appointments RIGHT JOIN doctors → LEFT?
FROM doctors d LEFT JOIN appointments a ON …
Flip 4 — accounts RIGHT JOIN branches → LEFT?
FROM branches b LEFT JOIN accounts a ON …
Flip 5 — A RIGHT JOIN B ON A.id=B.id → LEFT?
FROM B LEFT JOIN A ON B.id=A.id
13
RIGHT JOIN + GROUP BY
১০ মিনিট
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;
IT 2 · HR 1 · Finance 0 · Marketing 0
COUNT(e.employee_id) — না COUNT(*) (unmatched-এও ১ হতে পারে)।14
SUM / AVG + COALESCE
৮ মিনিট
SELECT d.department_name,
COALESCE(SUM(e.salary), 0) AS total_salary,
AVG(e.salary) AS avg_salary,
MAX(e.salary) AS max_salary,
MIN(e.salary) AS min_salary
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;
15
HAVING
৮ মিনিট
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(e.employee_id) > 2;
RIGHT JOIN → GROUP → COUNT → HAVING → Final
16
E-commerce — Products never sold
১০ মিনিট
SELECT p.product_name, COUNT(oi.product_id) AS times_sold
FROM order_items oi
RIGHT JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_id, p.product_name;
Preferred equivalent
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id …
Laptop 2 · Mobile 1 · Headphone 0 · Keyboard 0
17
Hospital — Doctors
৮ মিনিট
সব doctor + appointment count: appointments RIGHT JOIN doctors বা doctors LEFT JOIN appointments।
18
Bank — Branches
৮ মিনিট
সব branch + account count — RIGHT preserve branches (Sylhet = 0)। Equivalent: branches LEFT JOIN accounts।
19
Multi-table RIGHT
৮ মিনিট
FROM customers c
RIGHT JOIN orders o ON …
RIGHT JOIN products p ON …
Chain RIGHT পড়া কঠিন — প্রায়ই LEFT দিয়ে rewrite করো।
20
ON vs WHERE
১০ মিনিট
Query 1 — WHERE Finance
… RIGHT JOIN departments d ON …
WHERE d.department_name = 'Finance';
Query 2 — AND in ON
… ON e.department_id = d.department_id
AND d.department_name = 'Finance';
Filter placement result বদলাতে পারে — row-by-row দেখে বোঝাও।
21
Common Mistakes (১৫)
১০ মিনিট
- RIGHT = keep left ভাবা
- কোন table right ভুলে যাওয়া
- Wrong join key
- ON ভুলে
- LEFT/RIGHT গুলিয়ে
- Unmatched disappear আশা
- NULL না বোঝা
- WHERE দিয়ে unintentionally কাটা
- COUNT(*) ভুল জায়গায়
- Duplicate rows surprise
- One-to-many ignore
- Unrelated columns join
- SELECT *
- GROUP BY columns ভুলে
- যখন LEFT clearer তখনও RIGHT জোর করা
22
Conceptual Playground
৮ মিনিট
Select LEFT → RIGHT → JOIN TYPE → JOIN KEY → RUN
→ Animate matching → Highlight preserved right rows
→ Unmatched left → NULL → Final table
Switch: INNER | LEFT | RIGHT → see output change
RIGHT TABLE ROW → search LEFT → Match? YES data / NO NULL
23
Prediction Game (২০+)
১৫ মিনিট
Predict 1 — A{1,2,3} B{1,3,4} RIGHT JOIN rows?
৩ (সব B)
Predict 2 — Same INNER rows?
২
Predict 3 — Same LEFT (A left) rows?
৩ (সব A)
Predict 4 — B id=4 after RIGHT?
থাকে; A side NULL
Predict 5 — Finance no emp RIGHT?
Dept থাকে; emp NULL
Predict 6 — COUNT(e.id) Finance?
0
Predict 7 — COUNT(*) Finance?
Often 1 — careful
Predict 8 — RIGHT unmatched find?
WHERE left.key IS NULL
Predict 9 — Flip emp RIGHT dept
dept LEFT emp
Predict 10 — Products RIGHT sold count zeros?
Yes Headphone/Keyboard 0
Predict 11 — Prefer LEFT rewrite why?
Preserve table on left — readable
Predict 12 — WHERE after RIGHT on dept name
Filters preserved right set
Predict 13 — AND in ON vs WHERE
Can change which rows attach
Predict 14 — One dept many emp RIGHT rows?
Multiple rows that dept
Predict 15 — NULL on which side RIGHT?
Left-side columns
Predict 16 — Sylhet branch 0 accounts
Branch kept
Predict 17 — Doctor no appt RIGHT
Doctor kept; appt NULL
Predict 18 — Chain RIGHT hard?
Yes — rewrite LEFT
Predict 19 — Core RIGHT rule?
Keep RIGHT + match LEFT / else NULL
Predict 20 — Core LEFT rule?
Keep LEFT + match RIGHT / else NULL
24
Decision Tree
৫ মিনিট
Preserve LEFT? → LEFT JOIN
Preserve RIGHT? → RIGHT JOIN (or flip → LEFT)
Only matching? → INNER JOIN
25
INNER vs LEFT vs RIGHT
৮ মিনিট
| Requirement | JOIN |
|---|---|
| Only matching | INNER |
| Keep every left | LEFT |
| Keep every right | RIGHT |
| Missing right links from left | LEFT + IS NULL |
| Missing left links from right | RIGHT + IS NULL (or flip LEFT) |
26
Why Pros Often Prefer LEFT
৮ মিনিট
RIGHT valid — কিন্তু যে table রাখতে চাই তাকে LEFT করে LEFT JOIN লিখলে পড়া সহজ।
RIGHT ভুল/obsolete নয় — readability choice।
27
Workbench Lab
২০ মিনিট
CREATE DATABASE right_join_lab;
USE right_join_lab;
CREATE TABLE departments (
department_id INT PRIMARY KEY, department_name VARCHAR(40));
CREATE TABLE employees (
employee_id INT PRIMARY KEY, employee_name VARCHAR(80),
department_id INT, salary DECIMAL(10,2));
INSERT INTO departments VALUES
(1,'IT'),(2,'HR'),(3,'Finance'),(4,'Marketing');
INSERT INTO employees VALUES
(101,'Rahim',1,55000),(102,'Karim',2,48000),(103,'Hasan',1,72000);
- Basic RIGHT JOIN
- Specific columns
- Aliases
- WHERE
- NULL unmatched
- Find empty depts
- GROUP BY
- COUNT
- SUM
- AVG
- HAVING
- ORDER BY
- LIMIT
- 3-table note
- Convert to LEFT JOIN
28
Mini Project
১৫ মিনিট
departments / employees / projects / employee_projects:
- All depts + emp count
- Depts with zero emp
- Avg salary
- Highest salary
- Projects + assigned emp count
- Projects with no emp
- Depts > 5 emp
- Depts salary threshold
- Order by emp count
- Top 5 depts
RIGHT + LEFT equivalent দুটোই লেখো।
29
Performance Basics
৫ মিনিট
- Index join keys / PK / FK
- Select needed columns
- Avoid useless joins
- One-to-many row growth
- Large tables costly
30
Challenges (২০)
১৫ মিনিট
Ch 1 — Identify RIGHT table in emp RIGHT dept
departments
Ch 2 — Predict row count 3 emp 4 dept RIGHT
≥4 (dept all; IT may duplicate)
Ch 3 — Unmatched right rows
Finance, Marketing
Ch 4 — Missing left data means?
emp columns NULL
Ch 5 — Write RIGHT JOIN depts
FROM emp RIGHT JOIN dept ON …
Ch 6 — Fix missing ON
Add ON keys
Ch 7 — Convert to LEFT
FROM dept LEFT JOIN emp
Ch 8 — Add WHERE Finance
Shows Finance (+NULL emp)
Ch 9 — Explain NULL
No matching employee
Ch 10 — IS NULL pattern
WHERE e.employee_id IS NULL
Ch 11 — COUNT tip
COUNT(e.employee_id)
Ch 12 — SUM tip
COALESCE(SUM(salary),0)
Ch 13 — AVG empty dept
NULL avg unless handled
Ch 14 — GROUP BY
Group by dept keys
Ch 15 — HAVING >2
HAVING COUNT(e.employee_id)>2
Ch 16 — ORDER BY count
ORDER BY employee_count
Ch 17 — LIMIT top depts
ORDER DESC LIMIT 5
Ch 18 — 3-table note
Prefer LEFT rewrite
Ch 19 — Depts without emp
RIGHT + WHERE e.id IS NULL
Ch 20 — Products never sold
oi RIGHT JOIN p … or p LEFT oi
31
MCQ (২৫+)
১৫ মিনিট
MCQ 1. RIGHT JOIN keeps? A) left all B) right all C) match only D) none
B
MCQ 2. No left match? A) drop right B) left NULL C) error D) 0
B
MCQ 3. Find empty depts?
RIGHT + WHERE e.id IS NULL
MCQ 4. Equivalent of emp RIGHT dept?
dept LEFT emp
MCQ 5. COUNT for empty dept?
COUNT(e.employee_id)=0
MCQ 6. COUNT(*) risk?
May count 1 for unmatched
MCQ 7. Pros prefer?
Often LEFT for readability
MCQ 8. RIGHT wrong/obsolete?
No — style choice
MCQ 9. INNER vs RIGHT Finance?
INNER drops; RIGHT keeps
MCQ 10. LEFT emp→dept keeps Finance?
No
MCQ 11. ON purpose?
Match condition
MCQ 12. WHERE after RIGHT?
Filters result
MCQ 13. COALESCE SUM?
0 for empty depts
MCQ 14. One-to-many?
Multiple rows per right parent
MCQ 15. VIP memory?
Right side all stay
MCQ 16. Flip rule?
Swap tables + LEFT
MCQ 17. Chain RIGHT?
Hard to read
MCQ 18. Products never sold RIGHT?
oi RIGHT JOIN products
MCQ 19. Decision preserve right?
RIGHT or flip LEFT
MCQ 20. Decision only match?
INNER
MCQ 21. NULL side on RIGHT JOIN?
Left columns
MCQ 22. Index tip?
Join keys
MCQ 23. Core trio?
INNER match; LEFT keep left; RIGHT keep right
MCQ 24. Ambiguous id?
Qualify alias
MCQ 25. GROUP BY need?
Dept id/name non-aggs
MCQ 26. HAVING filters?
Groups after aggregate
32
Viva
১০ মিনিট
Viva 1. RIGHT JOIN কী?
সব right + matching left / else NULL
Viva 2. কেন?
সব right রাখতে
Viva 3. Preserved?
Right table
Viva 4. No match?
Left NULL
Viva 5. NULL কেন?
Left-এ match নেই
Viva 6. vs LEFT?
Opposite side preserved
Viva 7. vs INNER?
INNER unmatched বাদ
Viva 8. ON?
Join key condition
Viva 9. Join key?
Usually FK=PK
Viva 10. Empty depts?
WHERE e.id IS NULL
Viva 11. COUNT?
COUNT(employee_id)
Viva 12. COALESCE?
NULL sum → 0
Viva 13. Convert LEFT?
Swap tables use LEFT
Viva 14. Prefer LEFT why?
Readable preserve-on-left
Viva 15. RIGHT obsolete?
না
Viva 16. One-to-many?
Duplicates possible
Viva 17. Performance?
Index keys
Viva 18. Core memory?
Right VIP all stay
Viva 19. Flip activity?
Rewrite as LEFT
Viva 20. Playground?
Switch INNER/LEFT/RIGHT
Viva 21. ON vs WHERE?
Match vs post filter
Viva 22. Multi RIGHT?
Prefer LEFT rewrite
Viva 23. E-com use?
All products + sold count
Viva 24. Hospital?
All doctors + appt count
Viva 25. Bank?
All branches + accounts
Viva 26. Teaching frames?
Dept-by-dept match
33
Interview Top 30
১৫ মিনিট
IV 1. Explain RIGHT JOIN
Keep all right · SQL RIGHT JOIN · ভুল: keep left · ফলো: NULL? · ইউজ: reporting
IV 2. RIGHT vs LEFT
which side preserved · ভুল: same · ফলো: flip · ইউজ: interviews
IV 3. RIGHT vs INNER
keep unmatched right · ভুল: identical · ফলো: Finance · ইউজ: HR
IV 4. Unmatched right
WHERE left.key IS NULL · ভুল: INNER · ফলো: orphans · ইউজ: DQ
IV 5. Depts without emp
emp RIGHT dept WHERE e.id IS NULL · ভুল: LEFT wrong side · ফলো: LEFT rewrite · ইউজ: HR
IV 6. Products never sold
oi RIGHT p / p LEFT oi · ভুল: INNER · ফলো: COUNT · ইউজ: catalog
IV 7. NULL after RIGHT
left cols NULL · ভুল: =0 · ফলো: IS NULL · ইউজ: debug
IV 8. ON vs WHERE
match vs filter · ভুল: ignore · ফলো: demo · ইউজ: advanced
IV 9. GROUP BY
all depts + aggs · ভুল: drop zeros · ফলো: HAVING · ইউজ: BI
IV 10. COUNT
COUNT(left key) · ভুল: COUNT(*) · ফলো: zeros · ইউজ: metrics
IV 11. SUM
COALESCE · ভুল: leave NULL · ফলো: dashboard · ইউজ: finance
IV 12. COALESCE
display 0 · ভুল: mutate source · ফলো: AVG · ইউজ: UI
IV 13. Multiple RIGHT
hard read · ভুল: nest blindly · ফলো: LEFT · ইউজ: review
IV 14. Convert to LEFT
swap order · ভুল: change keys wrong · ফলো: prove equal · ইউজ: style
IV 15. Duplicates
one-to-many · ভুল: always bug · ফলো: grain · ইউজ: mentoring
IV 16. One-to-many
1 dept many emp · ভুল: force 1 · ফলো: COUNT · ইউজ: model
IV 17. Performance
index keys · ভুল: ignore · ফলো: EXPLAIN · ইউজ: scale
IV 18. Indexes
PK/FK · ভুল: function on key · ফলো: covering · ইউজ: DBA
IV 19. Prefer LEFT?
readability · ভুল: ban RIGHT · ফলো: when RIGHT ok · ইউজ: team style
IV 20. Decision tree
preserve side · ভুল: random · ফলো: business · ইউজ: BA
IV 21. VIP trick
right all stay · ভুল: left · ফলো: teach · ইউজ: class
IV 22. Flip 5 drills
practice rewrite · ভুল: memorize only · ফলো: assess · ইউজ: training
IV 23. HAVING after RIGHT
filter groups · ভুল: WHERE COUNT · ফলো: threshold · ইউজ: reports
IV 24. ORDER LIMIT
top depts · ভুল: before group mentally · ফলো: pagination · ইউজ: dashboards
IV 25. Bank branches
keep all branches · ভুল: INNER · ফলো: zero accounts · ইউজ: ops
IV 26. Doctors
keep all doctors · ভুল: only with appt · ফলো: LEFT rewrite · ইউজ: hospital
IV 27. Common mistake side
confuse LEFT/RIGHT · ভুল: guess · ফলো: draw · ইউজ: bugs
IV 28. Playground switch
INNER/LEFT/RIGHT outputs · ভুল: syntax first · ফলো: predict · ইউজ: interactive
IV 29. Business cue
সব Y দেখাও + X info · ভুল: INNER habit · ফলো: pick join · ইউজ: daily
IV 30. Final rule
RIGHT=all right+match left/NULL · ভুল: only matching · ফলো: LEFT prefer · ইউজ: memory
34
Homework
বাড়ি
প্রশ্ন (১৫):
- All departments + employees
- Departments without employees
- All products + sales count
- Products never sold
- All doctors + appointments
- Doctors without appointments
- All branches + account count
- Branches with zero accounts
- RIGHT + GROUP BY
- RIGHT + HAVING
- RIGHT + ORDER BY
- RIGHT + LIMIT
- RIGHT + NULL pattern
- Convert RIGHT to LEFT
- When replace RIGHT with LEFT?
উত্তর ১
FROM emp RIGHT JOIN dept ON …
উত্তর ২
… WHERE e.employee_id IS NULL
উত্তর ৩
FROM oi RIGHT JOIN products … COUNT
উত্তর ৪
… WHERE oi.product_id IS NULL
উত্তর ৫
appointments RIGHT JOIN doctors / doctors LEFT appt
উত্তর ৬
WHERE a.appointment_id IS NULL
উত্তর ৭
accounts RIGHT JOIN branches COUNT
উত্তর ৮
WHERE a.account_id IS NULL
উত্তর ৯
GROUP BY dept keys + COUNT(e.id)
উত্তর ১০
HAVING COUNT(e.employee_id) > n
উত্তর ১১
ORDER BY employee_count DESC
উত্তর ১২
LIMIT 5
উত্তর ১৩
WHERE left.key IS NULL
উত্তর ১৪
FROM right_table LEFT JOIN left_table ON …
উত্তর ১৫
When preserve-table-on-left is clearer — same result
35
Cheat Sheet
৫ মিনিট
RIGHT JOIN → RIGHT = ALL → LEFT = MATCH → no match → LEFT NULL
SELECT columns
FROM left_table l
RIGHT JOIN right_table r
ON l.key = r.key;
Unmatched left side:
FROM A RIGHT JOIN B ON A.id=B.id
WHERE A.id IS NULL;
Equivalent LEFT:
FROM B LEFT JOIN A ON B.id=A.id;
Memory: RIGHT JOIN = Right VIP all stay.
INNER = MATCH · LEFT = keep LEFT · RIGHT = keep RIGHT