Stages of executing a SELECT query

WHAT THIS TESTS The interviewer checks that you understand a database is a pipeline of distinct stages, not a black box that just runs SQL.

A GOOD ANSWER COVERS First, parsing: the server takes the raw SQL string and checks its syntax, producing a parse or syntax tree representing the statement's structure. Second, binding and analysis, sometimes called semantic analysis: the engine resolves the names in that tree against the system catalog, confirming the tables and columns exist, attaching their types, checking the user's permissions, and rewriting views and other constructs. Third, optimization: a cost-based optimizer considers logically equivalent ways to compute the result, choosing access methods like index scan versus sequential scan, join algorithms, and join order, using table statistics to estimate costs, and emits a physical execution plan. Fourth, execution: the executor walks that plan tree of operators, requesting pages through the buffer manager, performing scans, joins, filters, sorts, and aggregations, and streaming the resulting rows back to the client, often incrementally rather than all at once. Many engines also cache compiled plans so repeated queries skip parsing and optimization.

COMMON WRONG ANSWERS Skipping the optimizer entirely, as if SQL runs literally as written. Conflating parsing with planning. Forgetting catalog binding and permission checks. Assuming all rows are materialized before any are returned, when execution is typically streamed.

LIKELY FOLLOW-UPS What does the optimizer use statistics for; what is plan caching; how is a prepared statement different; what is the difference between logical and physical plans.

ONE CONCRETE EXAMPLE For SELECT name FROM users WHERE id = 5, the parser builds a tree, binding confirms users and its columns and the user's read permission, the optimizer sees an index on id and picks an index scan over a full scan, and the executor seeks that index, fetches the matching row's page, and returns the single name value to the client.