Lesson 11 � Intermediate

JOINs:
MULTIPLE TABLES KA MASTER.

Real databases mein data ek table mein nahi hota � students ek jagah, courses doosri jagah. JOIN se dono tables ko mila ke ek saath query karte ho. Yeh SQL ka sabse powerful feature hai.

? 24 min✓ Intermediate✓ Prerequisite: Aggregate

WHY: JOIN kyun zaroori hai?

Socho ek company ka database hai � ek table mein employees hain, doosre mein unke departments. Agar tumhe pata karna hai ki kaunsa employee kis department mein hai, toh dono tables ko ek karna padega. Yahi kaam JOIN karta hai.

INNER JOIN

Sirf matching rows dikhata hai � dono tables mein jo rows ka data same hai wohi aayega. Jaise students jinke course exist karta hai courses table mein.

LEFT JOIN

Left table ki saari rows dikhata hai, right table mein matching nahi ho toh NULL. Jaise sab students dikhao chahe unka course assign ho ya na ho.

RIGHT JOIN

Right table ki saari rows dikhata hai, left mein matching nahi ho toh NULL. Jaise sab courses dikhao chahe koi student ho ya na ho.

FULL JOIN

Dono tables ki saari rows dikhata hai. Matching hogi toh combined, nahi toh NULL. Poora data ek saath.

HOW: JOIN ka syntax samjho

JOIN ka formula simple hai � FROM table1 JOIN table2 ON condition. Condition mein batate hain ki dono tables ka kaunsa column match karna hai.

-- INNER JOIN (default)
SELECT s.name, c.course_name, c.duration
FROM students s
INNER JOIN courses c ON s.course_id = c.id;

-- LEFT JOIN
SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c ON s.course_id = c.id;

-- RIGHT JOIN
SELECT s.name, c.course_name
FROM students s
RIGHT JOIN courses c ON s.course_id = c.id;

-- FULL JOIN
SELECT s.name, c.course_name
FROM students s
FULL JOIN courses c ON s.course_id = c.id;

ℹ

Mental model: JOIN ek Venn diagram hai. INNER = intersection, LEFT = left circle complete, RIGHT = right circle complete, FULL = dono circles. ON clause batata hai ki matching kaise hogi.

Sample data setup

Pehle tables banao aur data daalo taaki JOIN practice kar sako:

-- Students table
CREATE TABLE students (
 id INT PRIMARY KEY,
 name VARCHAR(100),
 course_id INT
);

-- Courses table
CREATE TABLE courses (
 id INT PRIMARY KEY,
 course_name VARCHAR(100),
 duration VARCHAR(50)
);

-- Data daalo
INSERT INTO students VALUES
(1, 'Aman', 1),
(2, 'Priya', 2),
(3, 'Rahul', 1),
(4, 'Sneha', NULL);

INSERT INTO courses VALUES
(1, 'Data Science', '6 months'),
(2, 'Python', '3 months'),
(3, 'Machine Learning', '4 months');

JOIN types detailed mein

INNER JOIN

Sirf woh rows aayengi jahan ON condition match karti hai. Sneha ka course_id NULL hai toh woh nahi aayegi. ML course ke liye koi student nahi hai toh woh bhi nahi aayega.

LEFT JOIN

Sab students dikhenge. Aman, Priya, Rahul ke saath course ka naam bhi aayega. Sneha ke liye course columns mein NULL hoga kyunki uska course assign nahi hai.

RIGHT JOIN

Sab courses dikhenge. Data Science aur Python ke saath students bhi aayenge. Machine Learning ke liye student columns NULL hoga kyunki koi student nahi hai.

FULL JOIN

Sab students aur sab courses dikhenge. Jo match karega woh combined, baaki NULL. Poora data ek jagah.

Self JOIN: table apne aap se

Kabhi kabhi ek table ko apne aap se join karna hota hai � jaise students mein se woh dhundho jo ek hi course mein hain:

-- Self join: ek hi course ke students
SELECT a.name, b.name AS friend
FROM students a
JOIN students b ON a.course_id = b.course_id AND a.id != b.id;

ℹ

Pro tip: Self JOIN mein table ko alag-alag aliases dete hain (a, b) taaki SQL samajh sake ki kaunsa row kaunsa hai. a.id != b.id lagana mat bhoolna warna student apne aap se match hoga.

Try it: SQL query likho

Neeche ka editor SQL jaisa hai. Yahan apni JOIN query likho aur "Run Query" dabao. Screen par sample results dikhenge � yeh browser-based simulation hai, real database nahi.

SQL playgroundStudents aur courses tables se results dikhenge

query.sql

Run Query dabayein

Quick check

Ek INNER JOIN likho jo students aur courses tables ko course_id ke basis par join kare.

SELECT * se shuru karo, FROM students likho, INNER JOIN courses ON condition lagao.

Common JOIN mistakes

ON condition bhoolna: JOIN mein hamesha ON lagana zaroori hai � warna Cartesian product hoga (har row har row se match hogi).
Column name ambiguity: Dono tables mein same column name ho toh table alias use karo (s.name, c.name).
NULL handling: LEFT/RIGHT JOIN mein NULL values aati hain � IS NULL ya COALESCE use karo unko handle karne ke liye.

JOINs complete?

Ab Subqueries par chalo � nested queries seekho jo complex data extraction ko easy banati hain.

JOINs:MULTIPLE TABLES KA MASTER.