Subqueries — correlated vs uncorrelated — Hinglish notes

4.4.7 · Coding › Databases

Subqueries exist kyun karte hain?

Exactly har tarah ki subquery kya hoti hai?

Dono mein fark kaise karte hain (the test)

Figure — Subqueries — correlated vs uncorrelated

Worked Example 1 — Uncorrelated (scalar)

Worked example Company average se zyada kamaane waale employees

SELECT name, salary
FROM   employee
WHERE  salary > (SELECT AVG(salary) FROM employee);

Yeh step kyun?

Inner SELECT AVG(salary) FROM employee mein koi outer column nahi → uncorrelated.
Yeh ek baar chalta hai, ek single number return karta hai, maano 50000.
Query effectively ban jaati hai WHERE salary > 50000.
Yeh sahi kyun hai: average poori table ki ek fixed property hai, toh ise ek baar compute karna sahi aur efficient hai.

Worked Example 2 — Uncorrelated (list with IN)

Worked example Customers jinhoone kam se kam ek order diya

SELECT name
FROM   customer
WHERE  id IN (SELECT customer_id FROM orders);

Yeh step kyun?

Inner query customer_ids ki ek list produce karta hai — outer row se independent → uncorrelated.
Ek baar bana, phir har outer id ko uske against membership-test kiya jaata hai.

Worked Example 3 — Correlated (EXISTS)

Worked example Customers jinhoone kam se kam ek order diya — correlated version

SELECT c.name
FROM   customer c
WHERE  EXISTS (SELECT 1
               FROM   orders o
               WHERE  o.customer_id = c.id);   -- c.id OUTER query se hai!

Yeh step kyun?

Inner query c.id (outer) ko reference karta hai → correlated.
Har customer row ke liye, DB poochta hai: "kya koi order hai is customer ki id ke saath?" EXISTS pehle match pe ruk jaata hai (short-circuits), toh yeh bahut fast ho sakta hai.
Yahan IN ki jagah EXISTS kyun: EXISTS NULLs ko safely handle karta hai aur short-circuit kar sakta hai; IN mein list mein NULL ho toh surprising results aa sakte hain.

Worked Example 4 — Correlated (per-row aggregate)

Worked example Employees jo

apne department ke average se zyada kamate hain

SELECT e.name, e.salary
FROM   employee e
WHERE  e.salary > (SELECT AVG(e2.salary)
                   FROM   employee e2
                   WHERE  e2.dept_id = e.dept_id);  -- e.dept_id pe depend karta hai

Yeh step kyun?

Threshold har row ke liye alag hai (row ke department pe depend karta hai) → correlated hona zaroori hai.
Example 1 se compare karo: wahan threshold ek single global constant tha (uncorrelated). Sawaal hi type decide karta hai.

Recall Feynman: ek 12-saal ke bachche ko samjhao

Ek worksheet imagine karo. Uncorrelated: teacher board pe ek number likhti hai (maano class average), aur tum apna score usi same number se compare karte ho — board ek baar likha jaata hai. Correlated: har student ko apne group ka average ek alag table se har baar khud dhundna padta hai — toh lookup har student ke liye dobara hota hai. Same idea, lekin ek shared jawab hai aur doosra har row ka personal jawab.

Flashcards

Correlated aur uncorrelated subquery mein fark karne ka ek test kya hai?

Kya inner query outer query ke kisi column/alias ko reference karti hai. Haan → correlated; nahi → uncorrelated.

Ek uncorrelated subquery logically kitni baar evaluate hoti hai?

Ek baar, outer query ke uska result use karne se pehle.

Ek correlated subquery logically kitni baar evaluate hoti hai?

Outer query ki har row ke liye ek baar (semantically — optimizers ise rewrite kar sakte hain).

Subquery ke bina WHERE salary > AVG(salary) directly kyun nahi likh sakte?

WHERE aggregation se pehle har row pe evaluate hota hai; tumhe pehle aggregate compute karna hota hai, jo (uncorrelated) subquery provide karta hai.

Correlated existence checks ke liye EXISTS ko IN ke upar kyun prefer kiya jaata hai?

EXISTS pehle match pe short-circuit karta hai aur NULLs ke saath predictably behave karta hai, jabki IN mein NULLs ke saath surprising/empty results aa sakte hain.

Steel-man: kya correlated subqueries hamesha slow hoti hain?

Nahi — yeh sirf logical semantics hai; optimizers inhe aksar joins/semi-joins mein rewrite kar dete hain, toh performance ek uncorrelated query ke barabar ho sakti hai.

N outer rows aur inner cost c ke saath ek correlated subquery ka naive cost batao.

~N·c (plus per-row comparisons), uncorrelated ke ~c ke muqable.

"Employees jo apne department ke average se upar hain dhundho" — correlated ya uncorrelated, aur kyun?

Correlated — threshold har row ke dept_id pe depend karta hai, toh inner aggregate ko har row ke liye recompute karna padta hai.

Connections

Joins — inner outer cross — correlated subqueries ko aksar joins ke roop mein rewrite kiya ja sakta hai.
Aggregate Functions GROUP BY — subqueries hi woh tarika hai jisse tum rows ko aggregates se compare karte ho.
EXISTS vs IN vs ANY — sahi correlation operator chunna.
Query Optimizer Execution Plans — DB kaise decide karta hai loop kare ya rewrite kare.
NULL handling in SQL — kyun IN misbehave kar sakta hai jahan EXISTS safe hai.