What SELECT really does

The idea

A query reads like a sentence, but the database does not run it in that order. It runs the clauses in a fixed sequence: first it decides which table to look at (FROM), then which rows to keep (WHERE), and only then does it build the columns you asked for (SELECT). Sorting and cutting the list come last.

SELECT itself is simple. For every row that survived, it produces one output row, whose values are columns you copy, expressions you compute, or both. It never adds, removes or reorders rows. That is the job of the clauses around it.

Why it exists

Imagine a person doing this by hand with a printed product list. They would not write the answer column first. They would find the sheet, cross out the rows that do not matter, and only then work out the margin for the rows that remain. SQL is declarative: you describe the result and the database picks how to get it. The fixed order is what makes your description unambiguous. Whatever engine runs it, WHERE category = 'Furniture' means "decide this before anything is computed".

The order also keeps queries fast. Filtering first means the database computes a margin for the rows you keep and not for every order line in the store. And because FROM comes first, every other clause can refer to the table's columns without saying where they come from.

Seen this way, a query is a small production line: each clause takes the output of the one before and hands a smaller or reshaped table to the next. You never see the intermediate tables, but you can always rebuild them in your head, and that is how analysts debug.

How it works under the hood

Follow this query through the machine:

SELECT name, unit_price - unit_cost AS margin
FROM products
WHERE category = 'Furniture'
ORDER BY margin DESC
LIMIT 3;

1. FROM opens products: every row, every column.
2. WHERE tests category = 'Furniture' on each row and keeps those that pass.
3. SELECT builds the output. For each kept row it copies name, computes the subtraction and labels the result margin.
4. ORDER BY sorts the output rows. It runs after SELECT, so the alias margin already exists.
5. LIMIT keeps the first three rows of the sorted list.

Expressions work on one row at a time

unit_price - unit_cost never looks at the row above or below. The subtraction runs once per row, with that row's two values. That is why a SELECT with an expression returns as many rows as the table has after filtering. It does not summarise anything. Summaries are chapter 4.

NULL is a value you can't calculate with

A NULL is not zero and not an empty string. It marks "unknown", and any arithmetic with an unknown gives an unknown. In Harbor & Pine some products have no recorded cost, so their margin is NULL. The same applies to joining text: 'Price: ' || NULL is NULL. COALESCE(unit_cost, 0) swaps a NULL for a value you choose, which is a business decision. Make it on purpose.

Reading a query in running order

When a result looks wrong, do not read from the top. Walk the query the way the machine does. Run only SELECT * FROM products WHERE category = 'Furniture' and look at the rows. Do they match what you expected? Then add the expression and check one product by hand: 919.00 - 503.50 should be 415.50. Then add the sort, then the limit. Each step changes one thing, so a wrong number always points to the clause you added last. This habit is worth more than any single function you learn in this chapter.

Aliases and DISTINCT

AS margin renames an output column. It makes the result readable and gives later clauses a short name, but only the clauses that run after SELECT can use it. ORDER BY can. In Postgres, MySQL and SQL Server WHERE cannot, and the fix is to repeat the expression. Some engines, DuckDB included, let you off, so do not rely on it. Likewise SELECT DISTINCT category runs on the output rows after SELECT: it removes duplicates among the values you chose, not among the table's rows.

What you can show

Because SELECT builds the output, you can put anything in it that is computed from the row: a column, a calculation, a rounded value, a piece of text glued together. Each of these becomes a column of the result with the name you give it. Without an alias the database invents a name such as unit_price - unit_cost, which is awkward in a spreadsheet. Name every computed column.

Common misconceptions

"SELECT runs first, because I write it first"

It is written first because humans like to start with the answer. The database starts with FROM. That is why you can't reference a column alias in WHERE, and why SELECT can't make rows disappear. When a query behaves strangely, read it in running order, not writing order.

"SELECT * is the same as listing the columns"

It returns the same rows now, but it quietly changes the day someone adds or reorders a column, and it moves data you never use. Naming columns also documents what the query is for. Use * to look around, and name the columns in anything you keep.

"A missing cost counts as zero"

The database will never decide that for you. NULL flows through arithmetic and comes out as NULL. If you silently treat it as zero, the loveseat looks like a product with a margin of the full $1,659. Ask whether unknown really means zero, then say so with COALESCE.