SQL clauses — WHERE, GROUP BY, HAVING, ORDER BY, LIMIT

4.4.5 · Coding › Databases

Logical Processing Order (yeh yaad karo)

Figure — SQL clauses — WHERE, GROUP BY, HAVING, ORDER BY, LIMIT

WHERE — rows filter karo

Worked example Basic filtering

SELECT name, salary
FROM employees
WHERE salary > 50000 AND dept = 'Sales';

Ye step kyun? Dono conditions per-row facts hain (is employee ki salary, is employee ka dept), isliye WHERE sahi jagah hai — koi aggregation nahi chahiye.

Worked example WHERE mein aggregate kyun nahi kar sakte

-- ❌ ERROR
SELECT dept FROM employees WHERE COUNT(*) > 5;

Ye kyun fail hota hai? COUNT(*) ek group ki property hai, lekin WHERE stage mein groups exist hi nahi karte (GROUP BY baad mein chalta hai). Fix hai HAVING.

GROUP BY — rows ko groups mein collapse karo

Worked example Har department mein count

SELECT dept, COUNT(*) AS headcount, AVG(salary) AS avg_sal
FROM employees
WHERE salary > 0
GROUP BY dept;

Ye step kyun? WHERE pehle garbage rows (salary 0) drop karta hai, phir GROUP BY dept ke anusaar partition karta hai, phir COUNT/AVG har partition ko summarize karte hain. Output: ek row per department.

HAVING — groups filter karo

Worked example 5 se zyada log wale departments

SELECT dept, COUNT(*) AS headcount
FROM employees
WHERE salary > 0          -- per-row filter (sasta, pehle karo)
GROUP BY dept
HAVING COUNT(*) > 5;      -- per-group filter (aggregate chahiye)

Ye step kyun? salary > 0 ek row fact hai → WHERE. COUNT(*) > 5 ek group fact hai → HAVING. COUNT(*) > 5 ko WHERE mein daalna error dega; salary > 0 ko HAVING mein daalna kaam toh karega lekin slower hoga aur semantically galat hoga.

ORDER BY — result sort karo

Worked example Sort karo aur alias use karo

SELECT dept, COUNT(*) AS headcount
FROM employees
GROUP BY dept
ORDER BY headcount DESC, dept ASC;

Ye step kyun? headcount ek alias hai jo SELECT mein create hua; ORDER BY SELECT ke baad chalta hai isliye alias visible hai. (WHERE headcount > 5 try karo aur ye fail hoga — WHERE SELECT se pehle chalta hai.)

LIMIT — sirf kuch rows rakho

Worked example Top 3 departments

SELECT dept, COUNT(*) AS headcount
FROM employees
GROUP BY dept
ORDER BY headcount DESC
LIMIT 3;

Ye step kyun? "Top 3" sirf sorting ke baad hi meaningful hota hai, isliye ORDER BY pehle aana chahiye, phir LIMIT trim karta hai. ORDER BY ke bina, "LIMIT 3" 3 arbitrary rows return karta hai — ek classic silent bug.

Common mistake Common errors ko steel-man karo

1. "Main apna alias WHERE mein use karunga." Kyun sahi lagta hai: Tumne SELECT salary*12 AS annual upar type kiya, toh ye lagta hai ki ye har jagah available hai. Fix: WHERE SELECT se pehle chalta hai — alias abhi exist hi nahi karta. Expression repeat karo: WHERE salary*12 > 60000, ya HAVING/subquery use karo.

2. "WHERE aur HAVING interchangeable hain." Kyun sahi lagta hai: Dono filter karte hain aur boolean conditions use karte hain. Fix: WHERE = rows (pre-group, no aggregates), HAVING = groups (post-group, aggregates allowed). Alag stages hain.

3. "LIMIT 10 mujhe top 10 deta hai." Kyun sahi lagta hai: Results aksar accidentally sorted dikhte hain (insertion/index order se). Fix: ORDER BY nahi = koi guaranteed order nahi. "Top N" ke liye hamesha LIMIT ke saath ORDER BY lagao.

4. "WHERE x = NULL NULLs dhundta hai." Kyun sahi lagta hai: = baaki sab ke liye kaam karta hai. Fix: NULL ka matlab hai "unknown"; NULL = NULL NULL hai (TRUE nahi). WHERE x IS NULL use karo.

5. GROUP BY ke saath SELECT mein non-aggregated columns daalna. Kyun sahi lagta hai: Tum har group ke liye extra info chahte ho. Fix: Ye ambiguous hai ki kaunsi row ki value dikhani hai. Ya toh column ko GROUP BY mein add karo ya ise aggregate mein wrap karo (MAX, MIN, etc.).

Recall Feynman: 12-saal ke bachche ko explain karo

Ek bade box mein trading cards imagine karo.

WHERE = "woh cards fenko jo mujhe nahi chahiye" (ek card at a time).
GROUP BY = "dher lagao, ek dher per team."
HAVING = "woh dher puri tarah fenko jinmein 5 se kam cards hain."
ORDER BY = "bache hue dher ko sabse bade se pehle line mein lagao."
LIMIT = "mujhe bas top 3 dher do." Tum ek dher ko tab nahi fek sakte jab dher bane hi nahi — isliye HAVING dher banne ke baad aata hai, pehle nahi. Simple!

Flashcards

SQL mein clauses ka logical processing order kya hai?

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

WHERE aggregate functions jaise COUNT(*) use kyun nahi kar sakta?

WHERE, GROUP BY se pehle chalta hai, isliye groups (aur unke aggregates) abhi exist hi nahi karte.

WHERE aur HAVING mein ek-line difference kya hai?

WHERE grouping se pehle individual rows filter karta hai; HAVING grouping ke baad poore groups filter karta hai (aggregates use kar sakta hai).

Kya ORDER BY ek SELECT alias use kar sakta hai? Kyun?

Haan — ORDER BY, SELECT ke baad chalta hai, isliye aliases pehle se defined hote hain.

Kya WHERE ek SELECT alias use kar sakta hai? Kyun?

Nahi — WHERE, SELECT se pehle chalta hai, isliye alias abhi exist hi nahi karta.

"Top N" ke liye LIMIT ko ORDER BY ke saath kyun pair karna chahiye?

ORDER BY ke bina result order unspecified hota hai, isliye LIMIT arbitrary rows return karta hai, na ki sabse bade wale.

GROUP BY ke saath, SELECT mein kaunse columns bare appear ho sakte hain?

Sirf grouping columns; baaki saare columns aggregate function ke andar hone chahiye.

NULL values kaise dhundhe jaate hain, aur = NULL kyun nahi?

IS NULL use karo; = NULL NULL return karta hai (TRUE nahi) kyunki NULL ka matlab "unknown" hai.

LIMIT n OFFSET m kya karta hai?

m rows skip karta hai phir up to n rows return karta hai — yahi pagination ka basis hai.

Column aliases kis stage mein create hote hain?

SELECT stage mein (logical order ka step 5).

Connections

SQL SELECT basics — woh clause jisme ye sab attach hote hain
Aggregate functions (COUNT, SUM, AVG, MIN, MAX) — GROUP BY jo summarize karta hai
JOINs — FROM stage ka part, sabse pehla step
NULL handling and three-valued logic — kyun IS NULL exist karta hai
Subqueries and CTEs — workaround jab aliases/aggregates visible nahi hote
Indexes — kyun WHERE-before-GROUP-BY ordering performance ke liye matter karti hai
Pagination patterns — practice mein LIMIT/OFFSET