Joins — INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF

4.4.6 · Coding › Databases

Joins exist KYU karte hain?

Hum do running tables use karenge:

Employees

emp_id	name	dept_id
1	Asha	10
2	Bilal	20
3	Cara	NULL

Departments

dept_id	dept_name
10	Sales
30	Legal

Note karo ye deliberate mismatches: emp Cara ka koi department nahi hai; dept 30 (Legal) ka koi employee nahi hai; dept 20 Employees mein hai lekin Departments mein nahi hai.

Chhe joins

Definition INNER JOIN

Sirf wahi rows return karta hai jahan condition DONO sides pe match ho. Kisi bhi side ki unmatched rows drop ho jaati hain.

SELECT e.name, d.dept_name
FROM Employees e
INNER JOIN Departments d ON e.dept_id = d.dept_id;

Result: sirf Asha–Sales (dept_id 10 hi ek aisa pair hai jo dono mein present hai). Bilal (20 Dept mein nahi), Cara (NULL), Legal (koi emp nahi) sab gayab ho jaate hain.

Definition LEFT (OUTER) JOIN

LEFT table ki saari rows return karta hai; matched right columns fill ho jaate hain, unmatched right columns NULL ban jaati hain.

SELECT e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id;

Result: Asha–Sales, Bilal–NULL, Cara–NULL. Har employee bach jaata hai.

Definition CROSS JOIN

Cartesian product: har left row ko har right row ke saath pair karta hai. Koi ON condition nahi. Agar left mein $m$ rows hain aur right mein $n$ rows, toh output mein $m \times n$ rows hongi.

SELECT e.name, d.dept_name FROM Employees e CROSS JOIN Departments d;

Result: $3 \times 2 = 6$ rows.

Definition SELF JOIN

Ek table ko do aliases use karke khud se join kiya jaata hai. Hierarchies (employee → manager) ke liye ya ek hi table ke andar rows compare karne ke liye use hota hai.

SELECT e.name AS emp, m.name AS manager
FROM Employees e JOIN Employees m ON e.manager_id = m.emp_id;

Figure — Joins — INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF

HOW joins pehle principles se bante hain (Derivation)

Counting check

Hamare data ke liye: INNER mein 1 row hai. Left-unmatched = {Bilal, Cara} = 2. Right-unmatched = {Legal} = 1. $∣ LEFT ∣ = 1 + 2 = 3, ∣ RIGHT ∣ = 1 + 1 = 2, ∣ FULL ∣ = 1 + 2 + 1 = 4.$

Worked examples

Worked example Q1: Aisa employee dhundo jiske paas koi department NAHI hai ("anti-join")

SELECT e.name
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

LEFT JOIN kyu? Humein employees ko tab bhi rakhna hai jab koi department match na ho. WHERE d.dept_id IS NULL kyu? Padded rows mein right columns NULL hoti hain — us NULL pe filter karne se unmatched wale isolate ho jaate hain. Result: Bilal, Cara.

Worked example Q2: Departments jinmein koi employee nahi

SELECT d.dept_name
FROM Departments d
LEFT JOIN Employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

Ye step kyu? Departments ko LEFT pe rakhne se saare departments bachte hain; NULL-test orphans ko dhundh leta hai. Result: Legal.

Worked example Q3: Aise employees pair karo jo ek hi department share karte hain (SELF JOIN)

SELECT a.name, b.name
FROM Employees a
JOIN Employees b ON a.dept_id = b.dept_id AND a.emp_id < b.emp_id;

a.emp_id < b.emp_id kyu? Iske bina har pair do baar aata (A,B aur B,A) aur self-pairs bhi (A,A). Strict inequality har unordered pair ko ek baar rakhti hai.

Worked example Q4: Size chart banao — har product × har size (CROSS)

SELECT p.name, s.size FROM Products p CROSS JOIN Sizes s;

CROSS kyu? Humein genuinely har combination chahiye — koi matching condition exist nahi karti. Grids/calendars generate karne ke liye useful hai.

Common mistakes (Steel-manned)

Common mistake Join condition ko WHERE mein daalna LEFT JOIN ko tod deta hai

Galat lekin tempting:

SELECT e.name, d.dept_name
FROM Employees e LEFT JOIN Departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Sales';

Kyu sahi lagta hai: "Main sirf Sales ke liye filter kar raha hoon, aur ye ab bhi LEFT JOIN hai." Kyu galat hai: padded NULL rows mein d.dept_name = NULL hota hai, aur NULL = 'Sales' true nahi hota, isliye WHERE unhe delete kar deta hai — tumhara LEFT JOIN silently INNER JOIN mein degrade ho jaata hai. Fix: right-table conditions ON clause mein rakho: ON e.dept_id = d.dept_id AND d.dept_name = 'Sales'. ON padding se pehle filter karta hai; WHERE padding ke baad filter karta hai.

Recall Feynman: ek 12-saal ke bacche ko samjhao

Do lists imagine karo. List A: bacche aur lunch-table number jinpe woh baithte hain. List B: table numbers aur har table pe kya khana hai. Ek join har bacche ko uske table ke khaane se match karta hai.

INNER: sirf wahi bacche dikhao jo kisi real food-table pe baithte hain (floor pe baitha baccha aur khaali table dono skip karo).
LEFT: har bacche ko dikhao — agar kisi bacche ka koi table nahi, toh "no food" likho.
RIGHT: har table dikhao — khaali table bhi "koi nahi hai" bolta hai.
FULL: har bacche AUR har table ko dikhao, jahan missing ho wahan blank rakho.
CROSS: maan lo har baccha har table pe baith sakta hai — saare combos list karo.
SELF: ek hi list use karke bacchon ko usi table ke doosre bacchon se match karo.

Active recall

INNER JOIN kaun si rows return karta hai?

Sirf wahi rows jo ON condition dono tables pe match karti hain; kisi bhi side ki unmatched rows drop ho jaati hain.

LEFT JOIN kis table ki saari rows rakhta hai, aur miss hone par kya fill karta hai?

LEFT table ki saari rows; unmatched RIGHT columns NULL ban jaati hain.

A RIGHT JOIN B ko LEFT use karke kaise rewrite karoge?

B LEFT JOIN A (same result, order swap karo).

FULL OUTER JOIN kaun se do joins ka union hai?

LEFT JOIN ∪ RIGHT JOIN.

m aur n rows ka CROSS JOIN kitni rows produce karta hai?

m × n (Cartesian product), koi ON condition nahi hoti.

Har join ko kaun si operations ke sequence se derive kiya ja sakta hai?

CROSS JOIN (Cartesian product) → ON predicate se FILTER (INNER milta hai) → unmatched outer rows ko NULL se PAD karo.

No match wali rows dhundne ka standard pattern (anti-join) kya hai?

Dusri table LEFT JOIN karo, phir WHERE IS NULL.

RIGHT table pe WHERE mein filter kyu LEFT JOIN ko INNER JOIN bana deta hai?

Padded NULL rows comparison fail kar deti hain (NULL = value true nahi hota), isliye WHERE unhe padding ke baad remove kar deta hai.

LEFT JOIN semantics preserve karne ke liye right-table conditions kis clause mein honi chahiye?

ON clause mein (padding se pehle filter karta hai), WHERE mein nahi (padding ke baad filter karta hai).

SELF JOIN kis kaam aata hai?

Table ko do aliases ke zariye khud se join karne ke liye — jaise employee→manager hierarchies ya ek hi table ke andar rows compare karna.

INNER JOIN ON e.dept_id = d.dept_id NULL dept_id wali row kyu drop kar deta hai?

NULL kisi bhi cheez se equal nahi hota (NULL se bhi nahi), isliye NULL key kisi bhi row se match nahi karti.

Self-join pairing mein a.id < b.id kyu add karte hain?

Duplicate (A,B & B,A) pairs aur self-pairs (A,A) avoid karne ke liye.

Connections

Relational Algebra — selection $σ$ , projection $π$ , Cartesian product $\times$ joins ke aadhar hain.
Normalization — data ko tables mein split karta hai; joins use reassemble karte hain.
NULL semantics in SQL — isliye unmatched outer rows us tarah behave karte hain.
Indexes — optimizer real $m \times n$ kaam kaise avoid karta hai (hash join, merge join, nested loop).
Foreign Keys — woh relationships jinhe joins typically follow karte hain.
GROUP BY and Aggregation — aksar joins ke saath combine hota hai.