Data Insights MySQL Masterclass — Part 4

SQL JOINS — Multiple Tables Ko Connect Karna

SQL ka sabse powerful concept — JOINS. Do ya zyada tables ko ek saath jodhke meaningful data nikalna seekho. INNER, LEFT, RIGHT, FULL, SELF aur CROSS JOIN — sab kuch real-world examples, visual diagrams aur interview questions ke saath Data Insights par.

📑 Is Part 4 Mein Aap Kya Sikhenge:

Multiple tables ko connect karne ke saare methods — ek ek karke:

• Setup: Sample Tables — employees, departments, projects, salaries
• Topic 1: INNER JOIN — Matching Records from Both Tables
• Topic 2: LEFT JOIN (LEFT OUTER JOIN) — All from Left + Matching from Right
• Topic 3: RIGHT JOIN (RIGHT OUTER JOIN) — All from Right + Matching from Left
• Topic 4: FULL OUTER JOIN — All Records from Both Tables
• Topic 5: SELF JOIN — Table Apne Aap Se Join
• Topic 6: CROSS JOIN — Cartesian Product
• Topic 7: Multiple Table Joins — 3+ Tables Ek Query Mein

text

JOINS samajhne ke liye hum 4 related tables banayenge. Pehle yeh saari tables create aur populate karo:

📦 Table 1: departments

CREATE TABLE departments (
dept_id    INT PRIMARY KEY,
dept_name  VARCHAR(50) NOT NULL,
location   VARCHAR(50)
);

INSERT INTO departments
VALUES
(1, 'IT', 'Bangalore'),
(2, 'HR', 'Mumbai'),
(3, 'Sales', 'Delhi'),
(4, 'Finance', 'Chennai'),
(5, 'Marketing', 'Pune');
-- No employees in Marketing

📦 Table 2: employees

CREATE TABLE employees (
emp_id      INT PRIMARY KEY,
emp_name    VARCHAR(50) NOT NULL,
dept_id     INT,
manager_id  INT,
salary      DECIMAL(10,2),
hire_date   DATE
);

INSERT INTO employees
VALUES
(101, 'Rahul Sharma', 1, NULL, 85000, '2020-03-15'),
(102, 'Priya Singh', 2, 101, 62000, '2020-07-01'),
(103, 'Amit Kumar', 3, 101, 48000, '2021-01-10'),
(104, 'Sneha Patel', 1, 101, 92000, '2019-11-20'),
(105, 'Ravi Verma', 2, 102, 53000, '2021-09-05'),
(106, 'Kavita Joshi', 3, 103, 45000, '2022-02-14'),
(107, 'Deepak Rao', 4, 101, 68000, '2020-05-30'),
(108, 'Anjali Mehta', 1, NULL, 95000, '2018-08-12'),
(109, 'Suresh Nair', 3, 103, 51000, '2022-06-20'),
(110, 'Neha Gupta', 4, 107, 71000, '2019-04-18'),
(111, 'Rohit Tiwari', NULL, 101, 55000, '2023-01-10');
-- No dept assigned

📦 Table 3: projects

CREATE TABLE projects (
project_id    INT PRIMARY KEY,
project_name  VARCHAR(50),
dept_id       INT,
budget        DECIMAL(12,2)
);

INSERT INTO projects
VALUES
(201, 'Cloud Migration', 1, 500000),
(202, 'HR Portal', 2, 200000),
(203, 'Sales Dashboard', 3, 150000),
(204, 'Data Analytics', 1, 350000),
(205, 'Mobile App', 6, 400000);
-- dept_id 6 doesn't exist!

📊 Tables Preview:

-- departments:

dept_id	dept_name	location
1	IT	Bangalore
2	HR	Mumbai
3	Sales	Delhi
4	Finance	Chennai
5	Marketing	Pune

No employees here

-- employees:

emp_id	emp_name	dept_id	manager_id	salary	hire_date
101	Rahul Sharma	1	NULL	85000.00	2020-03-15
102	Priya Singh	2	101	62000.00	2020-07-01
103	Amit Kumar	3	101	48000.00	2021-01-10
104	Sneha Patel	1	101	92000.00	2019-11-20
105	Ravi Verma	2	102	53000.00	2021-09-05
106	Kavita Joshi	3	103	45000.00	2022-02-14
107	Deepak Rao	4	101	68000.00	2020-05-30
108	Anjali Mehta	1	NULL	95000.00	2018-08-12
109	Suresh Nair	3	103	51000.00	2022-06-20
110	Neha Gupta	4	107	71000.00	2019-04-18
111	Rohit Tiwari	NULL	101	55000.00	2023-01-10

No dept

-- projects:

project_id	project_name	dept_id	budget
201	Cloud Migration	1	500000.00
202	HR Portal	2	200000.00
203	Sales Dashboard	3	150000.00
204	Data Analytics	1	350000.00
205	Mobile App	6	400000.00

dept 6 not exists

text

💡 Deliberately Mismatched Data:
• Marketing dept (id=5) — exists in departments but has no employees
• Rohit Tiwari (id=111) — employee with NULL dept_id (no department assigned)
• Mobile App project (id=205) — references dept_id=6 which doesn't exist

Yeh mismatches deliberately hain taaki LEFT, RIGHT aur FULL JOIN ka farak clearly dikhe!

text

🔍 Definition: INNER JOIN returns only those rows that have matching values in both tables. If a row in Table A has no matching row in Table B (based on the JOIN condition), that row is excluded from the result. It is the most commonly used type of JOIN.

🎯 Samjho Simple Bhasha Mein: INNER JOIN matlab "sirf woh dikha do jo DONO tables mein match karte hain." Socho ek wedding reception hai — andar sirf woh guest aayega jiska naam guest list mein bhi hai AUR invitation card bhi hai. Agar naam list mein hai but card nahi, ya card hai but list mein naam nahi — woh bahar. INNER JOIN mein bhi sirf perfect matches aate hain.

💡 Visual Diagram:
Table A Table B
○ ○
/ \ / \
/ \ ███ / \
\ / ███ \ /
\ / \ /
○ ○

███ = INNER JOIN result (intersection — only matching data)

💻 Real-World Code Examples:

Example 1: Employee ka naam aur unke department ka naam dikhao.

-- INNER JOIN: employees + departments
SELECT
e.emp_id,
e.emp_name,
e.salary,
d.dept_name,
d.location

FROM employees e
INNER
JOIN departments d
ON e.dept_id = d.dept_id;

-- Note: 'e' aur 'd' table aliases hain (shortcut names)

Example 2: INNER JOIN with WHERE filter — sirf IT department ke employees.

-- INNER JOIN + WHERE filter
SELECT
e.emp_name,
d.dept_name,
e.salary

FROM employees e
INNER
JOIN departments d
ON e.dept_id = d.dept_id

WHERE d.dept_name = 'IT'

ORDER BY e.salary DESC;

Example 3: Department wise employee count (JOIN + GROUP BY).

-- INNER JOIN + Aggregate
SELECT
d.dept_name,
COUNT(*) AS emp_count,
ROUND(AVG(e.salary), 2) AS avg_salary

FROM employees e
INNER
JOIN departments d
ON e.dept_id = d.dept_id

GROUP BY d.dept_name

ORDER BY emp_count DESC;

📊 Expected Output (Example 1):

emp_id	emp_name	salary	dept_name	location
101	Rahul Sharma	85000.00	IT	Bangalore
102	Priya Singh	62000.00	HR	Mumbai
103	Amit Kumar	48000.00	Sales	Delhi
104	Sneha Patel	92000.00	IT	Bangalore
105	Ravi Verma	53000.00	HR	Mumbai
106	Kavita Joshi	45000.00	Sales	Delhi
107	Deepak Rao	68000.00	Finance	Chennai
108	Anjali Mehta	95000.00	IT	Bangalore
109	Suresh Nair	51000.00	Sales	Delhi
110	Neha Gupta	71000.00	Finance	Chennai

10 rows in set

⚡ Notice: Result mein Rohit Tiwari (dept_id=NULL) nahi hai — kyunki uska dept_id kisi bhi department se match nahi karta. Aur Marketing department bhi nahi hai — kyunki usmein koi employee nahi hai. INNER JOIN sirf MATCHES dikhata hai!

⚠️ Common Mistakes:

• Mistake: ON clause bhool jaana → FROM employees INNER JOIN departments bina ON ke → CROSS JOIN ban jayega (sabke saath sab combine — massive result!).
  Fix: Hamesha ON clause likho: ON e.dept_id = d.dept_id
• Mistake: Ambiguous column name → SELECT dept_id — kaunsi table ka? Error aayega.
  Fix: Table alias use karo: SELECT e.dept_id ya d.dept_id
• Mistake: INNER JOIN mein unmatched rows expect karna → INNER JOIN sirf matches dikhata hai.
  Fix: Agar unmatched bhi chahiye toh LEFT/RIGHT JOIN use karo.

💬 Interview Questions:

text

🔍 Definition: LEFT JOIN returns ALL rows from the left table (Table A) and matching rows from the right table (Table B). If a row in the left table has no matching row in the right table, the result still includes that row — with NULL values for all right table columns.

🎯 Samjho Simple Bhasha Mein: LEFT JOIN matlab "left table ka har row zaroor aayega — match ho ya na ho." Socho attendance register hai — saare students ka naam zaroor dikhega. Agar kisi ne exam diya hai toh marks bhi dikhenge, agar nahi diya toh marks mein NULL dikhega. LEFT table ke saare rows guaranteed hain.

💡 Visual Diagram:
Table A Table B
○ ○
/ \ / \
/███\ ███ / \
\███/ ███ \ /
\ / \ /
○ ○

███ = LEFT JOIN result (entire left table + matching right data)

💻 Real-World Code Examples:

Example 1: SAARE employees dikhao — department assigned ho ya na ho.

-- LEFT JOIN: All employees + their dept info (if available)
SELECT
e.emp_id,
e.emp_name,
e.salary,
d.dept_name,
d.location

FROM employees e
LEFT
JOIN departments d
ON e.dept_id = d.dept_id;

Example 2: Employees jinhe koi department assign nahi hai (orphan employees).

-- Find employees WITHOUT any department
SELECT
e.emp_name,
e.salary,
d.dept_name

FROM employees e
LEFT
JOIN departments d
ON e.dept_id = d.dept_id

WHERE d.dept_id IS NULL;
-- No matching department found

Example 3: SAARE departments dikhao — employees hon ya na hon (LEFT table = departments).

-- All departments + employee count (including empty departments)
SELECT
d.dept_name,
d.location,
COUNT(e.emp_id) AS emp_count

FROM departments d
LEFT
JOIN employees e
ON d.dept_id = e.dept_id

GROUP BY d.dept_name, d.location

ORDER BY emp_count DESC;

📊 Expected Output (Example 1):

emp_id	emp_name	salary	dept_name	location
101	Rahul Sharma	85000.00	IT	Bangalore
102	Priya Singh	62000.00	HR	Mumbai
103	Amit Kumar	48000.00	Sales	Delhi
104	Sneha Patel	92000.00	IT	Bangalore
105	Ravi Verma	53000.00	HR	Mumbai
106	Kavita Joshi	45000.00	Sales	Delhi
107	Deepak Rao	68000.00	Finance	Chennai
108	Anjali Mehta	95000.00	IT	Bangalore
109	Suresh Nair	51000.00	Sales	Delhi
110	Neha Gupta	71000.00	Finance	Chennai
111	Rohit Tiwari	55000.00	NULL	NULL

No dept match

11 rows in set (ALL employees shown!)

📊 Expected Output (Example 3 — All Depts):

dept_name	location	emp_count
IT	Bangalore	3
Sales	Delhi	3
HR	Mumbai	2
Finance	Chennai	2
Marketing	Pune	0

Empty department shown!

text

⚠️ Common Mistakes:

• Mistake: LEFT JOIN ke baad WHERE mein right table ka column filter karna → Yeh LEFT JOIN ko INNER JOIN mein convert kar deta hai!
  Fix: Right table ka filter ON clause mein daalo, WHERE mein nahi: LEFT JOIN d ON e.dept_id = d.dept_id AND d.location = 'Mumbai'
• Mistake: COUNT(*) use karna LEFT JOIN ke saath → NULL rows bhi count ho jayengi.
  Fix: COUNT(e.emp_id) use karo — yeh sirf non-NULL values count karega.
• Mistake: Left aur Right table confuse karna → FROM ke baad wali table LEFT hai, JOIN ke baad wali RIGHT hai.

💬 Interview Questions:

text

🔍 Definition: RIGHT JOIN returns ALL rows from the right table and matching rows from the left table. If no match is found in the left table, NULL values fill the left table columns. It is the mirror image of LEFT JOIN.

🎯 Samjho Simple Bhasha Mein: RIGHT JOIN bilkul LEFT JOIN jaisa hai — bas ulta. Isme RIGHT table ke saare rows zaroor aayenge, left table ke matching records aayenge. Agar match nahi mila toh left side NULL hoga. Real life mein RIGHT JOIN kam use hota hai — zyaadatar log table order change karke LEFT JOIN use karte hain.

💡 Practical Truth: RIGHT JOIN logically LEFT JOIN ka ulta hai. A RIGHT JOIN B is same as B LEFT JOIN A. Isliye production code mein zyaadatar developers sirf LEFT JOIN use karte hain aur table order change kar lete hain. RIGHT JOIN rarely used hota hai but interview mein zaroor poochha jaata hai.

💻 Real-World Code Examples:

Example 1: SAARE departments dikhao — employees hon ya na hon (RIGHT table = departments).

-- RIGHT JOIN: All departments + matching employees
SELECT
e.emp_name,
e.salary,
d.dept_name,
d.location

FROM employees e
RIGHT
JOIN departments d
ON e.dept_id = d.dept_id;

Example 2: Departments jinmein koi employee nahi hai (empty departments find karo).

-- Find departments with ZERO employees
SELECT
d.dept_name,
d.location

FROM employees e
RIGHT
JOIN departments d
ON e.dept_id = d.dept_id

WHERE e.emp_id IS NULL;

-- Same result using LEFT JOIN (preferred way):
SELECT d.dept_name, d.location

FROM departments d
LEFT
JOIN employees e
ON d.dept_id = e.dept_id

WHERE e.emp_id IS NULL;

📊 Expected Output (Example 1):

emp_name	salary	dept_name	location
Rahul Sharma	85000.00	IT	Bangalore
Sneha Patel	92000.00	IT	Bangalore
Anjali Mehta	95000.00	IT	Bangalore
Priya Singh	62000.00	HR	Mumbai
Ravi Verma	53000.00	HR	Mumbai
Amit Kumar	48000.00	Sales	Delhi
Kavita Joshi	45000.00	Sales	Delhi
Suresh Nair	51000.00	Sales	Delhi
Deepak Rao	68000.00	Finance	Chennai
Neha Gupta	71000.00	Finance	Chennai
NULL	NULL	Marketing	Pune

No employees!

11 rows in set

📊 Expected Output (Example 2 — Empty Departments):

dept_name	location
Marketing	Pune

1 row in set

⚠️ Common Mistakes:

• Mistake: LEFT aur RIGHT JOIN mix up karna → Yaad rakho: FROM ke baad = LEFT table, JOIN ke baad = RIGHT table.
  Fix: Confusion se bachne ke liye hamesha LEFT JOIN use karo aur table order adjust karo.
• Mistake: RIGHT JOIN mein left table ke unmatched rows expect karna → Sirf RIGHT table ke saare rows guaranteed hain.

💬 Interview Questions:

text

🔍 Definition: FULL OUTER JOIN returns ALL rows from both tables. Matching rows are combined. Non-matching rows from either table appear with NULL values for the other table's columns. It is the union of LEFT JOIN and RIGHT JOIN.

🎯 Samjho Simple Bhasha Mein: FULL JOIN matlab "sab dikha do — left ka bhi, right ka bhi, match ho ya na ho." Socho ek class hai — kuch students ne fees bhari, kuch ne nahi. Kuch students ka naam fee register mein hai but school records mein nahi (transferred). FULL JOIN saare records ek table mein la dega — jinke dono jagah data hai, jinke sirf school records hain, aur jinke sirf fee records hain.

⚡ Important: MySQL mein FULL OUTER JOIN directly supported nahi hai!
MySQL mein FULL OUTER JOIN keyword kaam nahi karta. Hum UNION of LEFT JOIN + RIGHT JOIN use karke same result lete hain.

💻 Real-World Code Examples:

Example 1: FULL OUTER JOIN using UNION (MySQL workaround).

-- FULL OUTER JOIN in MySQL (using UNION)
-- Part 1: LEFT JOIN (all employees + matching departments)
SELECT
e.emp_name,
e.salary,
d.dept_name,
d.location

FROM employees e
LEFT
JOIN departments d
ON e.dept_id = d.dept_id

UNION

-- Part 2: RIGHT JOIN (all departments + matching employees)
SELECT
e.emp_name,
e.salary,
d.dept_name,
d.location

FROM employees e
RIGHT
JOIN departments d
ON e.dept_id = d.dept_id;

📊 Expected Output:

emp_name	salary	dept_name	location
Rahul Sharma	85000.00	IT	Bangalore
Priya Singh	62000.00	HR	Mumbai
Amit Kumar	48000.00	Sales	Delhi
Sneha Patel	92000.00	IT	Bangalore
Ravi Verma	53000.00	HR	Mumbai
Kavita Joshi	45000.00	Sales	Delhi
Deepak Rao	68000.00	Finance	Chennai
Anjali Mehta	95000.00	IT	Bangalore
Suresh Nair	51000.00	Sales	Delhi
Neha Gupta	71000.00	Finance	Chennai
Rohit Tiwari	55000.00	NULL	NULL
NULL	NULL	Marketing	Pune

← Employee without dept ← Dept without employees 12 rows in set (EVERYTHING from BOTH tables!)

text

⚠️ Common Mistakes:

• Mistake: MySQL mein directly FULL OUTER JOIN likhna → Syntax error aayega.
  Fix: LEFT JOIN UNION RIGHT JOIN use karo.
• Mistake: UNION ki jagah UNION ALL use karna → UNION ALL duplicates rakhta hai, UNION removes duplicates.
  Fix: FULL JOIN simulation mein UNION use karo (duplicates remove hone chahiye).

💬 Interview Questions:

text

🔍 Definition: A SELF JOIN is when a table is joined with itself. The same table is referenced twice using different aliases. It is used when rows in a table have relationships with other rows in the same table — most commonly for hierarchical/tree structures like employee-manager relationships.

🎯 Samjho Simple Bhasha Mein: SELF JOIN matlab "ek table apne aap se join ho rahi hai." Socho employees table mein har employee ka manager_id hai — aur manager bhi ek employee hai usi table mein! Rahul (emp_id=101) Priya ka manager hai (Priya ka manager_id=101). Toh hum same table ko 2 baar use karke employee ka naam aur uske manager ka naam ek saath nikal sakte hain.

💡 Key Rule: SELF JOIN mein same table ko 2 alag aliases dene padte hain — warna MySQL confuse ho jayega ki kaunsi table ki kaunsi column hai. Example: employees e1 (employee) aur employees e2 (manager).

💻 Real-World Code Examples:

Example 1: Har employee aur uske manager ka naam dikhao.

-- SELF JOIN: Employee + Manager Name
SELECT
e1.emp_name AS 'Employee',
e1.salary AS 'Emp Salary',
e2.emp_name AS 'Manager',
e2.salary AS 'Mgr Salary'

FROM employees e1
INNER
JOIN employees e2
ON e1.manager_id = e2.emp_id;

Example 2: LEFT JOIN se saare employees dikhao — including top managers (jinke upar koi nahi).

-- SELF JOIN with LEFT JOIN (include managers with no boss)
SELECT
e1.emp_name AS 'Employee',
IFNULL(e2.emp_name, 'No Manager (Top Level)') AS 'Manager'

FROM employees e1
LEFT
JOIN employees e2
ON e1.manager_id = e2.emp_id;

Example 3: Employees jinki salary unke manager se zyada hai.

-- Employees earning more than their manager!
SELECT
e1.emp_name AS 'Employee',
e1.salary AS 'Emp Salary',
e2.emp_name AS 'Manager',
e2.salary AS 'Mgr Salary'

FROM employees e1
INNER
JOIN employees e2
ON e1.manager_id = e2.emp_id

WHERE e1.salary > e2.salary;

📊 Expected Output (Example 1 — Employee-Manager):

Employee	Emp Salary	Manager	Mgr Salary
Priya Singh	62000.00	Rahul Sharma	85000.00
Amit Kumar	48000.00	Rahul Sharma	85000.00
Sneha Patel	92000.00	Rahul Sharma	85000.00
Ravi Verma	53000.00	Priya Singh	62000.00
Kavita Joshi	45000.00	Amit Kumar	48000.00
Deepak Rao	68000.00	Rahul Sharma	85000.00
Suresh Nair	51000.00	Amit Kumar	48000.00
Neha Gupta	71000.00	Deepak Rao	68000.00
Rohit Tiwari	55000.00	Rahul Sharma	85000.00

9 rows (Rahul & Anjali excluded — they have no manager)

📊 Expected Output (Example 3 — Salary > Manager):

Employee	Emp Salary	Manager	Mgr Salary
Sneha Patel	92000.00	Rahul Sharma	85000.00
Suresh Nair	51000.00	Amit Kumar	48000.00
Neha Gupta	71000.00	Deepak Rao	68000.00

3 employees earn more than their managers!

⚠️ Common Mistakes:

• Mistake: SELF JOIN mein alias na dena → MySQL error dega ambiguous table reference.
  Fix: Hamesha 2 alag aliases do: employees e1, employees e2
• Mistake: INNER JOIN se top-level managers miss hona → Jinke manager_id NULL hai woh exclude ho jayenge.
  Fix: LEFT JOIN use karo agar saare employees dikhane hain.
• Mistake: ON condition ulta likha → e1.emp_id = e2.manager_id galat hai.
  Fix: e1.manager_id = e2.emp_id — employee ka manager_id = manager ka emp_id.

💬 Interview Questions:

text

🔍 Definition: CROSS JOIN returns the Cartesian product of two tables — every row from Table A is paired with every row from Table B. If Table A has M rows and Table B has N rows, the result has M × N rows. No ON condition is used.

🎯 Samjho Simple Bhasha Mein: CROSS JOIN matlab "sabke saath sabko combine karo." Socho tumhare paas 3 shirts hain aur 4 pants hain — kitne combinations ban sakte hain? 3 × 4 = 12. Har shirt har pant ke saath pair hogi. CROSS JOIN exactly yahi karta hai — har row ko dusri table ki har row ke saath combine karta hai. Result bahut bada hota hai!

⚡ WARNING: CROSS JOIN bahut bada result deta hai! 1000 rows × 1000 rows = 10,00,000 rows! Production mein galti se CROSS JOIN mat karo — server hang ho sakta hai. Yeh mostly small lookup tables ke combinations ke liye use hota hai.

💻 Real-World Code Examples:

Example 1: Har employee ko har department ke saath pair karo.

-- CROSS JOIN (no ON clause!)
SELECT
e.emp_name,
d.dept_name

FROM employees e
CROSS
JOIN departments d

LIMIT 15;
-- 11 × 5 = 55 rows total, showing first 15

Example 2: Real Use Case — Generate all Size-Color combinations for products.

-- Create lookup tables
CREATE TABLE sizes (size_name VARCHAR(10));

INSERT INTO sizes
VALUES ('S'), ('M'), ('L'), ('XL');

CREATE TABLE colors (color_name VARCHAR(20));

INSERT INTO colors
VALUES ('Red'), ('Blue'), ('Black');

-- Generate ALL size-color combinations
SELECT
s.size_name,
c.color_name,
CONCAT(s.size_name, '-', c.color_name) AS variant_code

FROM sizes s
CROSS
JOIN colors c

ORDER BY s.size_name, c.color_name;

📊 Expected Output (Example 2 — Size-Color Combinations):

size_name	color_name	variant_code
L	Black	L-Black
L	Blue	L-Blue
L	Red	L-Red
M	Black	M-Black
M	Blue	M-Blue
M	Red	M-Red
S	Black	S-Black
S	Blue	S-Blue
S	Red	S-Red
XL	Black	XL-Black
XL	Blue	XL-Blue
XL	Red	XL-Red

12 rows (4 sizes × 3 colors = 12 combinations)

⚠️ Common Mistakes:

• Mistake: Galti se INNER JOIN mein ON clause bhool jaana → Yeh silently CROSS JOIN ban jaata hai — millions of rows!
  Fix: Hamesha ON clause double-check karo.
• Mistake: Large tables par CROSS JOIN lagana → Server crash ya extreme slow query.
  Fix: CROSS JOIN sirf small lookup/reference tables ke saath use karo.

💬 Interview Questions:

text

🔍 Definition: Multiple table joins connect 3 or more tables in a single query by chaining JOIN clauses. Each additional JOIN adds a new table with its own ON condition. MySQL processes joins left to right — first two tables join, then the result joins the third table, and so on.

🎯 Samjho Simple Bhasha Mein: Real-world databases mein data ek table mein nahi hota — 10-20 tables mein scattered hota hai. Ek employee ki info chahiye toh employee table se naam, department table se department name, project table se project name, aur salary table se salary details — sab join karke ek report banani padti hai. Yahi multiple table join hai!

💡 Join Chain Flow:
Table A → JOIN → Table B → JOIN → Table C → JOIN → Table D

Har JOIN mein ON clause specify karna zaroori hai. Har JOIN ka type alag ho sakta hai — pehla INNER, doosra LEFT, teesra INNER — mix and match allowed hai!

💻 Real-World Code Examples:

Example 1: 3 Tables — Employee + Department + Project details ek saath.

-- 3 Table JOIN: employees + departments + projects
SELECT
e.emp_name,
d.dept_name,
d.location,
p.project_name,
p.budget

FROM employees e
INNER
JOIN departments d
ON e.dept_id = d.dept_id
INNER
JOIN projects p
ON d.dept_id = p.dept_id

ORDER BY d.dept_name, e.emp_name;

Example 2: Mixed Joins — Employee + Department (LEFT) + Project (LEFT) + Manager Name (SELF JOIN).

-- 4 Tables: employees + departments + projects + manager (self)
SELECT
e.emp_name AS 'Employee',
IFNULL(d.dept_name, 'Unassigned') AS 'Department',
IFNULL(p.project_name, 'No Project') AS 'Project',
IFNULL(m.emp_name, 'Top Level') AS 'Manager',
e.salary

FROM employees e
LEFT
JOIN departments d
ON e.dept_id = d.dept_id
LEFT
JOIN projects p
ON d.dept_id = p.dept_id
LEFT
JOIN employees m
ON e.manager_id = m.emp_id

ORDER BY e.emp_name;

Example 3: Real Analytics — Department wise project count, employee count aur total budget.

-- Department summary with projects and employees
SELECT
d.dept_name,
COUNT(DISTINCT e.emp_id) AS total_employees,
COUNT(DISTINCT p.project_id) AS total_projects,
IFNULL(SUM(DISTINCT p.budget), 0) AS total_budget,
ROUND(AVG(e.salary), 2) AS avg_salary

FROM departments d
LEFT
JOIN employees e
ON d.dept_id = e.dept_id
LEFT
JOIN projects p
ON d.dept_id = p.dept_id

GROUP BY d.dept_name

ORDER BY total_budget DESC;

📊 Expected Output (Example 3 — Dept Summary):

dept_name	total_employees	total_projects	total_budget	avg_salary
IT	3	2	850000.00	90666.67
HR	2	1	200000.00	57500.00
Sales	3	1	150000.00	48000.00
Finance	2	0	0.00	69500.00
Marketing	0	0	0.00	NULL

text

⚠️ Common Mistakes:

• Mistake: Multiple joins mein duplicate rows aana → Jab ek employee 2 projects mein ho toh 2 rows aayengi!
  Fix: COUNT mein COUNT(DISTINCT e.emp_id) use karo, ya pehle understand karo ki one-to-many relationships kaise kaam karti hain.
• Mistake: Join order galat hona → LEFT JOIN ke baad INNER JOIN lagana pichle LEFT JOIN ka effect khatam kar sakta hai.
  Fix: Join chain carefully plan karo — sequence matters!
• Mistake: ON clause mein galat column reference → Subtle wrong results milenge bina error ke.
  Fix: Hamesha ER diagram ya table relationships samajh ke JOIN likho.

💬 Interview Questions:

JOIN Type	Returns	Unmatched Rows?	Use Case
INNER JOIN	Only matching rows	❌ Excluded	Standard data retrieval
LEFT JOIN	All left + matching right	✅ Left table guaranteed	Find orphan records
RIGHT JOIN	All right + matching left	✅ Right table guaranteed	Rarely used (use LEFT)
FULL OUTER JOIN	All from both tables	✅ Both sides	Data reconciliation
SELF JOIN	Table joins itself	Depends on JOIN type	Employee-Manager hierarchy
CROSS JOIN	M × N combinations	N/A (no ON condition)	Generate combinations

🎯 Top 5 JOIN Interview Questions — Rapid Fire

Q1: INNER JOIN vs LEFT JOIN difference? → INNER = only matches. LEFT = all left rows + matching right.

Q2: Find employees with no department? → LEFT JOIN ... WHERE dept_id IS NULL

Q3: Find employee earning more than manager? → SELF JOIN with WHERE e1.salary > e2.salary

Q4: MySQL mein FULL OUTER JOIN kaise karo? → LEFT JOIN UNION RIGHT JOIN

Q5: CROSS JOIN ka result size? → M × N rows (Cartesian Product)

Next: Data Insights MySQL Masterclass — Part 5

Part 5 mein hum cover karenge: Subqueries — WHERE mein Subquery, FROM mein, SELECT mein, Correlated Subquery, EXISTS vs IN — SQL ka advanced level with real-world analytics queries aur interview questions Data Insights par.

Happy Querying & Keep Learning SQL! 🚀