What can you put in a SELECT list besides plain column names?
answer
- More than just column names goes there
- Think values, not only stored data
- Constants and computed formulas both qualify
- Standard calls them value expressions
- One expression equals one output column
basics
~20 sAny value expression: literals, arithmetic and string operators, scalar function calls such as UPPER, SUBSTRING or CAST, and CASE expressions. Each one is evaluated per row and produces a derived column, normally named with AS.
solid answer
~40 sThe SELECT list is a list of **value expressions**, not just column references. Each item can be a column (`o.total`), a literal (`'ACTIVE'`, `42`, `DATE '2026-01-01'`), an operator expression (`price * quantity`, `first_name || ' ' || last_name`), a scalar function call (`UPPER(city)`, `SUBSTRING(code FROM 1 FOR 3)`, `CAST(total AS DECIMAL(10,2))`, `EXTRACT(YEAR FROM order_date)`), or a `CASE` expression. The engine evaluates every item once **per result row**, so the SELECT list never adds or removes rows — it only decides the shape of each one. An expression that is not a bare column has no natural name, so give it one with `AS`: `price * quantity AS line_total`. A literal item simply repeats the same constant on every row.
code
sql · 9 linesSELECT
o.order_id,
'ACTIVE' AS status,
o.price * o.quantity AS line_total,
UPPER(c.city) AS city_upper,
SUBSTRING(o.sku FROM 1 FOR 3) AS sku_prefix,
EXTRACT(YEAR FROM o.order_date) AS order_year
FROM order_lines o
JOIN customers c ON c.customer_id = o.customer_id;go deeper
Be ready to write a SELECT list mixing a column, a constant, and one computed column, and to alias the computed one with AS. Know that UPPER, CAST and CASE are all legal there.
Explain that each item is a value expression evaluated once per row, that the SELECT list cannot change the row count, and that an unnamed expression's column name is implementation-dependent.
Show judgment about where derivations live: repeated formulas belong in a view or CTE rather than copy-pasted into a dozen queries, and result columns consumed by applications must carry explicit, stable aliases.
Own the convention: which derivations are computed in SQL versus in the application or a generated column, and how a team keeps a shared formula from drifting across reports and services.
## What the SELECT list actually is Beginners read `SELECT` as "pick some columns". The standard is broader: what sits between `SELECT` and `FROM` is a comma-separated list of **value expressions**, each producing exactly one column of the result. A bare column reference is just the simplest possible expression. The number of items in the list is the number of columns you get back; the number of rows is decided entirely by `FROM`, `WHERE`, `GROUP BY` and the rest — never by the SELECT list. ## The kinds of items you can write **Column references.** `total`, or qualified as `o.total` when more than one table is in scope. **Literals.** `SELECT 'ACTIVE' AS status, 1 AS is_current FROM orders` returns those constants on every row. Typed literals exist too: `DATE '2026-01-01'`, `TIMESTAMP '2026-01-01 08:00:00'`, `INTERVAL '30' DAY`. **Operator expressions.** Arithmetic (`price * quantity`, `total - discount`), string concatenation (`first_name || ' ' || last_name` in standard SQL), and date arithmetic (`order_date + INTERVAL '7' DAY`). **Scalar function calls.** A scalar function takes values and returns one value per row: `UPPER(city)`, `LOWER(email)`, `CHARACTER_LENGTH(name)`, `TRIM(BOTH ' ' FROM code)`, `SUBSTRING(sku FROM 1 FOR 3)`, `COALESCE(nickname, first_name)`, `NULLIF(status, '')`, `CAST(total AS DECIMAL(10,2))`, `EXTRACT(YEAR FROM order_date)`. The exact catalogue of functions is where engines diverge most, so a portable query sticks to the small standard set. **Conditional expressions.** `CASE` produces a value chosen by conditions and is legal anywhere a value expression is. **Aggregates and window functions** also appear in SELECT lists, but they are a different animal: an aggregate collapses many rows into one and drags `GROUP BY` rules along with it, and a window function computes over a set of rows without collapsing them. Both are their own subject; everything in this answer concerns plain per-row expressions. ## Every item is evaluated per row This is the mental model that makes the rest obvious. For each row that survives filtering, the engine evaluates each SELECT-list expression against that row's values and emits the results side by side. So `SELECT 1 FROM orders` on a 500-row table returns 500 rows each containing `1` — the constant does not compress anything, and a computed column does not add rows. Because each item is independent, an expression cannot see the value another item computed for the same row. If you need `price * quantity` twice, you write it twice, or compute it once in a subquery or CTE and select from that. ## Naming derived columns A bare column reference inherits the column's name. Every other expression has no name of its own, and what an engine calls an unnamed derived column is **implementation-dependent** — you may get `?column?`, the expression text, or a positional name. Client code that reads results by column name therefore should never rely on it: write `price * quantity AS line_total` and address `line_total`. ## Portability notes worth knowing The standard requires a `FROM` clause, so a bare `SELECT 1 + 1` is not standard SQL — PostgreSQL, MySQL, SQL Server and SQLite accept it anyway, while Oracle traditionally wants `SELECT 1 + 1 FROM DUAL`. The standard does not promise you an evaluation order across the items in a SELECT list, so never write a query whose correctness depends on one item being computed before another. String and date function names are the least portable part of the list. `UPPER`, `LOWER`, `TRIM`, `SUBSTRING(x FROM a FOR b)`, `CHARACTER_LENGTH`, `COALESCE`, `NULLIF`, `CAST` and `EXTRACT` are the standard spellings; the shorter or friendlier variants you may be used to (`SUBSTR`, `LEN`, `IFNULL`, `NVL`, `GETDATE`) are engine-specific. ## Practical guidance Use expressions in the SELECT list for presentation-shaped derivations — a display name, a line total, a truncated code, a year bucket. Keep the expression readable and named, and if the same derivation appears in three queries, put it in a view or a CTE rather than copy-pasting a formula that will eventually drift.
- If you omit AS on a computed column, what is that result column called?It is implementation-dependent. The standard only guarantees a name for a bare column reference; for an expression, engines may produce a placeholder name, echo the expression text, or use a positional name. Client code that reads results by name should always supply an explicit alias, for example `price * quantity AS line_total`.
- Does adding an expression to the SELECT list ever change how many rows come back?No. Plain per-row expressions only change the shape of each row, never the row count — that is fixed by `FROM`, `WHERE` and grouping. `SELECT 1 FROM orders` returns one row per surviving order. The exception is aggregation: an aggregate in the SELECT list collapses rows, but that is `GROUP BY` semantics, not the expression itself.
- Can one SELECT-list item reference the result of another item in the same SELECT list?Not in standard SQL. Each item is evaluated independently against the source row, so you cannot compute `price * quantity AS line_total` and then use `line_total * 1.08` in the next item. Repeat the expression, or compute it once in a derived table or CTE and select from that.
saying these in an interview costs you the question
- Thinks only stored columns may appear after SELECT
- Believes a literal column adds or removes rows
- Assumes unnamed expressions get a stable, portable column name
- Expects a later SELECT item to reuse an earlier item's alias
- Calls SUBSTR or GETDATE standard SQL functions