SELECT

SELECT chooses the columns and expressions in the result, and AS names them for GROUP BY, HAVING and ORDER BY.

SyntaxSELECT [ALL | DISTINCT] select_expr [[AS] alias_name] [, select_expr ...]

SELECT is projection. Out of everything the FROM clause makes available, the select list chooses what appears in the result and in what shape. An entry can be a column, a literal, a function call, an arithmetic expression, a CASE, or a subquery returning a single value — anything that evaluates to one value per row.

Aliases: two spellings of the same thing

expr AS alias and expr alias are identical; the manual says the AS keyword is optional when the alias is a plain identifier. Write AS anyway. Precisely because it is optional, a missing comma between two select list entries is not a syntax error — MySQL reads the second name as the alias of the first, so SELECT columna columnb FROM mytable returns one column of columna values headed columnb.

Where the alias can be used

A select-list alias may be referenced from GROUP BY, ORDER BY and HAVING, but not from WHERE: when WHERE is evaluated the column value may not yet have been computed. Repeat the expression, or wrap the query in a derived table.

Star

A bare * selects every column of every table in FROM; tbl_name.* selects every column of one table and can be mixed with named expressions. Neither belongs in application code, where a later ALTER TABLE silently changes the shape of every result.

Example

Example · sql
SELECT
    o.id,
    o.placed_at,
    o.subtotal_cents / 100                 AS subtotal,      -- expression
    ROUND(o.subtotal_cents * 0.21 / 100, 2) AS vat,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    'EUR'                                  AS currency,      -- literal column
    CASE WHEN o.shipped_at IS NULL THEN 'pending'
         ELSE 'shipped' END                AS fulfilment
FROM orders    AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.placed_at >= '2026-01-01'   -- must repeat expressions, not use aliases
ORDER BY subtotal DESC;             -- ORDER BY, however, may use the alias

When to use it

  • Reshaping raw columns into the exact fields an API response needs.
  • Naming a computed column so ORDER BY and HAVING can refer to it once instead of repeating it.
  • Replacing SELECT * in application queries so a schema change cannot alter the result shape.

More examples

Longhand, shorthand, and the trap between them

Read this before reviewing anyone's SQL. A dropped comma in a long select list produces a query that runs, returns data, and is wrong — the classic version is a report column that quietly shows the wrong field.

Example · sql
-- Longhand: AS spelled out.
SELECT CONCAT(last_name, ', ', first_name) AS full_name FROM customers;

-- Shorthand: identical statement, AS omitted.
SELECT CONCAT(last_name, ', ', first_name)    full_name FROM customers;

-- And the reason to prefer the longhand. This is NOT two columns: it is one
-- column of first_name values, headed "last_name". No warning, no error.
SELECT first_name last_name FROM customers;
SELECT first_name, last_name FROM customers;  -- what was meant

When the alias is not in scope

The three ways out of "Unknown column 'cnt' in 'where clause'". Which one you want depends on whether the condition is about a group (HAVING) or about a row (repeat, or wrap).

Example · sql
-- Illegal: cnt does not exist yet when WHERE is evaluated.
-- SELECT customer_id, COUNT(*) AS cnt FROM orders WHERE cnt > 3 GROUP BY customer_id;

-- Groups are filtered by HAVING, which may use the alias.
SELECT customer_id, COUNT(*) AS cnt
FROM   orders
GROUP  BY customer_id
HAVING cnt > 3;

-- For a per-row computed value, either repeat the expression in WHERE or wrap
-- the query in a derived table, whose columns ARE in scope for the outer WHERE.
SELECT o.id, o.gross
FROM  (SELECT id, subtotal_cents * 1.21 AS gross FROM orders) AS o
WHERE o.gross > 10000;

A select list with no table at all

A scratchpad. Use it to settle a format string, a rounding question or a CAST result in one round trip, rather than running a heavy query repeatedly to see what one column looks like.

Example · sql
-- FROM is optional. One row comes back, which makes this the fastest way to
-- check what an expression actually returns before committing it to a query.
SELECT
  DATE_FORMAT('2026-09-17', '%W %e %M %Y') AS formatted,  -- Thursday 17 September 2026
  CAST('12.345' AS DECIMAL(6,2))           AS rounded,    -- 12.35
  CONCAT_WS('-', 'INV', 2026, LPAD(42, 6, '0')) AS invoice_no;  -- INV-2026-000042

Related

sql fromsql wheresql distinctsql casesql order by

Discussion

  • Be the first to comment on this lesson.