01
গল্প — Employee & Manager
১৫ মিনিট
| employee_id | employee_name | manager_id |
|---|---|---|
| 101 | Rahim | NULL |
| 102 | Karim | 101 |
| 103 | Hasan | 101 |
| 104 | Nila | 102 |
| 105 | Sumi | 102 |
Rahim ≈ CEO · Karim/Hasan → Rahim · Nila/Sumi → Karim।
জিজ্ঞেস
Manager-এর নাম কোথায়? → employees table-এ।
Employee-এর নাম? → একই table।
সমস্যা: manager_id=101 আছে, কিন্তু manager name column নেই।
manager_id = 101 → need manager_name = Rahim
Rahim ও employees-এ → SELF JOIN দরকার
02
The Big Question
৮ মিনিট
এক table-এর দুইজনকে কীভাবে connect?
Karim → manager_id=101 → Rahim
employees ↕ SELF JOIN ↕ employees
Table duplicate করছি না — query-তে একই table দুই role দিয়ে ব্যবহার করছি।
03
Family Analogy
১০ মিনিট
| person_id | name | parent_id |
|---|---|---|
| 1 | Rahim | NULL |
| 2 | Karim | 1 |
| 3 | Nila | 1 |
| 4 | Sumi | 2 |
Sumi → parent_id=2 → Person 2 → Karim
একই table-এর এক row আরেক row-এর সাথে related
04
SELF JOIN কী?
১০ মিনিট
SELF JOIN = একই table-কে নিজের সাথে JOIN করে বিভিন্ন row-এর relationship বের করা।
SELF JOIN আলাদা keyword নয় (INNER/LEFT/RIGHT-এর মতো)। Technique: same table + JOIN + different aliases।
ONE TABLE → use TWO TIMES → TWO aliases → compare rows → relationship
05
কেন Aliases?
৮ মিনিট
employees
├── e = Employee Role
└── m = Manager Role
Physical table same · Roles different
Alias ছাড়া দুই role আলাদা করা যায় না।
06
প্রথম SELF JOIN Query
১০ মিনিট
SELECT
e.employee_name AS employee,
m.employee_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;
e = employee · m = manager
e.manager_id = m.employee_id → matching key
07
Animated Frames
১৫ মিনিট
Rahim
manager_id NULL → no manager → NULL
Karim
101 → Rahim
Hasan
101 → Rahim
Nila
102 → Karim
Sumi
102 → Karim
| employee | manager |
|---|---|
| Rahim | NULL |
| Karim | Rahim |
| Hasan | Rahim |
| Nila | Karim |
| Sumi | Karim |
08
কেন LEFT JOIN?
১০ মিনিট
সব employee রাখতে হবে → e LEFT JOIN m। INNER হলে Rahim বাদ যেতে পারে।
INNER
Karim/Hasan/Nila/Sumi
(Rahim ❌)
LEFT
সব + Rahim NULL manager
09
SELF vs Normal JOIN
৮ মিনিট
| Feature | Normal JOIN | SELF JOIN |
|---|---|---|
| Tables | Usually different | Same table |
| Example | Emp + Dept | Emp + Manager |
| Aliases | Helpful | Critical |
| Purpose | Connect tables | Connect rows inside table |
10
Dual-role Diagram
৫ মিনিট
employees
├── Employee Role e → manager_id
└── Manager Role m → employee_id
\ /
JOIN ON =
11
Hierarchical Data
১০ মিনিট
CEO → Manager → Lead → Employee · Category parent · Folder parent · Comment reply · Org tree।
Parent/manager একই table-এ থাকলে SELF JOIN useful।
12
Org Chart Visual
৫ মিনিট
Rahim (CEO)
/ \
Karim Hasan
/ \
Nila Sumi
Hierarchy এক table-এ manager_id দিয়ে store।
13
SELF JOIN + WHERE
৮ মিনিট
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
WHERE m.employee_name = 'Rahim';
Karim → Rahim · Hasan → Rahim
14
Employees Without Managers
৮ মিনিট
SELECT e.employee_id, e.employee_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
WHERE m.employee_id IS NULL;
101 Rahim
15
Who Manages Others
৮ মিনিট
SELECT DISTINCT m.employee_id, m.employee_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;
101 Rahim · 102 Karim
16
Employee + Manager + Senior Manager
১০ মিনিট
SELECT
e.employee_name AS employee,
m.employee_name AS manager,
mm.employee_name AS senior_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
LEFT JOIN employees mm ON m.manager_id = mm.employee_id;
Nila → Karim → Rahim · Sumi → Karim → Rahim
একই table তিনবার — e / m / mm।
17
Same-dept Comparison
১০ মিনিট
SELECT e1.employee_name, e2.employee_name, e1.salary, e2.salary
FROM employees e1
JOIN employees e2
ON e1.department_id = e2.department_id
AND e1.employee_id < e2.employee_id;
e1.id < e2.id → Rahim vs Karim এবং Karim vs Rahim duplicate এড়ায়।18
Compare Challenge
৫ মিনিট
Predict pairs
একই dept-এ pairs — id< দিয়ে unique pairs।
19
Category → Parent
৮ মিনিট
SELECT c.category_name AS category, p.category_name AS parent_category
FROM categories c
LEFT JOIN categories p ON c.parent_category_id = p.category_id;
Mobile → Electronics · Android → Mobile · Electronics → NULL
20
Product → Replacement
৮ মিনিট
SELECT p.product_name AS product, r.product_name AS replacement_product
FROM products p
LEFT JOIN products r ON p.replacement_product_id = r.product_id;
21
Hospital — Supervisor
৮ মিনিট
Doctor → Supervisor → Senior: d LEFT JOIN s ON d.supervisor_id=s.doctor_id LEFT JOIN ss ON s.supervisor_id=ss.doctor_id।
22
Comment → Parent
৮ মিনিট
Nested replies: c LEFT JOIN p ON c.parent_comment_id = p.comment_id।
23
Conceptual Playground
৮ মিনিট
Select Table → Role A → Role B → Join columns → INNER/LEFT → RUN
→ Animate match → NULL if no parent → Result
Dataset: Emp hierarchy | Category | Product replace | Comment | Family
24
Row Matching Steps
৫ মিনিট
Highlight employee → read manager_id → search same table
→ find employee_id → connect → else NULL → build result
25
Predict the Output (২০+)
১৫ মিনিট
Predict 1 — Karim manager?
Rahim
Predict 2 — Rahim manager LEFT?
NULL
Predict 3 — INNER drops whom?
Rahim (no manager)
Predict 4 — Nila senior manager?
Rahim
Predict 5 — Without manager pattern?
WHERE m.employee_id IS NULL
Predict 6 — Who are managers?
DISTINCT m referenced by e.manager_id
Predict 7 — Why aliases?
Two roles on same table
Predict 8 — SELF JOIN keyword?
No — technique
Predict 9 — Mobile parent?
Electronics
Predict 10 — Electronics parent?
NULL
Predict 11 — iPhone 13 replacement?
iPhone 14
Predict 12 — Why id < ?
Avoid duplicate pairs
Predict 13 — Same manager pairs use?
e1.manager_id=e2.manager_id AND e1.id
Predict 14 — Salary > manager needs?
JOIN m + WHERE e.salary>m.salary
Predict 15 — 3 aliases mean?
e, m, mm levels
Predict 16 — Comment reply parent?
SELF JOIN on parent_comment_id
Predict 17 — Doctor supervisor?
SELF JOIN supervisor_id
Predict 18 — COUNT reports per manager?
GROUP BY m.employee_id
Predict 19 — Top-level emp?
manager_id NULL / unmatched m
Predict 20 — Same table twice physical?
No — logical roles
26
Common Mistakes (১৫)
১০ মিনিট
- SELF JOIN = special keyword ভাবা
- Alias না দেওয়া
- একই alias দুবার
- e/m roles গুলিয়ে
- Wrong ON columns
- ON ভুলে
- সব emp চাইলে INNER
- NULL ignore
- Parent-child না বোঝা
- Duplicate compare pairs
id <ভুলে- Physical duplicate ভাবা
- SELECT *
- Ambiguous columns
- Relationship না বুঝে join
27
SELF vs INNER/LEFT/RIGHT
৮ মিনিট
| Concept | Meaning |
|---|---|
| INNER | Matching only |
| LEFT | Keep left |
| RIGHT | Keep right |
| SELF | Same table, multiple roles (uses INNER/LEFT…) |
e LEFT JOIN m এবং e JOIN m — দুটোই SELF JOIN technique।
28
Mental Model
৫ মিনিট
SELF JOIN → Same Table → Aliases → Roles → Connect rows
Emp→Mgr · Child→Parent · Cat→Parent · Product→Replace · Comment→Parent · Emp→Emp
29
Workbench Lab
২০ মিনিট
CREATE DATABASE self_join_lab;
USE self_join_lab;
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(80),
department_id INT,
manager_id INT,
salary DECIMAL(10,2)
);
INSERT INTO employees VALUES
(101,'Rahim',1,NULL,90000),
(102,'Karim',1,101,60000),
(103,'Hasan',2,101,65000),
(104,'Nila',1,102,50000),
(105,'Sumi',1,102,55000);
- Emp → Manager
- No managers
- Managers list
- Emp → Mgr → Senior
- Category parent
- Product replacement
- Doctor supervisor
- Comment parent
- Same dept pairs
- Salary compare
30
Mini Project — Org Analytics
১৫ মিনিট
- Employee + Manager
- Without managers
- Managers + report counts
- Emp + Mgr + Senior
- Salary > manager
- Same department employees
- Salary difference pairs
- Department hierarchy notes
- Manager hierarchy
- Top-level employees
31
Salary > Manager
৮ মিনিট
SELECT e.employee_name, e.salary, m.employee_name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;
32
Same Manager Pairs
৮ মিনিট
SELECT e1.employee_name, e2.employee_name, e1.manager_id
FROM employees e1
JOIN employees e2
ON e1.manager_id = e2.manager_id
AND e1.employee_id < e2.employee_id;
33
Performance Basics
৫ মিনিট
- SELF JOIN large tables-এ costly হতে পারে
- Index
manager_id/ relationship columns - PK usually indexed
- Select needed columns
- Avoid unnecessary repeated SELF JOINs
- Watch row multiplication
34
Classroom Activities (২০)
১৫ মিনিট
Act 1 — Parent vs child?
parent_id points to parent row
Act 2 — Emp vs manager role?
e vs m aliases
Act 3 — Predict Karim→?
Rahim
Act 4 — Correct alias pair?
e and m different
Act 5 — Fix same alias twice
Use e and m
Act 6 — Top-level employees
manager NULL / m.id IS NULL
Act 7 — Find managers
DISTINCT m via JOIN
Act 8 — No managers
LEFT + WHERE m IS NULL
Act 9 — Salary > manager
JOIN + WHERE e.salary>m.salary
Act 10 — Same-manager pairs
ON manager_id + id<
Act 11 — Parent categories
c LEFT JOIN p
Act 12 — Child categories
WHERE parent = X
Act 13 — Replacement products
p LEFT JOIN r
Act 14 — Parent comments
c LEFT JOIN parent
Act 15 — Write emp-manager SQL
e LEFT JOIN m ON e.manager_id=m.employee_id
Act 16 — Add WHERE Rahim reports
WHERE m.name='Rahim'
Act 17 — GROUP BY reports
GROUP BY m.employee_id COUNT
Act 18 — HAVING managers >2 reports
HAVING COUNT(*)>2
Act 19 — ORDER BY name
ORDER BY employee
Act 20 — NL→SELF JOIN
Same table relationship → aliases → ON
35
MCQ (২৫+)
১৫ মিনিট
MCQ 1. SELF JOIN is? A) keyword B) technique C) index D) constraint
B
MCQ 2. Why aliases? A) optional B) required for roles C) slower D) DROP
B
MCQ 3. Emp-manager ON?
e.manager_id = m.employee_id
MCQ 4. Keep all emp? A) INNER B) LEFT e→m C) RIGHT only D) CROSS
B
MCQ 5. INNER drops? A) managers B) top-level no manager C) all D) none
B
MCQ 6. 3 levels need? A) 1 alias B) 3 aliases C) 2 tables D) UNION
B
MCQ 7. Avoid pair duplicates?
e1.id < e2.id
MCQ 8. Hierarchical example?
Category parent
MCQ 9. Find no manager?
LEFT + WHERE m.id IS NULL
MCQ 10. Who is manager?
Referenced by others' manager_id
MCQ 11. Physical copy table?
No — logical roles
MCQ 12. Can use INNER?
Yes
MCQ 13. Can use LEFT?
Yes
MCQ 14. Salary > mgr?
JOIN m WHERE e.salary>m.salary
MCQ 15. Same manager pairs?
ON manager_id AND id<
MCQ 16. Product replace?
SELF JOIN replacement_id
MCQ 17. Comment reply?
parent_comment_id SELF JOIN
MCQ 18. Ambiguous name?
Qualify e./m.
MCQ 19. Performance tip?
Index manager_id
MCQ 20. Core memory?
Same table multiple roles
MCQ 21. Normal JOIN vs SELF?
Different tables vs same
MCQ 22. Org chart store?
manager_id column
MCQ 23. Senior manager?
Second SELF JOIN mm
MCQ 24. Mistake same alias?
Ambiguity/error
MCQ 25. SELECT * issue?
Duplicate cols hard to read
MCQ 26. Playground first step?
Pick dataset + roles
36
Viva
১০ মিনিট
Viva 1. SELF JOIN কী?
Same table multi-role JOIN technique
Viva 2. Separate JOIN type?
না
Viva 3. Aliases কেন?
Roles আলাদা করতে
Viva 4. Same table twice?
Connect rows inside
Viva 5. Emp-manager?
e.manager_id=m.employee_id
Viva 6. Parent-child?
parent_id → id
Viva 7. Hierarchical?
Tree in one table
Viva 8. LEFT with SELF?
Keep all children/emps
Viva 9. No managers?
WHERE m.id IS NULL
Viva 10. Find managers?
DISTINCT referenced m
Viva 11. Compare emp?
SELF JOIN same dept + id<
Viva 12. Avoid duplicates?
id <
Viva 13. INNER SELF?
Yes
Viva 14. LEFT SELF?
Yes
Viva 15. Multiple times?
Yes e/m/mm
Viva 16. Performance?
Index relationship cols
Viva 17. Core examples?
Mgr/parent/replace/comment
Viva 18. Mental model?
Aliases → roles → ON
Viva 19. Mistake keyword?
Thinking SELF JOIN keyword
Viva 20. Org analytics?
Reports + hierarchy
Viva 21. Salary > mgr?
Compare via JOIN
Viva 22. Same mgr pairs?
ON manager_id
Viva 23. Categories?
parent_category_id
Viva 24. Products?
replacement_product_id
Viva 25. Comments?
parent_comment_id
Viva 26. Teaching tip?
WHY before syntax
37
Interview Top 30
১৫ মিনিট
IV 1. What is SELF JOIN?
Technique same table · e LEFT m · ভুল: keyword · ফলো: aliases · ইউজ: hierarchy
IV 2. JOIN type?
No — uses INNER/LEFT · ভুল: SELF JOIN keyword · ফলো: example · ইউজ: interviews
IV 3. Why aliases?
Two roles · ভুল: skip · ফলো: e/m · ইউজ: readability
IV 4. Emp-manager
ON manager_id=employee_id · ভুল: wrong cols · ফলো: LEFT · ইউজ: HR
IV 5. Parent-child
parent_id · ভুল: two tables always · ফলো: family · ইউজ: trees
IV 6. No managers
LEFT + IS NULL · ভুল: INNER · ফলো: CEO · ইউজ: org
IV 7. Find managers
DISTINCT m via join · ভুল: manager_id IS NOT NULL only · ফলো: COUNT reports · ইউজ: HR
IV 8. Salary > manager
JOIN + WHERE e.salary>m.salary · ভুল: subquery only · ফলো: equity · ইউজ: payroll
IV 9. Same-manager
ON manager_id AND id< · ভুল: duplicates · ফলো: teams · ইউজ: analytics
IV 10. Avoid pairs
id < · ভুল: DISTINCT * · ফলো: why · ইউজ: compare
IV 11. GROUP BY
reports per manager · ভুল: wrong group · ফলো: HAVING · ইউজ: BI
IV 12. HAVING
managers with >n reports · ভুল: WHERE COUNT · ফলো: threshold · ইউজ: span of control
IV 13. LEFT SELF
preserve all · ভুল: INNER · ফলো: NULL · ইউজ: complete list
IV 14. INNER SELF
only matched · ভুল: keep CEO · ফলো: when · ইউজ: filtered
IV 15. Multiple SELF
e/m/mm · ভুল: one join enough always · ফলো: depth · ইউজ: levels
IV 16. Hierarchical
tree columns · ভুল: normalize away · ফলো: CTE later · ইউজ: catalogs
IV 17. Performance
index manager_id · ভুল: ignore · ফলো: millions · ইউজ: DBA
IV 18. Indexing
PK + FK-like · ভুল: function on key · ফলো: EXPLAIN · ইউজ: tune
IV 19. Debug
check aliases/ON · ভুল: SELECT * panic · ফলো: sample · ইউজ: support
IV 20. Use cases
mgr/parent/replace/comment · ভুল: only HR · ফলো: social · ইউজ: product
IV 21. Categories
parent_category_id · ভুল: JOIN categories2 · ফলো: root NULL · ইউজ: e-com
IV 22. Replacement
replacement_id SELF · ভুল: hardcode · ফলো: chain · ইউজ: catalog
IV 23. Comments
parent_comment_id · ভুল: separate replies table always · ফলো: nested · ইউজ: social
IV 24. Doctors
supervisor_id · ভুল: RIGHT confuse · ফলো: senior · ইউজ: hospital
IV 25. Mental cue
same table? relationship? SELF · ভুল: new table · ফলো: teach · ইউজ: BA
IV 26. Mistake alias
same alias twice · ভুল: ignore error · ফলো: rename · ইউজ: juniors
IV 27. Mistake INNER
lose top nodes · ভুল: blame data · ফলো: LEFT · ইউজ: QA
IV 28. Playground
roles+ON+animate · ভুল: syntax dump · ফলো: predict · ইউজ: class
IV 29. Org project
10 reports · ভুল: only one query · ফলো: GROUP · ইউজ: portfolio
IV 30. Final rule
same table multi roles · ভুল: special keyword · ফলো: examples · ইউজ: memory
38
Homework
বাড়ি
প্রশ্ন (১৫):
- Employee + manager
- Without managers
- All managers
- Direct reports count
- Emp + mgr + senior
- Salary > manager
- Same manager pairs
- Duplicate pair control
- Category-parent
- Root categories
- Child categories
- Product-replacement
- Doctor-supervisor
- Comment-parent
- Org hierarchy report
উত্তর ১
e LEFT JOIN m ON e.manager_id=m.employee_id
উত্তর ২
… WHERE m.employee_id IS NULL
উত্তর ৩
SELECT DISTINCT m… JOIN ON e.manager_id=m.employee_id
উত্তর ৪
GROUP BY m.employee_id COUNT(e.employee_id)
উত্তর ৫
e LEFT m LEFT mm
উত্তর ৬
JOIN m WHERE e.salary > m.salary
উত্তর ৭
e1 JOIN e2 ON manager_id AND e1.id
উত্তর ৮
Use employee_id <
উত্তর ৯
c LEFT JOIN p ON parent_category_id
উত্তর ১০
WHERE parent_category_id IS NULL
উত্তর ১১
WHERE parent_category_id = :id
উত্তর ১২
p LEFT JOIN r ON replacement_product_id
উত্তর ১৩
d LEFT JOIN s ON supervisor_id
উত্তর ১৪
c LEFT JOIN p ON parent_comment_id
উত্তর ১৫
Combine SELF JOINs + filters as needed
39
Cheat Sheet + Playground Blueprint
৮ মিনিট
SELF JOIN
SAME TABLE → TWO+ ALIASES → ROLES → JOIN ROWS
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;
e = Employee · m = Manager
e.manager_id = m.employee_id
Use cases: Emp→Mgr · Child→Parent · Cat→Parent · Product→Replace · Comment→Parent · Emp↔EmpPlayground: Dataset → Role A/B → Columns → INNER/LEFT → RUN
→ Animate → Predict mode → Mistake mode (wrong ON)
Same table? Need row relationship? → SELF JOIN → aliases e/m → e.manager_id = m.employee_id