Intuition The big pictureA SELECT query is not run top-to-bottom the way you write it. SQL is a declarative language: you describe what you want, and the engine decides how . But there is a fixed logical processing order that explains every "why doesn't this work?" bug you'll ever hit.
WHY this matters: 90% of SQL confusion ("why can't I use my alias in WHERE?", "why does HAVING exist if WHERE exists?") dissolves the moment you internalize the order. That's the 80/20 of SQL clauses.
Definition Logical query orderAlthough you write SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT, the database evaluates them in this order:
FROM (and JOINs) — get the raw rowsWHERE — filter individual rowsGROUP BY — collapse rows into groupsHAVING — filter the groupsSELECT — compute output columns / aliasesORDER BY — sort the resultLIMIT / OFFSET — keep only some rows
Intuition Why this order explainseverything
WHERE before GROUP BY → WHERE filters raw rows , so it can't see aggregates like COUNT(*) (groups don't exist yet).HAVING after GROUP BY → HAVING filters groups , so it can use aggregates.SELECT after HAVING but before ORDER BY → column aliases defined in SELECT are invisible to WHERE/HAVING but visible to ORDER BY .
Keeps only the rows for which a per-row boolean condition is TRUE. Runs before any grouping, so it operates on individual records.
Worked example Basic filteringSELECT name , salary FROM employees WHERE salary > 50000 AND dept = 'Sales' ; Why this step? Both conditions are per-row facts (this employee's salary, this employee's dept), so WHERE is the right place — no aggregation needed.
Worked example Why you can't aggregate in WHERE-- ❌ ERROR SELECT dept FROM employees WHERE COUNT ( * ) > 5 ; Why this fails? COUNT(*) is a property of a group , but at the WHERE stage groups don't exist yet (GROUP BY runs later ). The fix is HAVING.
Partitions the surviving rows into groups where all rows in a group share the same values for the listed columns. After grouping, each group becomes one output row , and you may only refer to (a) the grouping columns, or (b) aggregate functions of the other columns.
Intuition Why the "only grouped columns or aggregates" ruleA group of 50 employees in 'Sales' becomes ONE row. If you ask for name, SQL doesn't know which of the 50 names to show — it's ambiguous. But COUNT(*) or AVG(salary) collapse all 50 into a single, well-defined number. So the rule isn't arbitrary; it's the only way the math is well-defined.
Worked example Count per departmentSELECT dept, COUNT ( * ) AS headcount, AVG (salary) AS avg_sal FROM employees WHERE salary > 0 GROUP BY dept; Why this step? WHERE first drops garbage rows (salary 0), then GROUP BY partitions by dept, then COUNT/AVG summarize each partition. Output: one row per department.
Like WHERE, but it filters groups (post-aggregation) instead of rows. It can use aggregate functions because it runs after GROUP BY.
Intuition WHERE vs HAVING — the one-line ruleWHERE filters rows before grouping. HAVING filters groups after grouping. Use WHERE whenever possible (it's cheaper — fewer rows to group), and HAVING only for conditions on aggregates.
Worked example Departments with more than 5 peopleSELECT dept, COUNT ( * ) AS headcount FROM employees WHERE salary > 0 -- per-row filter (cheap, do first) GROUP BY dept HAVING COUNT ( * ) > 5 ; -- per-group filter (needs aggregate) Why this step? salary > 0 is a row fact → WHERE. COUNT(*) > 5 is a group fact → HAVING. Putting COUNT(*) > 5 in WHERE would error; putting salary > 0 in HAVING would still work but be slower and semantically wrong.
Sorts the final result set by one or more columns/expressions, ASC (default) or DESC. Runs after SELECT , so it can use column aliases.
Worked example Sort and use an aliasSELECT dept, COUNT ( * ) AS headcount FROM employees GROUP BY dept ORDER BY headcount DESC , dept ASC ; Why this step? headcount is an alias created in SELECT; ORDER BY runs after SELECT so the alias is visible. (Try WHERE headcount > 5 and it fails — WHERE runs before SELECT.)
Definition LIMIT / OFFSETReturns at most n n n LIMIT n OFFSET m skips m m m n n n pagination .
Worked example Top 3 departmentsSELECT dept, COUNT ( * ) AS headcount FROM employees GROUP BY dept ORDER BY headcount DESC LIMIT 3 ; Why this step? "Top 3" only makes sense after sorting, so ORDER BY must come first, then LIMIT trims. Without ORDER BY, "LIMIT 3" returns 3 arbitrary rows — a classic silent bug.
Common mistake Steel-manned common errors1. "I'll use my alias in WHERE."
Why it feels right: You typed SELECT salary*12 AS annual at the top, so it looks available everywhere. Fix: WHERE runs before SELECT — the alias doesn't exist yet. Repeat the expression: WHERE salary*12 > 60000, or use HAVING/subquery.
2. "WHERE and HAVING are interchangeable."
Why it feels right: Both filter and use boolean conditions. Fix: WHERE = rows (pre-group, no aggregates), HAVING = groups (post-group, aggregates allowed). Different stages.
3. "LIMIT 10 gives me the top 10."
Why it feels right: Results often appear sorted by accident (insertion/index order). Fix: No ORDER BY = no guaranteed order. Always pair LIMIT with ORDER BY for "top N".
4. "WHERE x = NULL finds NULLs."
Why it feels right: = works for everything else. Fix: NULL means "unknown"; NULL = NULL is NULL (not TRUE). Use WHERE x IS NULL.
5. Putting non-aggregated columns in SELECT with GROUP BY.
Why it feels right: You want extra info per group. Fix: It's ambiguous which row's value to show. Either add the column to GROUP BY or wrap it in an aggregate (MAX, MIN, etc.).
Recall Feynman: explain to a 12-year-oldImagine a big box of trading cards.
WHERE = "throw away cards I don't want" (one card at a time).GROUP BY = "make piles, one pile per team."HAVING = "throw away whole piles that have fewer than 5 cards."ORDER BY = "line up the remaining piles biggest-first."LIMIT = "just hand me the top 3 piles."
You can't throw away a pile before the piles exist — that's why HAVING comes after the piling, not before. Simple!
Mnemonic Remember the order"F ind W rong G roups, H unt S orted O utput L ater "**
→ FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
(Find–Wrong–Groups–Hunt–Sorted–Output–Later)
What is SQL's logical processing order of clauses? FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
Why can't WHERE use aggregate functions like COUNT(*)? WHERE runs before GROUP BY, so groups (and their aggregates) don't exist yet.
What is the one-line difference between WHERE and HAVING? WHERE filters individual rows before grouping; HAVING filters whole groups after grouping (can use aggregates).
Can ORDER BY use a SELECT alias? Why? Yes — ORDER BY runs after SELECT, so aliases are already defined.
Can WHERE use a SELECT alias? Why? No — WHERE runs before SELECT, so the alias doesn't exist yet.
Why must LIMIT be paired with ORDER BY for "top N"? Without ORDER BY the result order is unspecified, so LIMIT returns arbitrary rows, not the largest ones.
With GROUP BY, which columns may appear bare in SELECT? Only the grouping columns; all others must be inside an aggregate function.
How do you find NULL values, and why not = NULL? Use IS NULL; = NULL yields NULL (not TRUE) because NULL means "unknown".
What does LIMIT n OFFSET m do? Skips m rows then returns up to n rows — the basis of pagination.
In which stage are column aliases created? In the SELECT stage (step 5 of the logical order).
SQL SELECT basics — the clause these all attach toAggregate functions (COUNT, SUM, AVG, MIN, MAX) — what GROUP BY summarizesJOINs — part of the FROM stage, earliest stepNULL handling and three-valued logic — why IS NULL existsSubqueries and CTEs — workaround when aliases/aggregates aren't visible yetIndexes — why WHERE-before-GROUP-BY ordering matters for performancePagination patterns — LIMIT/OFFSET in practice
SELECT computes columns and aliases
Intuition Hinglish mein samjhoDekho, SQL query likhne ka order aur uske chalne (execute hone) ka order alag hota hai. Hum likhte hain SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT, lekin database andar-andar isko is order me process karta hai: pehle FROM (rows lao), phir WHERE (ek-ek row filter karo), phir GROUP BY (rows ko piles/groups me baant do), phir HAVING (poore groups ko filter karo), phir SELECT (columns aur alias banao), phir ORDER BY (sort karo), aur last me LIMIT (sirf top N rows rakho). Yeh order yaad kar lo to SQL ke 80% confusion khatam.
Sabse important baat: WHERE rows pe kaam karta hai, HAVING groups pe. Isliye COUNT(*) jaisa aggregate WHERE me nahi chalega — kyunki us time tak groups bane hi nahi hote. Aggregate ka condition lagana ho to HAVING use karo. Aur ek aur trap: alias (jaise AS headcount) WHERE me kaam nahi karega kyunki SELECT abhi tak run hua hi nahi, par ORDER BY me chalega kyunki woh SELECT ke baad aata hai.
LIMIT ke saath hamesha ORDER BY lagana, warna "top 3" maangne par database koi bhi random 3 rows de dega — silent bug ban jaata hai. Aur NULL dhoondhna ho to IS NULL likho, = NULL kabhi mat likhna, kyunki NULL ka matlab "pata nahi" hota aur NULL = NULL bhi TRUE nahi hota.
Bas yeh mental picture rakho — cards ka box: pehle bekar cards phenko (WHERE), phir team-wise piles banao (GROUP BY), chhoti piles hatao (HAVING), badi-se-chhoti lagao (ORDER BY), top 3 utha lo (LIMIT). Itna clear ho gaya to exam aur interview dono nikal jaayenge.
