How Does the SELECT Statement Work?

A SELECT is a pipeline with a fixed order of stages, and knowing that order explains most of the confusing behaviour you will meet. Logically the engine evaluates FROM first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. In reality the optimizer is free to rearrange the physical work — it can push a predicate down, choose a different join order or scan an index instead of the table — but it must preserve the logical result.

The key mental model is that FROM produces a set of rows with all the columns of the joined tables, WHERE cuts that set down, GROUP BY collapses it into one row per group, and SELECT chooses which columns or expressions to project out of what is left. ORDER BY then sorts the surviving rows and LIMIT takes the first few.

Two consequences follow immediately. An alias defined in the SELECT list cannot be referenced in WHERE, because the SELECT stage has not run yet — that is why you repeat the expression or wrap the query in a CTE. And a WHERE predicate on a column from the right-hand side of a LEFT JOIN changes the join type in effect, because filtering the joined output is not the same as filtering the right table before the join.

Another consequence: ORDER BY is not free and not optional in practice. Without it, the engine is free to return rows in any order it finds cheapest, so pagination built on OFFSET can repeat or skip rows between requests. If you want stable pagination you need a total ordering, which usually means adding a tiebreaker column to the ORDER BY.

The projection list of a SELECT can contain columns, expressions and aliases. An alias created with AS renames the result column for the client and for any ORDER BY or GROUP BY that follows.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  stock_qty INTEGER NOT NULL
);

INSERT INTO products (id, name, unit_price, stock_qty) VALUES
  (1, 'Mechanical Keyboard', 79.50, 24),
  (2, 'USB-C Hub', 32.00, 61),
  (3, '27-inch Monitor', 249.99, 7);

SELECT id,
       name AS product_name,
       unit_price,
       stock_qty
FROM products
ORDER BY unit_price DESC;

The result is three rows, with the 27-inch Monitor first at 249.99 and the USB-C Hub last at 32.00. The alias product_name is what the result set calls the second column — the underlying column is still called name in the table.

Two details an interviewer may notice. The AS keyword is optional in most engines, but writing it makes the intent explicit and avoids ambiguity when the alias looks like a reserved word. And the alias is scoped to the result set: you can order by product_name or group by it, but you cannot use it in the WHERE clause of the same query, because WHERE runs before the projection exists.

One more caution for production code: avoid SELECT *. It looks convenient, but it makes the query's output change whenever someone adds a column, it transfers columns you are not using, and it breaks a positional row mapping the moment the schema moves. Name the columns you need.

Derived columns are ordinary expressions in the projection list. They can be arithmetic, string concatenation, a CASE expression or a scalar function call. The engine evaluates them once per output row, after the FROM, WHERE and GROUP BY stages have run.

CREATE TABLE sales_lines (
  id INTEGER PRIMARY KEY,
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL
);

INSERT INTO sales_lines (id, quantity, unit_price) VALUES
  (1, 2, 19.99),
  (2, 5,  4.50),
  (3, 1,  0.00);

SELECT id,
       quantity,
       unit_price,
       quantity * unit_price AS line_total,
       CASE WHEN quantity > 0
            THEN ROUND((quantity * unit_price) / quantity, 2)
            ELSE 0.0
       END AS unit_price_check
FROM sales_lines
ORDER BY id;

The line totals are 39.98, 22.50 and 0.00. The third row is the interesting one: quantity is 1 and unit_price is 0.00, so the division does not divide by zero here — but the CASE guard exists because the same expression on a row with quantity 0 would produce a NULL in SQLite, where a zero divisor yields NULL rather than an error. PostgreSQL and MySQL raise an error in that situation, so the guard is what keeps the query portable.

Three rules worth internalising. Integer division truncates: 7 / 2 is 3 in SQLite and PostgreSQL, so cast to a numeric type when you want fractions. Division by zero is either an error or a NULL depending on the engine, so guard it explicitly. And arithmetic with any NULL operand yields NULL, which then propagates into your report.

For a derived column that you want to filter on later — say a discount band — put it in a CTE or a subquery rather than a WHERE clause, because the alias does not exist yet at the filtering stage.