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

01

কেন JOIN দরকার?

১৫ মিনিট

জিজ্ঞেস করো

Employee information এক table-এ, department অন্য table-এ। নামের সাথে department নাম একসাথে কীভাবে দেখাব?

employees

employee_id	name	department_id
101	Rahim	1
102	Karim	2
103	Hasan	1
104	Nila	3

departments

department_id	department_name
1	IT
2	HR
3	Finance

সমস্যা

Employee table-এ department নাম নেই; department table-এ employee নাম নেই। কীভাবে একসাথে?

employees + departments ↓ matching department_id ↓ INNER JOIN ↓ Combined Result

Bad Design — এক বিশাল table

emp_id	name	dept_id	dept_name	manager
101	Rahim	1	IT	Mr. A
102	Karim	2	HR	Mr. B
103	Hasan	1	IT	Mr. A

একই IT information বারবার লিখতে হচ্ছে — update কঠিন, ভুল সহজ।

Teacher Tip (normalization সহজ করে): আলাদা table রাখি, প্রয়োজনে JOIN দিয়ে একসাথে দেখি।

Employee Table → department_id → Department Table → department_name

02

Real-Life Analogies

১০ মিনিট

School: Student ID দিয়ে student + department connect
Hospital: doctor_id দিয়ে patient + doctor
E-commerce: customer_id দিয়ে orders + customers
Bank: branch_id দিয়ে accounts + branches

JOIN = related information-এর table দুটোকে relationship দিয়ে একসাথে দেখা।

03

INNER JOIN কী?

১০ মিনিট

INNER JOIN শুধু matching rows return করে।

TABLE A TABLE B ○ ○ ○ ○ ○ ○ ○ ╲ matching ╱ ○ Keep ______ ______ / \______/ \ \ MATCH / \____________________/

INNER JOIN = Only Matching Records

04

প্রথম INNER JOIN Query

১৫ মিনিট

SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;

SELECT → কী দেখতে চাই? FROM → কোন table দিয়ে শুরু? INNER JOIN → কোন table connect? ON → কীভাবে related? employees.department_id = departments.department_id → matching key

05

Expected Output

৫ মিনিট

employee_id	name	department_id	department_name
101	Rahim	1	IT
102	Karim	2	HR
103	Hasan	1	IT
104	Nila	3	Finance

Rahim → department_id=1 → departments id=1 → IT

06

Step-by-Step Visual

৫ মিনিট

employees ↓ Take department_id ↓ Look inside departments ↓ Find same department_id ↓ Match found? / \ YES NO ↓ ↓ Keep Ignore ↓ Final Result

07

Match না থাকলে কী হয়?

১০ মিনিট

Hasan-এর department_id = 99, কিন্তু departments-এ ৯৯ নেই।

99 → No matching department → INNER JOIN → Row excluded Hasan result-এ আসবে না

মূল পার্থক্য: INNER JOIN unmatched বাদ দেয় (LEFT JOIN পরে শেখব)।

08

INNER JOIN vs WHERE

৮ মিনিট

SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.department_id WHERE departments.department_name = 'IT';

FROM → INNER JOIN → Matching rows → WHERE → IT only

JOIN = relationship; WHERE = ফলাফল ফিল্টার।

09

Selecting Specific Columns

৮ মিনিট

SELECT employees.employee_id, employees.employee_name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;

Professional SQL-এ দরকারি column নির্বাচন ভালো — SELECT * সবসময় নয়।

10

Table Aliases

১০ মিনিট

SELECT e.employee_name, d.department_name FROM employees AS e INNER JOIN departments AS d ON e.department_id = d.department_id;

e = employees d = departments

Alias পড়া সহজ করে — industry-তে খুব common।

11

INNER JOIN + WHERE Filters

১০ মিনিট

শুধু IT

SELECT e.employee_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id WHERE d.department_name = 'IT';

IT এবং HR

WHERE d.department_name IN ('IT', 'HR');

IT + salary > 60000

WHERE d.department_name = 'IT' AND e.salary > 60000;

12

INNER JOIN + ORDER BY

৮ মিনিট

SELECT e.employee_name, d.department_name, e.salary FROM employees e INNER JOIN departments d ON e.department_id = d.department_id ORDER BY d.department_name;

ORDER BY e.salary DESC;

13

INNER JOIN + GROUP BY

১০ মিনিট

প্রতিটি department-এ কতজন employee?

SELECT d.department_name, COUNT(*) AS employee_count FROM employees e INNER JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name;

employees → INNER JOIN → dept info → GROUP BY → COUNT → Report

14

INNER JOIN + Aggregates

১০ মিনিট

SELECT d.department_name, COUNT(*) AS employees, AVG(e.salary) AS avg_salary, MAX(e.salary) AS highest_salary, MIN(e.salary) AS lowest_salary, SUM(e.salary) AS total_salary FROM employees e INNER JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name;

15

INNER JOIN + HAVING

৮ মিনিট

যেসব department-এ ৫ জনের বেশি employee:

SELECT d.department_name, COUNT(*) AS employee_count FROM employees e INNER JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name HAVING COUNT(*) > 5;

JOIN → GROUP BY → COUNT → HAVING → Final

16

তিন টেবিল INNER JOIN

১৫ মিনিট

customers	orders	products
1 Rahim	101 → cust 1, prod 501	501 Laptop 80000
2 Karim	102 → cust 2, prod 502	502 Mobile 30000

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

Customers → customer_id → Orders → product_id → Products

17

E-commerce Schema

১০ মিনিট

Lab / project tables:

customers — customer_id, customer_name, city, country
products — product_id, product_name, category_id, price
categories — category_id, category_name
orders — order_id, customer_id, order_date, payment_method
order_items — order_item_id, order_id, product_id, quantity, unit_price

Sample scale: ~১৫ customers, ১০ products, ৫ categories, ২০ orders, ৩০ order items (Workbench-এ INSERT)।

18

Business Questions (১০টি)

২০ মিনিট

Customer + order: customers c INNER JOIN orders o ON c.customer_id=o.customer_id
Order + product: via order_items
Customer কোন product কিনেছে: ৩-table JOIN
Category বিক্রি: JOIN + SUM(quantity) GROUP BY category
Customer total purchase: SUM(qty*unit_price) GROUP BY customer
City-wise orders: JOIN + GROUP BY city
Category AVG price: products JOIN categories + AVG
Purchase > 50000: GROUP BY + HAVING SUM(...) > 50000
Category sold qty > 10: HAVING SUM(quantity) > 10
Top 10 customers: ORDER BY total DESC LIMIT 10

-- Q5 sketch SELECT c.customer_name, SUM(oi.quantity * oi.unit_price) AS total_purchase FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id INNER JOIN order_items oi ON o.order_id = oi.order_id GROUP BY c.customer_name;

Mistake: wrong key (order_id vs customer_id) — row গুলিয়ে যায় বা empty।

19

INNER vs অন্য JOINs (সংক্ষেপ)

৮ মিনিট

INNER JOIN → Only matching LEFT JOIN → All left + matching right RIGHT JOIN → All right + matching left FULL OUTER → All from both (MySQL-এ সরাসরি নয় — পরে)

আজ গভীর LEFT/RIGHT নয় — শুধু ধারণা।

20

INNER JOIN vs LEFT JOIN

১০ মিনিট

Hasan department_id=99 (match নেই):

INNER JOIN

Rahim → IT Karim → HR Hasan → ❌ excluded

LEFT JOIN (preview)

Rahim → IT Karim → HR Hasan → NULL dept

21

PK → FK → JOIN

৮ মিনিট

Primary Key (departments.department_id) ↑ Foreign Key (employees.department_id) ↓ Relationship → JOIN ON …

JOIN সবসময় শুধু PK/FK নয় — কিন্তু real-world-এ PK/FK সবচেয়ে common pattern।

22

Common Beginner Mistakes (১৫)

১০ মিনিট

ON ভুলে যাওয়া
Wrong columns join
employee_id দিয়ে department join করা
Ambiguous id — alias লাগে
সবসময় SELECT *
Alias ভুলে যাওয়া
Wrong ON condition
INNER-এ unmatched আশা করা
LEFT-এর সাথে গুলিয়ে ফেলা
Unexpected duplicates (one-to-many)
Relationship না বুঝে join
WHERE ভুল table-এ
JOIN পর GROUP BY ভুল
Row multiplication বুঝতে না পারা
NULL behavior ignore

23

One-to-Many Duplicates

১০ মিনিট

One Customer → Many Orders Rahim + 5 orders JOIN পর: Rahim Rahim Rahim Rahim Rahim

এটা সবসময় bug নয় — one-to-many relationship-এর স্বাভাবিক ফল।

24

Execution Flow (simplified)

৫ মিনিট

FROM → INNER JOIN ON match → Keep matching → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

25

MySQL Workbench Lab

২০ মিনিট

CREATE DATABASE join_lab; USE 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), FOREIGN KEY (department_id) REFERENCES departments(department_id) ); INSERT INTO departments VALUES (1,'IT'),(2,'HR'),(3,'Finance'); INSERT INTO employees VALUES (101,'Rahim',1,55000),(102,'Karim',2,48000), (103,'Hasan',1,72000),(104,'Nila',3,60000);

Steps: view → first JOIN → columns → aliases → WHERE → ORDER → GROUP → aggregates → HAVING → 3-table → mini report

SELECT e.employee_name, d.department_name, e.salary FROM employees e INNER JOIN departments d ON e.department_id = d.department_id ORDER BY d.department_name;

Hasan IT 72000 · Rahim IT 55000 · Karim HR 48000 · Nila Finance 60000

26

Interactive Activities (২০+)

১৫ মিনিট

কার্ডে ক্লিক করে উত্তর দেখো।

Activity 1 — Predict INNER JOIN employees↔departments

শুধু matching department_id rows

Activity 2 — Matching key এখানে কী?

department_id

Activity 3 — dept_id=99, match নেই →?

INNER JOIN-এ row বাদ

Activity 4 — Write ON for emp/dept

e.department_id = d.department_id

Activity 5 — Fix: JOIN without ON

ON clause যোগ করো

Activity 6 — Hasan unmatched: INNER or LEFT to keep?

LEFT রাখে; INNER বাদ

Activity 7 — Predict IT-only after JOIN+WHERE

শুধু IT employees

Activity 8 — Join emp+dept names

SELECT e.name, d.department_name … INNER JOIN …

Activity 9 — customers↔orders key?

customer_id

Activity 10 — orders↔products key?

product_id (often via order_items)

Activity 11 — 3-table sketch

c JOIN o ON customer_id JOIN p ON product_id

Activity 12 — Add WHERE IT

WHERE d.department_name='IT'

Activity 13 — GROUP BY dept count

COUNT(*) GROUP BY d.department_name

Activity 14 — HAVING count>5

HAVING COUNT(*) > 5

Activity 15 — Why 5 Rahim rows?

one-to-many orders — normal

Activity 16 — Why row disappeared?

no match on JOIN key

Activity 17 — Wrong key: emp_id=dept_id?

ভুল relationship — empty/wrong

Activity 18 — NL→SQL: emp + dept name

INNER JOIN ON department_id

Activity 19 — Debug ambiguous id

use e.id / d.id aliases

Activity 20 — Manual predict 2×2 match

শুধু overlapping keys

27

Mini Project — Sales Analytics

১৫ মিনিট

customers / products / categories / orders / order_items তৈরি করে:

Customer + Order report
Customer + Product report
Product + Category
Total sales by customer
Total sales by category
Orders count by customer
Avg order value by customer
Top 10 customers
Categories above sales threshold
Products quantity sold above threshold

28

Industry Use Cases

৮ মিনিট

Analyst: customer + sales
Data Scientist: multi-table features
Data Engineer: ETL joins
BI: dashboard datasets
Finance / HR / E-com / Healthcare: related entities

29

Performance Basics

৫ মিনিট

Index join keys (PK/FK)
Select needed columns
Avoid unnecessary joins
Understand one-to-many row growth
Large tables → JOIN cost matters

Optimizer গভীর নয় — আজ clear & correct JOIN।

30

Cheat Sheet

৫ মিনিট

SELECT columns
FROM table1 t1
INNER JOIN table2 t2
    ON t1.key = t2.key;

INNER JOIN → Match → Keep matching → Ignore unmatched

SELECT e.employee_name, d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

Two tables → find relationship → department_id → INNER JOIN → ON keys → only matching

31

Comparison Tables

১০ মিনিট

Topic	Simple takeaway
INNER vs LEFT	match only vs keep left unmatched
JOIN vs WHERE	relate vs filter
PK vs FK	identity vs reference
INNER vs UNION	relate columns vs stack rows
INNER vs CROSS	match condition vs every×every
ON vs WHERE	how related vs which rows keep after
SELECT * vs columns	all vs intentional

32

MCQ (২৫+)

১৫ মিনিট

MCQ 1. JOIN কেন? A) sort B) related tables একসাথে C) delete D) index

B

MCQ 2. INNER JOIN returns? A) all left B) matching only C) all right D) all both

B

MCQ 3. ON করে? A) filter only B) join condition/key C) sort D) group

B

MCQ 4. No match → INNER? A) NULL row B) excluded C) error D) duplicate

B

MCQ 5. Alias e মানে? A) error B) employees short name C) export D) end

B

MCQ 6. JOIN then WHERE? A) relationship then filter B) only filter C) only sort D) drop ON

A

MCQ 7. GROUP BY after JOIN? A) অসম্ভব B) সম্ভব — summaries C) শুধু LEFT D) error

B

MCQ 8. HAVING after JOIN? A) হ্যাঁ on aggregates B) না C) শুধু WHERE D) ON

A

MCQ 9. 3 tables? A) অসম্ভব B) multiple INNER JOIN C) শুধু UNION D) CROSS only

B

MCQ 10. PK/FK role? A) common relationship pattern B) required always C) aliases D) LIMIT

A

MCQ 11. One-to-many duplicates? A) always bug B) often normal C) illegal D) NULL

B

MCQ 12. INNER vs LEFT Hasan 99?

INNER excludes; LEFT keeps with NULL

MCQ 13. Ambiguous column?

qualify with table/alias

MCQ 14. SELECT * always?

better pick needed columns

MCQ 15. Wrong join key?

wrong/empty results

MCQ 16. Forget ON?

syntax/error or invalid join

MCQ 17. dept count query needs?

JOIN + GROUP BY + COUNT

MCQ 18. Top 10 customers?

JOIN + SUM + ORDER + LIMIT

MCQ 19. ON vs WHERE?

ON relates; WHERE filters result

MCQ 20. CROSS JOIN beginner?

every×every — not INNER

MCQ 21. UNION vs INNER?

UNION stacks rows; JOIN relates columns

MCQ 22. Index help?

join keys often indexed for speed

MCQ 23. Filter IT+salary?

WHERE after JOIN with AND

MCQ 24. Matching key name?

often FK = PK of other table

MCQ 25. Core memory?

INNER = matching only

MCQ 26. Unmatched expectation?

use LEFT not INNER

33

Viva

১০ মিনিট

Viva 1. JOIN কী?

related tables একসাথে দেখা

Viva 2. কেন দরকার?

data আলাদা table — একসাথে report

Viva 3. INNER JOIN?

শুধু matching rows

Viva 4. Returns কী?

শুধু যেগুলোর match আছে

Viva 5. ON কী?

কোন column দিয়ে relate

Viva 6. Matching key?

সাধারণত FK = অন্য table PK

Viva 7. Primary key?

unique identifier

Viva 8. Foreign key?

অন্য table-এর PK reference

Viva 9. Non-PK join সম্ভব?

হ্যাঁ, কিন্তু PK/FK common

Viva 10. No match?

INNER-এ বাদ

Viva 11. INNER vs LEFT?

LEFT unmatched left রাখে

Viva 12. Duplicate rows?

one-to-many — often normal

Viva 13. Alias?

table short name

Viva 14. ৩ table?

হ্যাঁ — chain INNER JOIN

Viva 15. GROUP BY সাথে?

হ্যাঁ

Viva 16. HAVING সাথে?

হ্যাঁ

Viva 17. ORDER BY সাথে?

হ্যাঁ

Viva 18. Core rules?

INNER=match; ON=key; no match=exclude

Viva 19. Ambiguous id?

table.column / alias

Viva 20. JOIN vs WHERE?

relate vs filter

Viva 21. Industry use?

customer+order+product reports

Viva 22. Performance tip?

index join keys; select needed cols

Viva 23. Wrong key symptom?

empty or wrong combos

Viva 24. SELECT * issue?

extra cols / ambiguity

Viva 25. Teaching flow?

FROM JOIN ON → WHERE → GROUP → HAVING → SELECT → ORDER

Viva 26. Why normalize then JOIN?

avoid duplicate dept data

34

Interview Top 30

১৫ মিনিট

IV 1. Emp + dept names

INNER JOIN ON department_id · ভুল: wrong key · ফলো: unmatched? · ইউজ: HR

IV 2. Customers + orders

ON customer_id · ভুল: order_id · ফলো: no orders? · ইউজ: sales

IV 3. Products + categories

ON category_id · ভুল: name join · ফলো: INNER vs LEFT · ইউজ: catalog

IV 4. Three tables

c→o→p chain · ভুল: missing ON · ফলো: add order_items · ইউজ: analytics

IV 5. Sales by category

JOIN + SUM GROUP BY · ভুল: COUNT only · ফলো: HAVING · ইউজ: BI

IV 6. Customers >50k

SUM HAVING · ভুল: WHERE SUM · ফলো: top N · ইউজ: CRM

IV 7. INNER vs LEFT

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

IV 8. ON vs WHERE

relate vs filter · ভুল: put join in WHERE only habit · ফলো: OUTER · ইউজ: review

IV 9. Duplicate rows

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

IV 10. One-to-many

1 parent many children · ভুল: bug · ফলো: diagram · ইউজ: modeling

IV 11. Correct join key

FK→PK · ভুল: similar names · ফলো: composite keys · ইউজ: DBA

IV 12. Debug bad JOIN

check ON keys + samples · ভুল: add DISTINCT blind · ফলো: EXPLAIN · ইউজ: support

IV 13. No matching row

INNER drops · ভুল: expect NULL · ফলো: LEFT · ইউজ: QA

IV 14. Indexes + JOIN

index keys help · ভুল: ignore · ফলো: large tables · ইউজ: performance

IV 15. Aliases why

clarity + disambiguate · ভুল: skip · ফলো: self-join later · ইউজ: code quality

IV 16. SELECT columns

needed only · ভুল: * always · ফলো: covering · ইউজ: ETL

IV 17. GROUP after JOIN

dept counts · ভুল: group before understand grain · ফলো: HAVING · ইউজ: reports

IV 18. HAVING after JOIN

filter groups · ভুল: WHERE COUNT · ফলো: threshold · ইউজ: BI

IV 19. ORDER + LIMIT top

after aggregates · ভুল: LIMIT early mentally · ফলো: pagination · ইউজ: dashboards

IV 20. Ambiguous column

qualify · ভুল: pick random · ফলো: same names · ইউজ: debugging

IV 21. PK/FK story

relationship foundation · ভুল: JOIN without model · ফলো: cascade · ইউজ: design

IV 22. Filter IT salary

JOIN + WHERE AND · ভুল: filter wrong table · ফলো: IN list · ইউজ: HR

IV 23. City order counts

JOIN customers+orders GROUP city · ভুল: DISTINCT city only · ফলো: map · ইউজ: logistics

IV 24. Avg price by category

JOIN products+categories AVG · ভুল: AVG after wrong group · ফলো: MIN/MAX · ইউজ: pricing

IV 25. UNION vs JOIN

stack vs relate · ভুল: interchangeable · ফলো: when each · ইউজ: interviews

IV 26. CROSS vs INNER

cartesian vs match · ভুল: forget ON → CROSS-like · ফলো: accidental · ইউজ: bugs

IV 27. Performance beginner

keys indexed; fewer cols; avoid useless joins · ভুল: SELECT * · ফলো: millions rows · ইউজ: scale

IV 28. Business NL→SQL

two tables→find key→INNER→ON · ভুল: start syntax · ফলো: teach · ইউজ: BA

IV 29. Common mistake ON wrong

empty set · ভুল: blame data only · ফলো: sample keys · ইউজ: triage

IV 30. Final rule

INNER=matching only; ON=how related · ভুল: keep unmatched · ফলো: LEFT class · ইউজ: daily SQL

35

Homework

বাড়ি

প্রশ্ন (১৫):

Customer + Order JOIN
Product + Category JOIN
Order + Customer JOIN
Three-table INNER JOIN
JOIN + WHERE
JOIN + IN
JOIN + ORDER BY
JOIN + GROUP BY
JOIN + HAVING
JOIN + COUNT
JOIN + SUM
JOIN + AVG
Top customers
Top categories
Multi-JOIN business report

উত্তর ১

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

উত্তর ২

products p INNER JOIN categories cat ON p.category_id=cat.category_id

উত্তর ৩

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

উত্তর ৪

c JOIN o ON customer_id JOIN p/oi ON product keys

উত্তর ৫

… JOIN … WHERE city='Dhaka' (example)

উত্তর ৬

… WHERE payment_method IN ('Card','bKash')

উত্তর ৭

… ORDER BY order_date DESC

উত্তর ৮

… GROUP BY category_name

উত্তর ৯

… HAVING SUM(...) > threshold

উত্তর ১০

COUNT(*) after JOIN GROUP BY

উত্তর ১১

SUM(qty*unit_price) GROUP BY customer

উত্তর ১২

AVG(price) GROUP BY category

উত্তর ১৩

ORDER BY total DESC LIMIT 10

উত্তর ১৪

sales by category ORDER BY SUM DESC LIMIT n

উত্তর ১৫

combine JOIN+WHERE+GROUP+HAVING+ORDER as needed

বিষয়সূচী

কেন JOIN দরকার?

employees

departments

Bad Design — এক বিশাল table

Real-Life Analogies

INNER JOIN কী?

প্রথম INNER JOIN Query

Expected Output

Step-by-Step Visual

Match না থাকলে কী হয়?

INNER JOIN vs WHERE

Selecting Specific Columns

Table Aliases

INNER JOIN + WHERE Filters

INNER JOIN + ORDER BY

INNER JOIN + GROUP BY

INNER JOIN + Aggregates

INNER JOIN + HAVING

তিন টেবিল INNER JOIN

E-commerce Schema

Business Questions (১০টি)

INNER vs অন্য JOINs (সংক্ষেপ)

INNER JOIN vs LEFT JOIN

INNER JOIN

LEFT JOIN (preview)

PK → FK → JOIN

Common Beginner Mistakes (১৫)

One-to-Many Duplicates

Execution Flow (simplified)

MySQL Workbench Lab

Interactive Activities (২০+)

Mini Project — Sales Analytics

Industry Use Cases

Performance Basics

Cheat Sheet

Comparison Tables

MCQ (২৫+)

Viva

Interview Top 30

Homework