Core CS · DBMS

Why WHERE cannot see your alias

Q: In what order does a SELECT statement actually run?

FROM , then WHERE , then GROUP BY , then HAVING , then SELECT , then DISTINCT , then ORDER BY , then LIMIT or OFFSET . Give the list and then give the reason it is not the written order: the database needs rows before it can filter them, and it needs the filtered rows before it can work out what a column you invented is worth. Two consequences are what actually get asked about, so lead with them: a SELECT alias is invisible to WHERE and visible to ORDER BY , and WHERE filters rows while HAVING filters groups.

Q: Why can WHERE not use a column alias when ORDER BY can?

Because the alias is created by SELECT , which runs fifth. WHERE runs second, so at that moment the name does not exist, and the database reports an unknown column rather than a syntax error. ORDER BY runs seventh, after SELECT has produced the column, so the same word resolves there without complaint. In WHERE you either repeat the expression the alias stood for, or wrap the whole query in an outer SELECT so the alias becomes a real column of the inner result.

Q: What is the difference between WHERE and HAVING?

WHERE filters individual rows before any grouping happens; HAVING filters the groups that GROUP BY produced. That order is exactly why WHERE cannot contain an aggregate such as COUNT(*) : at step two there are no groups yet, so there is nothing to count. Practical rule: if a condition can be decided one row at a time, put it in WHERE , because it removes those rows before the grouping work is done.

Q: Somebody writes WHERE marks = NULL and gets nothing back. What is wrong?

NULL means the value is not recorded, so comparing anything to it produces unknown rather than true or false, and WHERE keeps only the rows that come back true. That applies to NULL = NULL as well, so a row does not even match itself. IS NULL and IS NOT NULL are the only tests that work. On our table marks = NULL returns no rows and marks IS NULL returns Divya.

Q: COUNT(*) returns 6 and COUNT(marks) returns 5 on the same table. Why?

COUNT(*) counts rows; COUNT(marks) counts the non-null values in that column. Divya is one row and zero values, so the two disagree by exactly one. Every aggregate behaves the same way: AVG(marks) divides by 5 and not by 6, which means a missing mark is silently ignored rather than counted as a zero. If you want it counted as zero you have to say so, with something like COALESCE(marks, 0) .

Q: What exactly does DISTINCT compare?

The whole select list, not the first column. SELECT DISTINCT city, marks removes a row only when both values match another row, which is why adding one column to the list usually brings the duplicates straight back. For this one purpose all nulls are treated as equal and fold into a single row, even though NULL = NULL is unknown everywhere else in SQL. And DISTINCT applies to the row, so SELECT DISTINCT(city), marks does not mean distinct cities; those brackets are decoration.

Q: Is BETWEEN inclusive?

Yes, at both ends. marks BETWEEN 60 AND 80 is exactly marks >= 60 AND marks <= 80 , so a 60 and an 80 both qualify. Two traps come with it. The low bound must be written first, because BETWEEN 80 AND 60 expands to an impossible pair and returns nothing rather than an error. And a null on either side makes the whole expression unknown, so a row with no marks is never between anything.

Q: Does AND or OR bind tighter?

NOT first, then AND , then OR , the same way multiplication is applied before addition. So a OR b AND c means a OR (b AND c) , which is rarely what somebody typing left to right had in mind, and no error is raised either way. Give the rule and then say what you do about it: put the brackets in even when the precedence already agrees with you, because the next person to read the query should not need to know the rule in order to trust it.

Q: Is LIMIT 10 without an ORDER BY safe?

No. Without ORDER BY the row order is not defined, so LIMIT 10 hands you ten arbitrary rows, and the same query can return a different ten after an update or a change of plan. LIMIT runs last, on whatever arrangement the previous step happened to leave behind, so “the top ten” only means something once you have said what top means. Ties matter too: if the sort key repeats, the rows inside a tie are still in no defined order, so add a unique tiebreaker such as the primary key.

You write a query SELECT first. The database runs it FROM first. One six-row table and one query, taken apart clause by clause in the order it really happens.

Run one query clause by clause, six rows at a time →

Nothing in the text of a query tells you which clause runs first. The order is fixed by the standard, it is not the order you typed, and one wrong assumption about it produces an error that reads like a rule you broke when it is really a name that did not exist yet.

01 The idea

Written in one order, run in another

A SELECT statement is written in an order that reads well in English: take these columns, from this table, where this is true. The database does not run it that way. It runs FROM first, because it has nothing to filter until it has rows in hand, and it runs SELECT fifth, long after WHERE has finished its work. The order you type is a reading order. The order it runs in is a fixed list of eight steps, and every database on the planet agrees on that list.

Each clause is a small machine with rows going in and rows coming out. FROM hands over every row of the table. WHERE tests one condition against each of those rows and passes on the ones that come back true. SELECT throws away the columns you did not ask for and creates any new ones you named with AS. DISTINCT folds identical rows together, ORDER BY sorts whatever is left, and LIMIT keeps the first few rows of that sorted list and throws the rest away. Nothing skips ahead and nothing reaches back.

That single-file arrangement has one consequence you will meet almost immediately. SELECT marks AS score creates the name score at step five. WHERE ran at step two, when that name did not exist, so WHERE score >= 60 is not a rule you have broken. It is a column the database has never heard of, and it will say so in exactly those words. ORDER BY runs at step seven, after score exists, so ORDER BY score is accepted without complaint. Same alias, same query, two different answers, and the whole explanation is a position on a list of eight.

A clause may only use names that already existed when it ran. FROM creates the table’s columns and its alias; SELECT creates column aliases. That is the entire reason WHERE cannot see a SELECT alias and ORDER BY can.

ClauseOne keyword-led piece of a query: FROM, WHERE, ORDER BY. Each one takes a set of rows in and hands a set of rows out to the next one.

PredicateThe condition a clause tests against a row. It comes back true, false or unknown. WHERE keeps only the rows that come back true, which means unknown is thrown away with false.

AliasA second name for a column or a table, written with AS. It is created by the clause that declares it, so it exists only for the clauses that run after that one.

02 Worked example

One table, one query, six clauses

Six students. Four columns. Divya sat the exam late, so her marks are not recorded yet and that cell holds NULL, which is not zero and not an empty string but the absence of a value. This table is the only data in the lesson: the flow below, the console, the cheat sheet and every practice problem run against these same six rows.

sid	name	city	marks
S1	Asha	Pune	78
S2	Ravi	Delhi	55
S3	Meera	Pune	91
S4	Karan	Delhi	78
S5	Divya	Surat	`NULL`
S6	Arjun	Kochi	64

Here is the question we are asking of it: the first two cities, in alphabetical order, that have at least one student scoring 60 or more. Written out, that is five clauses and one keyword.

SELECT DISTINCT cityFROM     studentWHERE    marks >= 60ORDER BY cityLIMIT    2;

Read it top to bottom and you would guess the database picks the city column first and then filters. It does the opposite. Follow the row set instead, because the row set is what actually moves.

1 · FROMloads every row of student
6 rows, 4 columns

→

2 · WHEREtests marks >= 60 per row; Ravi fails it and Divya is unknown
6 → 4 rows

→

3 · SELECTkeeps city, drops the other three, and this is where any AS name is born
4 cols → 1

→

4 · DISTINCTPune arrived twice, from Asha and from Meera; one copy goes
4 → 3 rows

→

5 · ORDER BY, LIMITsorts to Delhi, Kochi, Pune, then keeps the front two
3 → 2 rows

The answer is Delhi, Kochi. Six rows went in and two came out, and each of the five nodes is responsible for a named part of the loss: WHERE removed two rows, SELECT removed three columns, DISTINCT removed one duplicate, LIMIT removed one more row. Nothing was removed twice and nothing was removed by accident.

Node 3 is highlighted because it is the one people place wrongly. SELECT is the first word you type and the third thing that happens. Everything before it works on all four columns whether you asked for them or not, which is why WHERE marks >= 60 is legal even though marks is nowhere in the select list. Everything after it works on what SELECT left behind.

Hold on to that asymmetry. It is the difference between the two halves of the next section: what a clause can filter on, and what a clause can name.

03 Mechanics

The eight slots, and where each name is born

This is the list. Two of the eight slots are empty in our query because it has no grouping, and they are shown anyway because you need to know where they sit before you meet GROUP BY. The last column is the one that answers interview questions: it says which names come into existence at that step, and therefore which clauses are allowed to use them.

#	Clause	What it does	Our query, rows in and out	Names it creates
1	FROM	Names the source and produces every row in it.	`— → 6 rows, 4 cols`	Every column of student, plus the table alias if you wrote AS s.
2	WHERE	Tests one predicate against each row and keeps the rows that come back true.	`6 → 4 rows`	Nothing.
3	GROUP BY	Folds rows into one row per group.	`4 → 4 (not used here)`	Aggregates over each group. From this step on, the only things you may name are the grouping columns and aggregates; a bare column that is neither is not valid standard SQL.
4	HAVING	The same kind of test as WHERE, but on groups, and it may use aggregates.	`4 → 4 (not used here)`	Nothing.
5	SELECT	Evaluates the expressions you listed and drops every column you did not.	`4 cols → 1 col`	Column aliases written with AS.
6	DISTINCT	Removes rows that are identical across the whole select list.	`4 → 3 rows`	Nothing.
7	ORDER BY	Sorts the rows that are left. ASC is the default. It may still name a column SELECT dropped, as long as each surviving row stands for exactly one row of the input.	`3 → 3 rows`	Nothing.
8	LIMIT / OFFSET	Skips OFFSET rows from the front, then keeps LIMIT rows.	`3 → 2 rows`	Nothing.

What can go inside WHERE

Step 2 is where most of a query’s work happens, so it is worth knowing everything you are allowed to put there. Each line below is a complete WHERE clause against the six rows from section 02, with the surviving sids worked out by hand.

WHERE marks >= 60                     -- comparison        -> S1 S3 S4 S6WHERE marks BETWEEN 60 AND 80         -- both ends included -> S1 S4 S6WHERE city IN ('Pune', 'Kochi')       -- a list of values  -> S1 S3 S6WHERE name LIKE 'A%'                  -- % = any run       -> S1 S6WHERE name LIKE '_a%'                 -- _ = exactly one   -> S2 S4WHERE marks IS NULL                   -- the only test     -> S5WHERE marks = NULL                     -- never true        -> no rowsWHERE NOT marks >= 60                 -- unknown stays out -> S2

Lines 6, 7 and 8 are one idea in three costumes. NULL is not a value, so no comparison involving it produces true or false; it produces unknown. Line 7 asks whether an unrecorded mark equals nothing in particular, gets unknown for all six rows, and returns none of them. Line 8 is stranger: NOT applied to unknown is still unknown, so Divya does not appear in marks >= 60 and does not appear in its negation either. A row can be missing from both halves of a yes-or-no question, and that surprises people once and then never again.

Line 5 needs one more word. % matches any run of characters including an empty one, and _ matches exactly one. So '_a%' reads “any first character, then an a, then anything”, which is why it finds Ravi and Karan and not Asha. Whether LIKE cares about upper and lower case is not part of the operator at all; it comes from the collation of the column, and it differs between products.

AND before OR, always

When a WHERE clause holds more than one condition, NOT is applied first, then AND, then OR, exactly as multiplication is applied before addition. The query below is the one that catches people, because the reading a human gives it left to right is not the reading the database gives it.

WHERE city = 'Pune' OR city = 'Kochi' AND marks >= 80-- AND binds tighter, so the database reads it as:WHERE city = 'Pune' OR (city = 'Kochi' AND marks >= 80)   -- S1 S3-- if you meant the other one, the brackets are not optional:WHERE (city = 'Pune' OR city = 'Kochi') AND marks >= 80   -- S3

Line 3 returns Asha and Meera, because every Pune row satisfies the left half on its own and nothing else clears 80 in Kochi. Line 5 returns Meera alone, because now the mark test applies to the Pune rows too and Asha’s 78 fails it. One pair of brackets, two different answers, and no error either way.

The alias, and the two clauses that disagree about it

Now the payoff. The query below is legal English and illegal SQL, and the line numbers in the comments are the step numbers from the table above.

SELECT   name, marks AS score      -- step 5: the name "score" is created hereFROM     student                   -- step 1: runs first, whatever you typedWHERE    score >= 60               -- step 2: ERROR, no column named scoreORDER BY score DESC, name;         -- step 7: fine, score exists by now -- the fix is one word: name the real column, not the aliasSELECT   s.name, s.marks AS scoreFROM     student AS s              -- FROM names the source and creates the alias sWHERE    s.marks >= 60             -- Meera 91, Asha 78, Karan 78, Arjun 64ORDER BY score DESC, name;

The second query returns Meera 91, then Asha 78, then Karan 78, then Arjun 64. The second sort key earns its place: Asha and Karan both scored 78, and a single-key sort promises nothing at all about which of two tied rows comes first. Add a tiebreaker whenever you would notice the difference.

Two aliases appear across that pair of queries, the table alias s and the column alias score, and only score is refused in WHERE. That is not a special rule about the word AS. It follows from the eight-step list and nothing else, and working out why is worth more than memorising the exception.

One qualification, because you will meet it the first time you sort by something you did not display. ORDER BY is allowed to name a column SELECT dropped: SELECT name FROM student ORDER BY marks DESC is accepted everywhere, even though marks is not in the output. That is not a contradiction of the rule above, because FROM created marks at step 1 and every row of the result still stands for exactly one row of the table, so the value to sort by is never in doubt. The moment a step folds several input rows into one output row, that one-to-one link is gone and so is any column the fold discarded, which is a good thing to have thought about before you reach the last practice problem.

A name is usable by every clause that runs after the clause that created it, and by no clause that runs before. Everything in this lesson that looks like an arbitrary restriction is that one sentence applied to a different pair of steps.

05 Cheat sheet

Every clause on one card, checked against the same six rows

The right-hand column is what makes this worth glancing at before an interview: every claim here was worked out by hand against Asha, Ravi, Meera, Karan, Divya and Arjun, so you can check any row of it in thirty seconds without a database.

Feature	Written as	On our six rows	The trap
Comparison	`marks >= 60`	S1 S3 S4 S6	`a NULL satisfies no comparison, so Divya is in neither >= 60 nor < 60`
BETWEEN	`marks BETWEEN 60 AND 80`	S1 S4 S6	`inclusive at both ends; BETWEEN 80 AND 60 returns nothing`
IN	`city IN ('Pune','Kochi')`	S1 S3 S6	`a list of exact values, not a pattern`
NOT IN with a NULL	`city NOT IN ('Pune', NULL)`	no rows at all	`it expands to NOT (city='Pune' OR city=NULL), and that is never true`
LIKE, percent sign	`name LIKE 'A%'`	S1 S6	`% matches a run of any length, including zero characters`
LIKE, underscore	`name LIKE '_a%'`	S2 S4	`_ matches exactly one character, never zero`
IS NULL	`marks IS NULL`	S5	`marks = NULL returns nothing; IS NULL is the only test that works`
AND, OR, NOT	`city='Pune' OR city='Kochi' AND marks>=80`	S1 S3	`NOT, then AND, then OR; this reads as Pune OR (Kochi AND 80)`
DISTINCT	`SELECT DISTINCT city`	Pune, Delhi, Surat, Kochi	`compares the whole select list, and folds every NULL into one row`
ORDER BY	`ORDER BY city`	Delhi, Kochi, Pune, Surat (on the distinct list above)	`ASC is the default; where the NULLs land is vendor-specific`
LIMIT and OFFSET	`ORDER BY city LIMIT 2 OFFSET 1`	Kochi, Pune (same distinct list, skip one)	`OFFSET skips first, then LIMIT counts; meaningless without ORDER BY`
Column alias	`marks AS score`	created at step 5	`invisible to WHERE, visible to ORDER BY`
Table alias	`FROM student AS s`	created at step 1	`the alias becomes the name of the source for the rest of the query`

Unknown is not falseSQL has three truth values. TRUE AND unknown is unknown, but FALSE AND unknown is plain false, because one false already settles an AND. Likewise TRUE OR unknown is true. WHERE keeps a row only when the whole expression comes back true, so unknown and false are thrown out together, and that is why a missing mark quietly disappears from queries nobody wrote about missing marks.

The name rule in one lineA name is usable by every clause that runs after the clause that created it. FROM creates the table’s columns and its alias at step 1, so both are available everywhere. SELECT creates column aliases at step 5, so they are available to steps 6, 7 and 8 and to nothing before them. There is no third rule to remember.

Logical order is not the execution planThe eight steps define what the answer must be, not the route the engine takes to it. A real optimiser will read an index instead of sorting, push a WHERE condition down into the table scan so filtered rows are never fetched, and stop reading as soon as LIMIT is satisfied. It may reorder as much as it likes provided the rows it returns are the rows these eight steps would have produced.

06 Where & why

The same query, five products, three things that move

The eight-step order is the part that does not change between databases. Three things around it do, and each of them has caught out somebody porting a query on their first job: how you spell LIMIT, whether LIKE cares about case, and where the NULLs go in a sort.

PostgreSQL

It tells you exactly what went wrong

SELECT marks AS score FROM student WHERE score >= 60 comes back with ERROR: column "score" does not exist, which names the problem precisely: not a bad rule, a missing name. ORDER BY score on the same query is accepted. LIKE here is case-sensitive, so name LIKE 'a%' matches none of our six students, and ILIKE is the case-insensitive form. In a sort, PostgreSQL puts NULLs last ascending and first descending unless you write NULLS FIRST or NULLS LAST yourself.

MySQL

Looser everywhere except in WHERE

MySQL allows a column alias in GROUP BY, HAVING and ORDER BY, and follows the standard in refusing it in WHERE, which tells you the restriction is about the running order and not about strictness. Its default collation is case-insensitive, so name LIKE 'a%' does match Asha and Arjun here, the opposite of PostgreSQL. It sorts NULLs first when ascending, and it accepts both LIMIT 2 OFFSET 1 and the older LIMIT 1, 2, where the offset is written first.

Oracle

No LIMIT keyword at all

Oracle has never had LIMIT. From 12c you write OFFSET 1 ROWS FETCH FIRST 2 ROWS ONLY after the ORDER BY. Older code uses ROWNUM, and that carries a trap this lesson explains for you: ROWNUM is assigned as rows are produced, before ORDER BY runs, so WHERE ROWNUM <= 2 ORDER BY city takes two arbitrary rows and then sorts those two. Oracle also stores an empty string as NULL, so WHERE city = '' matches nothing, ever.

SQL Server · SQLite

Two more spellings of the same eight steps

SQL Server has no LIMIT either: use SELECT TOP (2), or OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY, which is only legal when an ORDER BY is present. Its default collation is case-insensitive like MySQL’s. SQLite does have LIMIT 2 OFFSET 1, and its LIKE ignores case for ASCII letters. So the eight steps hold on all five, and that is as far as the agreement goes: the last line of our lesson query needs three different spellings across them, and name LIKE 'a%' finds Asha and Arjun on MySQL, SQL Server and SQLite while finding nobody at all on PostgreSQL or Oracle, with nothing changed but the server.

Learn the order once, because it is the same everywhere and it explains the alias rule, the WHERE versus HAVING rule and half the error messages you will see. Then, the first time you touch an unfamiliar database, check those three: the paging syntax, the collation, and the NULL sort position.

07 Interview questions

What they actually ask

SQL is the part of a DBMS interview where the interviewer can check you instead of believing you, because both of you can run the query. Answers that name the step number beat answers that name the rule.

In what order does a SELECT statement actually run?

FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then DISTINCT, then ORDER BY, then LIMIT or OFFSET. Give the list and then give the reason it is not the written order: the database needs rows before it can filter them, and it needs the filtered rows before it can work out what a column you invented is worth. Two consequences are what actually get asked about, so lead with them: a SELECT alias is invisible to WHERE and visible to ORDER BY, and WHERE filters rows while HAVING filters groups.

Why can WHERE not use a column alias when ORDER BY can?

Because the alias is created by SELECT, which runs fifth. WHERE runs second, so at that moment the name does not exist, and the database reports an unknown column rather than a syntax error. ORDER BY runs seventh, after SELECT has produced the column, so the same word resolves there without complaint. In WHERE you either repeat the expression the alias stood for, or wrap the whole query in an outer SELECT so the alias becomes a real column of the inner result.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before any grouping happens; HAVING filters the groups that GROUP BY produced. That order is exactly why WHERE cannot contain an aggregate such as COUNT(*): at step two there are no groups yet, so there is nothing to count. Practical rule: if a condition can be decided one row at a time, put it in WHERE, because it removes those rows before the grouping work is done.

Somebody writes WHERE marks = NULL and gets nothing back. What is wrong?

NULL means the value is not recorded, so comparing anything to it produces unknown rather than true or false, and WHERE keeps only the rows that come back true. That applies to NULL = NULL as well, so a row does not even match itself. IS NULL and IS NOT NULL are the only tests that work. On our table marks = NULL returns no rows and marks IS NULL returns Divya.

COUNT(*) returns 6 and COUNT(marks) returns 5 on the same table. Why?

COUNT(*) counts rows; COUNT(marks) counts the non-null values in that column. Divya is one row and zero values, so the two disagree by exactly one. Every aggregate behaves the same way: AVG(marks) divides by 5 and not by 6, which means a missing mark is silently ignored rather than counted as a zero. If you want it counted as zero you have to say so, with something like COALESCE(marks, 0).

What exactly does DISTINCT compare?

The whole select list, not the first column. SELECT DISTINCT city, marks removes a row only when both values match another row, which is why adding one column to the list usually brings the duplicates straight back. For this one purpose all nulls are treated as equal and fold into a single row, even though NULL = NULL is unknown everywhere else in SQL. And DISTINCT applies to the row, so SELECT DISTINCT(city), marks does not mean distinct cities; those brackets are decoration.

What do % and _ match in LIKE, and is LIKE case-sensitive?

% matches any run of characters including an empty one, and _ matches exactly one character, never zero. So 'A%' is anything starting with A, '%a' is anything ending in a, and '_a%' is anything whose second character is a. Case sensitivity is not part of the operator at all; it comes from the column’s collation. PostgreSQL is case-sensitive and offers ILIKE, while MySQL, SQL Server and SQLite are case-insensitive by default, so the same query can return different rows on two servers.

Is BETWEEN inclusive?

Yes, at both ends. marks BETWEEN 60 AND 80 is exactly marks >= 60 AND marks <= 80, so a 60 and an 80 both qualify. Two traps come with it. The low bound must be written first, because BETWEEN 80 AND 60 expands to an impossible pair and returns nothing rather than an error. And a null on either side makes the whole expression unknown, so a row with no marks is never between anything.

Does AND or OR bind tighter?

NOT first, then AND, then OR, the same way multiplication is applied before addition. So a OR b AND c means a OR (b AND c), which is rarely what somebody typing left to right had in mind, and no error is raised either way. Give the rule and then say what you do about it: put the brackets in even when the precedence already agrees with you, because the next person to read the query should not need to know the rule in order to trust it.

Is LIMIT 10 without an ORDER BY safe?

No. Without ORDER BY the row order is not defined, so LIMIT 10 hands you ten arbitrary rows, and the same query can return a different ten after an update or a change of plan. LIMIT runs last, on whatever arrangement the previous step happened to leave behind, so “the top ten” only means something once you have said what top means. Ties matter too: if the sort key repeats, the rows inside a tie are still in no defined order, so add a unique tiebreaker such as the primary key.

Is SELECT * acceptable?

Hard

Run SELECT DISTINCT city FROM student WHERE marks >= 60 ORDER BY marks; through the eight steps by hand and name the exact step at which the query can no longer be finished. Then give three different repairs that each return a defensible answer, and for each one say what it has silently decided on your behalf. Give the actual rows two of your repairs return.

Follow-up

Answer for the standard, not for your laptop: PostgreSQL, Oracle and SQL Server refuse this query, while SQLite and MySQL run it and hand back an arbitrary answer. One repair keeps DISTINCT and changes what a single row means; another keeps the meaning and gives up the sort you asked for. There is no repair that keeps both, and saying so is most of the answer.

Show the hint

Write out the one-column row set that DISTINCT hands to ORDER BY, then try to name the value ORDER BY is supposed to compare for the row that reads Pune.