When the Database Engine doesn't read SQL the way you write it

When I first started approaching SQL, I held a very natural assumption: The lines written first will be parsed and executed first by the system.

This matches standard linear workflows. From traditional procedural programming to tools like Power Query or Excel, data transformations typically follow an explicit top-to-bottom sequence: Step 1 finishes, Step 2 processes the output, and Step 3 yields the final result.

In relational database systems, however, this assumption leads directly to one of the most common syntax errors in data querying.

You write an otherwise valid query, declare an aliased calculated field on line 2, and hit run. The Database Engine promptly returns: Unknown column ....

1. A common technical disconnect

Consider this standard query on a payments table:

The analytical objective is clear:

1. Retrieve the identifier customerNumber.

2. Compute an expression labeled tax as 10% of amount (amount * 0.1).

3. Filter the output to records where tax exceeds 5,000.

Syntactically, the statement appears well-formed. However, the Database Engine stops the execution with an error:

Why does the engine fail to recognize an alias declared on the second line of the statement?

2. Core cause: Lexical syntax vs. Logical query processing

This issue stems from the separation between Lexical order (how the query is written) and Logical query processing order (how the engine evaluates it).

SQL was originally designed in the 1970s around declarative English syntax to improve readability. In natural language, the objective is stated first: "Select record X from source Y where condition Z applies." Consequently, SELECT occupies the initial position in lexical structure.

However, database engines compile and optimize statements based on resource efficiency and relational algebra. The primary technical objective is minimizing disk I/O operations and memory allocation.

From an engineering perspective, the engine cannot evaluate expressions or project output columns (SELECT) before identifying the underlying physical storage locations (FROM).

As a result, upon parsing a query, the Database Engine restructures the execution into a different logical sequence:

3. Analyzing the mechanics of execution

Evaluating query clauses against the logical processing pipeline clarifies engine behavior:

·       Why does the WHERE clause reject the alias tax?

The WHERE clause executes at Step 2, immediately after locating the base tables. Its function is to filter raw records directly to minimize the volume of data loaded into memory buffers.

The SELECT clause, however, is evaluated at Step 5.

When the WHERE clause evaluates individual rows at Step 2, the expression amount * 0.1 has not yet been computed, and the alias tax does not yet exist in the memory namespace. The Unknown column error reflects the exact state of the logical processing pipeline.

·       Why does ORDER BY recognize the alias without issue?

Modifying the query to sort by the calculated field:

The statement executes without errors.

This succeeds because ORDER BY is evaluated at Step 6, directly after SELECT (Step 5) has calculated the projection list and assigned alias labels. By this stage, the intermediate result set is fully structured, allowing sorting operations to reference tax directly.

4. Aligning your code with execution logic

Once you understand the engine's internal order, you no longer need to fix syntax errors through guesswork.

The standard solution is straightforward: Repeat the raw calculation directly inside the WHERE clause rather than referencing the alias.

Modern cost-based Query Optimizers identify and consolidate duplicate expressions without incurring redundant computational overhead.

Understanding logical query processing marks the transition from memorizing disjointed syntax rules to navigating SQL as an integrated relational system. Aligning query construction with the engine's internal execution sequence prevents unexpected errors and clarifies how data moves through each stage of a query.