Tools mentioned in this article
Open the browser-based tool while you read and try the workflow immediately.
The order you write is not the order that runs
Most confusing SQL errors come from one fact: the clause order you type is not the order the database evaluates. You write SELECT first, but the engine gets to it late — which is exactly why this fails:
SELECT price * quantity AS total
FROM order_items
WHERE total > 1000; -- ERROR: unknown column 'total'and this works:
SELECT price * quantity AS total
FROM order_items
ORDER BY total DESC; -- fineNothing about the alias changed. What changed is when each clause runs relative to SELECT.
The two orders, side by side
| Written order | Logical execution order |
|---|---|
1. SELECT | 1. FROM / JOIN — assemble the rows |
2. FROM | 2. WHERE — filter individual rows |
3. JOIN | 3. GROUP BY — collapse rows into groups |
4. WHERE | 4. HAVING — filter the groups |
5. GROUP BY | 5. SELECT — compute the output columns (aliases are born here) |
6. HAVING | 6. DISTINCT — drop duplicate result rows |
7. ORDER BY | 7. ORDER BY — sort the result |
8. LIMIT | 8. LIMIT / OFFSET — cut the result |
Read the right column top to bottom and most SQL surprises stop being surprises. This is the logical order defined by the standard; the optimizer is free to reorder physical work as long as the answer matches.
Everything below follows from this one table.
Why aliases fail in WHERE but work in ORDER BY
SELECT is step 5. WHERE is step 2. When WHERE runs, the alias does not exist yet. ORDER BY is step 7, after SELECT, so by then it does.
The portable fix is to repeat the expression:
SELECT price * quantity AS total
FROM order_items
WHERE price * quantity > 1000;Or push it down into a subquery, which makes the alias a real column for the outer query:
SELECT * FROM (
SELECT price * quantity AS total FROM order_items
) t
WHERE t.total > 1000;Dialects vary in how much they bend this rule. MySQL permits aliases in GROUP BY and HAVING; PostgreSQL permits them in GROUP BY and ORDER BY but not in HAVING. No mainstream database allows an alias in WHERE. If you want one rule to remember, it’s that one.
WHERE vs HAVING
They are not interchangeable, and the reason is the ordering again: WHERE (step 2) filters rows before grouping; HAVING (step 4) filters groups after aggregation.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'paid' -- filter rows first: only paid orders
GROUP BY customer_id
HAVING COUNT(*) >= 3; -- then filter groups: 3+ such ordersSwapping them changes the meaning entirely. HAVING status = 'paid' would be asking a question about a group, not a row — and an aggregate condition in WHERE (WHERE COUNT(*) >= 3) is an error, because at step 2 nothing has been counted yet.
A practical rule: if the condition mentions an aggregate function, it belongs in HAVING; otherwise put it in WHERE. Keeping non-aggregate conditions in WHERE also lets the engine discard rows earlier, which is usually faster.
GROUP BY: the rule that differs by database
When you group, every selected column must either be in the GROUP BY list or be wrapped in an aggregate function. Otherwise the database cannot know which of the collapsed rows to show.
-- Broken: which name goes with the count?
SELECT customer_id, name, COUNT(*) FROM orders GROUP BY customer_id;PostgreSQL rejects this outright. MySQL also rejects it by default — since 5.7.5 the ONLY_FULL_GROUP_BY mode is enabled out of the box. Older MySQL configurations accepted it and returned an arbitrary row’s value, which is why legacy queries sometimes break on a server upgrade with the message “expression is not in GROUP BY clause and contains nonaggregated column”. The fix is to add the column to GROUP BY or aggregate it (MAX(name)).
Aggregates and NULL
Two behaviors are worth memorizing because they silently change numbers:
| Expression | Counts NULLs? |
|---|---|
COUNT(*) | Yes — counts rows |
COUNT(column) | No — skips rows where the column is NULL |
COUNT(DISTINCT column) | No — distinct non-NULL values only |
SUM / AVG / MAX / MIN | No — NULLs are ignored |
AVG(column) divides by the count of non-NULL values, not by the row count. If NULL should count as zero, say so explicitly with AVG(COALESCE(column, 0)).
This matters most after a LEFT JOIN: unmatched rows produce NULLs on the right side, so COUNT(*) counts a customer with no orders as 1, while COUNT(o.id) correctly counts 0.
SELECT c.id, COUNT(o.id) AS order_count -- not COUNT(*)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;LIMIT runs last — and needs a tiebreaker
LIMIT is step 8, applied after sorting. Two consequences:
It doesn’t make aggregation cheaper. LIMIT 10 on a GROUP BY query still groups everything first, then throws most of it away.
Without a unique sort key, paging is unstable. If created_at has ties, rows can appear on two pages or on none as you page through. Add a unique tiebreaker:
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;Syntax differs by dialect: PostgreSQL and MySQL both accept LIMIT n OFFSET m (MySQL also has the older LIMIT m, n — note the reversed arguments), while SQL Server uses OFFSET m ROWS FETCH NEXT n ROWS ONLY.
The JOIN + WHERE trap
One ordering consequence deserves its own warning. JOIN is step 1, WHERE is step 2 — so a WHERE condition on the right-hand table runs after the outer join has already inserted NULLs, and those NULL rows fail the condition:
-- Silently behaves like an INNER JOIN
SELECT * FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';
-- Keeps customers with no paid orders
SELECT * FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';Conditions that filter the join belong in ON; conditions that filter the final result belong in WHERE. The full breakdown of join types and row counts is in the SQL JOIN types reference.
Assembling queries without memorizing the order
Knowing the order is one thing; typing clauses in the right place every time is another. The Visual SQL Builder lets you pick tables, join conditions, filters, grouping, and sorting from a form and emits the clauses in the correct written order. Because you can see the generated SQL change as you add each piece, it doubles as a way to learn where a condition actually belongs.
To read an existing query, the SQL Formatter indents it so the clause boundaries become obvious, and SQL to ER Diagram shows how the tables you’re joining are related. If you’re still designing the tables, the CREATE TABLE reference covers types and constraints. Everything runs in your browser — queries and schema you paste are never sent anywhere.
Summary
- Logical order:
FROM→WHERE→GROUP BY→HAVING→SELECT→DISTINCT→ORDER BY→LIMIT - Aliases are created in
SELECT, soWHEREcan’t see them butORDER BYcan - Non-aggregate conditions go in
WHERE; aggregate conditions go inHAVING ONLY_FULL_GROUP_BYis the MySQL default since 5.7.5 — grouped queries must aggregate or group every selected columnCOUNT(*)counts rows;COUNT(column)skips NULLs. After aLEFT JOINyou almost always want the latterLIMITruns last and needs a unique tiebreaker inORDER BYfor stable paging- Filter the join in
ON, filter the result inWHERE
FAQ
Why does my alias work in ORDER BY but not in WHERE?
Because WHERE is evaluated before SELECT and ORDER BY after it. Aliases are created by SELECT, so they simply do not exist yet when WHERE runs. Repeat the full expression in WHERE, or wrap the query in a subquery so the alias becomes a real column.
What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping; HAVING filters groups after aggregation. If your condition uses an aggregate function such as COUNT() or SUM(), it has to be in HAVING. Everything else should stay in WHERE, where it can eliminate rows earlier.
Why do I get “expression is not in GROUP BY clause” on MySQL?
That is the ONLY_FULL_GROUP_BY SQL mode, enabled by default since MySQL 5.7.5. Every selected column must appear in GROUP BY or be wrapped in an aggregate. Queries written against older MySQL versions often break on upgrade for this reason — add the column to GROUP BY or aggregate it rather than turning the mode off.
Does adding LIMIT make an aggregate query faster?
Generally no. LIMIT is applied at the very end, after grouping and sorting, so the database still has to build the whole grouped result before discarding rows. To make such a query cheaper, reduce work earlier with WHERE conditions or an index that supports the grouping.
Is the SQL I paste in sent to a server?
No. The Visual SQL Builder and SQL Formatter run entirely in your browser — the queries and table names you enter are never transmitted anywhere.