How a SELECT query is processed

2.1 Written order and logical order

SQL is declarative. You describe the result you want, and the engine chooses how to build it. That is why the order you type does not match the order the engine applies. The written form starts with SELECT because that is the most interesting part, but the engine must know the source rows before it can compute anything from them.

The logical order is FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then DISTINCT, then ORDER BY, then LIMIT. Later modules fill in the grouping stages. For a plain query, read the list as: gather rows, drop rows, compute output columns, remove duplicates, sort, cut. The list is a specification of meaning, not a promise about the physical plan. A smart engine may reorder work as long as the result matches this specification.

The alias rule follows from the list. A select-list alias is created in the SELECT stage, which runs after WHERE, so standard SQL does not allow that alias inside WHERE. SQLite accepts it as an extension, and relying on the extension produces a query that breaks on PostgreSQL. ORDER BY runs after SELECT, so an alias is legal there, and GROUP BY is engine-dependent in the same way.

2.2 FROM builds the working set

FROM produces the rows every later stage reads. With one table, the working set is every row of that table. With a join, the working set is the combined rows the join condition produces, and a wrong or missing condition multiplies rows instead of matching them. The engine may read a table in rowid order, through an index, or through a temporary structure it builds for a join, so the working set has no guaranteed order even when the table looks sorted.

Every stage after this one sees the working set and may shrink it. Unexplained row multiplication in a report usually starts here: two rows in the parent table matched by several children become more rows than the author expected.

2.3 WHERE keeps only TRUE rows

WHERE evaluates its condition on each working row and keeps the row only when the result is TRUE. Two consequences follow. First, a row whose condition evaluates to UNKNOWN disappears, which is the NULL trap from the previous lesson. Second, a condition written as bare NULL filters everything out, while a condition written as NOT (NULL) also filters everything out because NOT UNKNOWN is still UNKNOWN.

The engine can often move part of the filter into an index lookup. A condition such as student_id = 4 becomes a direct seek, while LOWER(name) = 'sana' usually forces a scan because the index stores the raw name, not its lowercase form. That distinction returns in module five when indexes become a topic of their own.

2.4 SELECT computes the output columns

SELECT evaluates each expression on the surviving rows and labels it. fee * 12, UPPER(city) and a literal string are all expressions. The stage does not change how many rows exist unless the expression is an aggregate, which collapses them, or unless the statement uses DISTINCT. An expression that references a column keeps that column's name; anything else needs an alias if you want a readable header.

A select alias such as fee_in_thousands exists only from this stage onward. Because SQLite is flexible, it lets the alias leak back into WHERE, and you can see that behaviour in the probe below. Keep that trick out of code that other people read. Use the base expression in WHERE, and use the alias in ORDER BY where the standard allows it.

2.5 DISTINCT, ORDER BY and LIMIT

DISTINCT runs after the select list, comparing whole output rows. It usually needs a temporary structure to detect duplicates: a sort or a hash table over the projected columns. ORDER BY then sorts whatever survived, using the output columns and their aliases. LIMIT runs last and stops the client from receiving more rows, though the engine still had to produce and sort the rows that feed it.

That last point decides whether LIMIT helps. With an index on the sort column, SQLite can walk the index in order and stop after the requested number of rows, so a top-10 query costs about ten rows instead of a full sort. Without a usable index, it sorts everything first and the limit only trims the last step.

2.6 Tracing one query stage by stage

The examples below trace SELECT name, city FROM students WHERE city IS NOT NULL AND joined_on >= '2026-02-01' ORDER BY joined_on DESC LIMIT 2 on the LearnHub data. Follow the row counts as each stage runs. The first block is the working set of all nine students, limited to the columns that matter for the trace.

Nine rows enter WHERE. city IS NOT NULL removes Zoya Ali, whose city is unknown. joined_on >= '2026-02-01' removes Aarav Sharma and Meera Iyer, who joined in January. Six rows survive, and SELECT then projects just the name, city and join date.

ORDER BY joined_on DESC sorts those six rows, newest first. The tie-breaker question is worth asking: if two students joined on the same day, the order between them is not defined unless the query says more. Add name ASC as a second key when a stable page matters. LIMIT 2 then keeps the first two sorted rows and the client receives Dev Patel and Neel Joshi.

EXPLAIN QUERY PLAN shows the strategy instead of the rows. The same query over Delhi students shows a full scan and a temporary B-tree for the sort, because there is no index on city or joined_on in the practice database. The word SCAN means the engine walked the table; SEARCH would mean it used an index to reach rows directly.

2.7 What each stage costs

The table is a model, not a measurement. A filter that uses an index can turn the WHERE row from n into a handful of seeks, and an ordered index can remove the sort entirely. When a query feels slow, the useful question is which row of this table the engine is doing far more work than you expected.

2.8 Where SQLite and PostgreSQL differ

Both engines follow the same logical order and neither promises row order without ORDER BY. The differences live in the edges, which is exactly where interview questions land. Saying "it depends on the engine, and here is the SQLite rule" is a stronger answer than picking one behaviour.

2.9 Common mistakes

• Assuming rows come back in insertion order. A page without ORDER BY looks stable until it is not.
• Putting a select alias in WHERE and testing only on SQLite.
• Believing LIMIT 10 makes a slow query fast. Without an index the sort still runs over every row.
• Forgetting that DISTINCT compares the full select list.
• Reading EXPLAIN QUERY PLAN output as a fixed format. The strategy names and columns can change between SQLite releases, so checks should assert behaviour, not exact plan text.