A colleague spent twenty minutes on a query once. It was returning the top ten orders correctly. Then she added a WHERE clause and the wrong orders started coming back.
She assumed the WHERE was broken. It wasn't. She thought LIMIT 10 ran first, and the sort happened on those ten rows. The database doesn't work that way. It sorts everything, then trims. She drew it on a whiteboard and never made the same mistake again.
I've taught WHERE and ORDER BY many times. Every group has someone who gets stuck on the same three things: NULL sorting, aggregate in WHERE, missing parentheses. They're not hard ideas. They're just the three places where people's gut feeling is wrong.
Here's where the gut feeling is wrong.
The database runs your query in a different order than you wrote it
You write top to bottom. The database runs it in a completely different order.
FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT.
That's the whole pipeline. Once you can picture it, half of SQL stops being confusing.
WHERE runs second. It sees one row at a time, before anything has been grouped. So when you try to write WHERE COUNT(*) > 5, the database has no idea what you mean. The count doesn't exist yet. That error message you get? It's not the database being difficult. It's saying "you asked for this too early."
ORDER BY runs sixth. By that point the database has the full result in front of it, aliases and all. So ORDER BY line_total works even when line_total is only defined in the SELECT list. It can see it. WHERE can't.
I used to memorise this order for exams and forget it a week later. What made it stick was debugging. Once you've stared at a slow query and traced it through the pipeline a few times, it stops being something you memorise and starts being something you can see.
WHERE, and the one mistake everyone makes
WHERE keeps the rows you asked for and throws away the rest.
It's also the biggest speed win you have.
If WHERE cuts the rows by 20x, everything after it has 20x less work.
If a report is slow, WHERE is the first place to look.
Not the last.
Some patterns you'll use all the time:
WHERE credit_limit > 50000
for a straight comparison.
WHERE region = 'North' AND credit_limit > 60000 when you need two conditions to be true at once.
WHERE customer_name LIKE 'A%' for pattern matching.
The % is any sequence of characters, _ is exactly one.
WHERE region IN ('North', 'South') when you have a short list and don't want to chain ORs.
And WHERE order_amount BETWEEN 5000 AND 20000 when both ends should be included.
Then there's the one that gets everyone: WHERE credit_limit IS NULL.
If you write WHERE credit_limit = NULL, you get zero rows. No error.
No warning. Just an empty result that looks like a bug.
NULL isn't equal to anything. Not even another NULL. So the comparison fails without telling you.
Always use IS NULL or IS NOT NULL.
I've made this mistake more times than I'd like to admit. I've also watched people with fifteen years of experience make it. There's no shame in it. There's just a fix.
One more thing on WHERE.
SQL checks AND before OR. So A OR B AND C is read as A OR (B AND C). Not (A OR B) AND C.
When your intent is mixed, add parentheses. I've watched this one break more production reports than any other single mistake in SQL.
ORDER BY — putting the result in the order you want
ORDER BY sorts the final result. It runs after SELECT, so it can see everything SELECT can see.
Default is ascending. ORDER BY credit_limit puts the smallest first. DESC reverses it.
Multiple sort keys work left to right. ORDER BY region ASC, credit_limit DESC sorts by region first, then by credit limit inside each region. That's the pattern behind almost every report. One column to organise by, one to rank within.
You can sort by a column you didn't select. The output can be narrower than the sort. That works because ORDER BY runs after SELECT, which still has the original columns. Clean report, correct order, no compromise.
You can sort by an alias too. ORDER BY adjusted_limit DESC works when adjusted_limit is defined in the SELECT list. You can't do the same in WHERE, because the alias doesn't exist yet. That's how you know WHERE runs before ORDER BY. Comes up in interviews a lot.
If the column you're sorting by has an index, the database reads in that order and skips the sort. On a small table you won't notice. On a table with tens of millions of rows, it's the difference between a second and a minute. When a report gets slow, ORDER BY is the first thing I check.
The NULL sorting problem
NULL isn't zero. It isn't an empty string. It's the absence of a value, so it can't be compared like a number or a word.
Which means different databases sort it differently.
Oracle and PostgreSQL put NULLs last in ascending order, first in descending.
MySQL and SQL Server do the opposite. NULLs come first in ascending, last in descending.
This is exactly the kind of bug that works on your machine and breaks on the server. It's a portability problem, not a logic problem, and it only shows up when you move code from development to production.
Oracle and PostgreSQL let you be explicit. ORDER BY credit_limit ASC NULLS LAST puts the customer with no credit limit at the bottom.
MySQL and SQL Server don't support NULLS FIRST or NULLS LAST. You sort a computed column instead, like ORDER BY ISNULL(credit_limit, 999999) or ORDER BY credit_limit IS NULL, credit_limit. Different mechanics, same result.
If a report depends on where NULLs appear, be explicit. The default is never safe across databases.
WHERE vs HAVING, and why this is the question that catches people
Both clauses filter. They're completely different in what they do.
WHERE filters rows before any grouping. One row at a time. Aggregates don't exist yet.
HAVING filters groups after GROUP BY has produced them. It sees the grouped result. So it can hold SUM, AVG, COUNT.
Try to swap them and the database stops you with an error about aggregate functions in WHERE. That error isn't the database being difficult. It's saying "you asked for this too early." Same error message as before. Same reason.
You'll often want both. Filter to delivered orders only with WHERE. Group by customer. Keep only the customers whose delivered average is above 5,000 with HAVING.
The reason to do the WHERE first is not just correctness. It's speed. Filter early means fewer rows to group. On a large table that small win adds up.
Six mistakes I keep seeing
Two of these come up in every training session I run. The other four are the ones I used to make myself.
Putting an aggregate in WHERE. WHERE COUNT(*) > 5 throws an error because the count doesn't exist when WHERE runs. Move it to HAVING.
Assuming NULL sorts the same way everywhere. It doesn't. Oracle and PostgreSQL put NULLs last in ascending. MySQL and SQL Server put them first. If the query might run on more than one database, be explicit about NULL placement.
The other four, quickly.
BETWEEN on a timestamp column doesn't cover the whole end date.
It stops at midnight. Use >= '2024-01-01' AND < '2024-02-01',
or cast the timestamp to a date first.
WHERE credit_limit = NULL returns zero rows silently. No error. Use IS NULL.
AND before OR. SQL checks AND first, so add parentheses when the intent is mixed.
ORDER BY runs before LIMIT. Not after. The pipeline sorts first, then trims.
What this article doesn't cover
Those six are the ones that break production. They aren't the full picture.
The guide on my site covers twenty-four interview questions on these two clauses. How NULL sorts in every major database. How to sort by a column you didn't select. How to make ORDER BY faster on a huge table.
How WHERE and HAVING work together when you need both. The follow-up questions interviewers actually ask after the first answer.
There are sample tables you can run against, worked examples for every clause, and a quiz at the bottom.
That full walkthrough is here:
Restricting and Sorting Data in SQL: WHERE and ORDER BY Explained(https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/restricting-sorting-data-sql-faq.html)
Six here, twenty-four there. That's what the page has that I can't fit into a Dev.to post.
Top comments (0)