beginner~4h

SELECT Queries — WHERE, GROUP BY, HAVING, ORDER BY

The SELECT statement is the most used SQL statement. Understanding its execution order and clause interactions is critical for writing correct and efficient queries.

Learning objectives

Recite the logical execution order of a SELECT statement, and explain why it isn't the same as the order you type the clauses in.
Explain, precisely, why WHERE can't filter on an aggregate but HAVING can.
Predict what COUNT(*) vs COUNT(column) will return differently on a table with NULLs.

📖 Story

Imagine you're a chef working from a walk-in fridge stocked with every ingredient in the building. A SELECT query is you giving three instructions to a kitchen assistant, in this order: first, go into the fridge and pull out only the ingredients that match what I need — that's WHERE. Second, sort what you pulled into bins by type — vegetables in one bin, proteins in another — that's GROUP BY. Third, arrange the bins on the counter in the order I want to see them — that's ORDER BY. There's a fourth instruction, easy to miss: sometimes you only want bins that meet some condition, like "only show me bins with more than five items" — that's HAVING, and it applies to the bins, not the individual ingredients.

Why this order matters more than the syntax

Here's the part that trips almost everyone up at least once: you type a SELECT statement as SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY, but the database doesn't execute it in that order at all. It executes FROM and WHERE first — go get the ingredients, filter them — then GROUP BY, then HAVING, then finally SELECT picks which columns to show, and ORDER BY runs last of all. This is exactly why a column alias you define in SELECT can't be used in your own WHERE clause — at the point WHERE runs, that alias doesn't exist yet, because SELECT hasn't happened.

WHERE vs HAVING, precisely

	WHERE	HAVING
Filters	Individual rows	Groups (after GROUP BY)
Runs	Before grouping	After grouping
Can use aggregates like `COUNT()`, `SUM()`?	No	Yes
Typical use	"only rows where status = 'active'"	"only departments with more than 10 employees"

A useful mental shortcut: if the condition is about a single row's own values, it's WHERE; if the condition is about something you only know after grouping — a count, a sum, an average — it has to be HAVING, because that number simply doesn't exist yet at the point WHERE runs.

PostgreSQL's real execution order

FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT / OFFSET. Each stage hands its output to the next one as input — WHERE never sees the full table, it sees whatever FROM produced; HAVING never sees individual rows, it only sees the groups that GROUP BY already built.

Key aggregate functions, and where people get surprised

COUNT(*) counts rows, full stop — it doesn't care whether any column is NULL.
COUNT(column) counts only the rows where that specific column is not NULL — on a table where a third of rows have a NULL phone number, COUNT(*) and COUNT(phone) will legitimately disagree, and that's correct behavior, not a bug.
SUM, AVG, MIN, MAX all silently ignore NULL values rather than erroring on them, which is convenient until you forget it's happening.

PostgreSQL's own SELECT execution, step by step

Scan the table (or index) named in FROM.
Apply WHERE to discard rows that don't match.
If a GROUP BY is present, bucket the surviving rows into groups.
Apply HAVING to discard whole groups that don't match.
Evaluate the SELECT list — including any aggregate functions — per remaining group (or per row, if there's no GROUP BY).
Apply DISTINCT if present.
Sort by ORDER BY.
Trim to LIMIT / OFFSET.

Start with FROM — decide which table (or joined tables) you're pulling from.
Add WHERE — narrow it down to the rows you actually care about, before doing any heavier work.
Add GROUP BY only if you need a summary per category, not per row — if you want "total sales per region," you need GROUP BY region; if you want every individual sale, you don't.
Add HAVING only if your filter condition depends on the aggregate itself — "regions with total sales over ₹10 lakh" needs HAVING SUM(sales) > 1000000, because you can't know that number until the grouping is done.
Write the SELECT list last, mentally — decide which columns and computed values you actually want to see, now that you know what rows/groups survived.
Add ORDER BY to control presentation — this never changes which rows come back, only the order they're shown in.
Add LIMIT if you only need the top N — always pair it with ORDER BY, otherwise "top 10" has no defined meaning.

Always pair LIMIT with an explicit ORDER BY — without one, "the first 10 rows" is whatever order the database engine happens to return them in that day, which can silently change after a reindex or a version upgrade.
Prefer COUNT(column) over COUNT(*) the moment you actually care about "how many rows have a value here," not just "how many rows exist" — using the wrong one is a quiet source of off-by-some-N bugs.
Filter with WHERE as early and as tightly as possible — the fewer rows that survive into GROUP BY, the less work every later stage has to do.
Alias computed expressions in SELECT clearly (AS total_orders) — future readers of the query (including you, in six months) shouldn't have to mentally re-derive what an unlabeled column means.
When a query returns a value that seems to be silently dropping rows, check NULL handling before anything else — it's the single most common root cause.

⚠️ Why this keeps happening

Most SQL tutorials teach WHERE, GROUP BY, and HAVING as three unrelated keywords to memorize, rather than as one execution pipeline — so it's genuinely reasonable that people reach for the wrong one under pressure. The good news is there's exactly one rule to remember, and it resolves almost every case below.

Trying to filter on an aggregate using WHERE. WHERE COUNT(*) > 5 fails, because at the point WHERE runs, no grouping — and therefore no count — exists yet. It has to be HAVING COUNT(*) > 5.
Using a SELECT-defined alias inside WHERE. SELECT price * qty AS total ... WHERE total > 100 fails in most databases, for the same underlying reason: WHERE runs before SELECT, so total doesn't exist yet from its perspective.
Confusing COUNT(*) and COUNT(column), and being surprised by the difference. If a column has NULLs, these two return different numbers — neither is wrong, they're answering different questions.
Ordering by a column that isn't in SELECT and assuming it changes the returned rows. ORDER BY only changes presentation order — it can never add, remove, or change which rows come back.
Assuming NULLs sort last (or first) across every database. Sort position of NULL in ORDER BY is engine-specific — PostgreSQL and MySQL don't agree by default, so relying on it without an explicit NULLS FIRST/NULLS LAST is a portability trap.

Filter with WHERE on indexed columns wherever possible — an unindexed WHERE forces a full sequential scan of the table, which is fine at ten thousand rows and painful at ten million.
Avoid SELECT * in production code — pulling columns you don't need wastes I/O and network bandwidth, and it silently breaks the moment someone renames a column you weren't actually using.
Push WHERE filters before GROUP BY whenever the logic allows it (which the query planner usually does automatically, but it's worth verifying with EXPLAIN) — filtering ten rows down to three is far cheaper than grouping ten rows and then discarding most of the groups.
For "top N per category" queries, a window function (covered in a later chapter) is usually faster than GROUP BY plus a self-join, because it avoids scanning the table twice.
Watch for HAVING clauses that could have been written as WHERE — every row that could have been eliminated before grouping, but wasn't, is wasted grouping work.

Run EXPLAIN ANALYZE on any query that touches a table over roughly a million rows before shipping it, not after it's already slow in production.
Set a sane statement timeout at the connection or role level, so one runaway analytical query can't quietly hold resources indefinitely.
For dashboards and reports, consider a materialized view or a pre-aggregated summary table instead of re-running an expensive GROUP BY on every page load.
Watch out for ORDER BY on an unindexed column combined with a small LIMIT — the planner may still have to sort a huge intermediate result before it can take the first few rows.
Log slow queries (log_min_duration_statement in PostgreSQL) so a creeping performance regression shows up before a customer complains about it.

Write a query that intentionally tries to use WHERE COUNT(*) > 5, read the exact error message your database gives you, then rewrite it correctly with HAVING.
On a table with some NULL values in one column, run COUNT(*) and COUNT(that_column) side by side and confirm, with your own eyes, that they differ.
Write a report query: "total revenue per product category, but only categories with more than 3 distinct products" — this forces you to combine GROUP BY, HAVING, and a COUNT(DISTINCT ...) correctly.
Take a query using SELECT * and rewrite it to name every column explicitly — notice how much clearer it becomes what the query actually depends on.

✓ Quick recap

Real execution order: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET — not the order you type the clauses in.
WHERE filters rows, before grouping, and cannot see aggregates. HAVING filters groups, after grouping, and can.
COUNT(*) counts rows; COUNT(column) counts non-null values in that column — they can legitimately disagree.
ORDER BY only changes presentation order; it never changes which rows are returned.
Always pair LIMIT with an explicit ORDER BY, or "the first N rows" has no guaranteed meaning.

Want a visual for this concept?

Generate a diagram tailored to “SELECT Queries — WHERE, GROUP BY, HAVING, ORDER BY” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to JOINs — INNER, LEFT, RIGHT, FULL, SELF, CROSS →← Back to all SQL chapters