Choosing columns is projection — listing the ones a reader needs — and an alias is a nickname you pin on a projected column so the output reads like a deliverable instead of a schema dump. Both are one-line moves, but they load the query with a timing rule that surprises people in WHERE.
What exactly does an alias change, and what does it not?
An alias relabels the result only. It renames nothing on disk: the tools table from the last lesson still calls its columns name and price; a query can hand them friendlier names — item, unit_price — while everyone downstream still thinks in schema terms. Here is the same data wearing its report clothes:
SELECT name AS item, price AS unit_price FROM tools;
item unit_price
---------------- ----------
Ball-peen hammer 17.9
Torpedo level 12.6
Pliers set 24.3
Chalk line 8.95
Caulking gun 11.2
Wire strippers 9.4
The AS keyword is documentation: it separates the column from the nickname at a glance. It is also optional, which keeps the alias trap from our first-SELECT lesson alive — a missing comma looks identical to an omitted AS:
SELECT name item, stock FROM tools LIMIT 3;
item stock
---------------- -----
Ball-peen hammer 22
Torpedo level 15
Pliers set 11
Same semantics, zero ceremony. In a codebase, prefer the explicit AS — reviewers can then read any bare pair of identifiers as a bug, because it genuinely is one most of the time. Note the part aliases cannot touch: CALLING them anywhere does not alter the stored column, and exporting the result to CSV will carry the alias as the header row.
When does an alias need quotes around it?
Underscores keep single-word aliases friction-free: unit_price. For a label with a space — useful for slide-ready or stakeholder-facing exports — wrap the alias in quotes. SQLite accepts single-quoted strings here (MySQL tolerates them too); the SQL standard spelling is double quotes, which is the form PostgreSQL and SQL Server expect, and it is what I would write into anything shared:
SELECT name AS 'Shelf label', price AS 'Desk price' FROM tools WHERE price < 12;
Shelf label Desk price
-------------- ----------
Chalk line 8.95
Caulking gun 11.2
Wire strippers 9.4
Multi-word labels also make the alias the natural place to explain units or assumptions — price_in_gbp beats price the day someone else inherits the spreadsheet. That said, save fancy labels for the final layer of a pipeline; internal staging queries should ship boring names so the next author can reason about them.
Where in a query can you use an alias you just defined?
This is the timing rule. A SELECT list is computed after filters: when WHERE runs, the alias does not exist yet in the standard — so portable SQL refers to the original column there. In the tools table, ordering by the alias is the blessed pattern (sorts happen last, on the finished projection):
SELECT name AS item, weight FROM tools ORDER BY item LIMIT 4;
item weight
---------------- ------
Ball-peen hammer 480.0
Caulking gun 380.0
Chalk line 95.0
Pliers set 640.0
Six rows sorted by their report name — item behaved exactly like a column because ORDER BY inspects the result after the projection. As a strict-engine contrast, try the same alias inside WHERE on PostgreSQL:
SELECT name AS item FROM tools WHERE item = 'Chalk line';
-- SQLite 3.39: fine. PostgreSQL: error - column item does not exist.
-- MySQL and SQL Server reject it too. Order of evaluation, not opinion.
My sandbox ran it happily and returned one row — SQLite is the lenient engine; the standard engines are not, and DBAs reviewing cross-database SQL will flag it. Write the filter against name and the sort against item, and the query survives any engine.
What makes a good alias name?
Three habits cover day-to-day honesty: keep them lowercase with underscores so no engine trips on case folding; name the meaning rather than the source (total_paid, days_overdue); and when an alias purely clarifies an abbreviation, make the expansion evident. Two aliases that disagree about units invite a support ticket that the query author quietly inherits forever.
| Rule | Why it holds across engines |
|---|---|
| Alias with AS, then sort by the alias | ORDER BY sees the final projection on every engine |
| Do not filter by an alias in WHERE | Filters run before projection; new names are too young to be usable |
| Quote only multi-word aliases | Standard spellings; single words need no quotes anywhere |
Quick reference
SELECT column AS nicknamerelabels output only — nothing in storage changes.- AS is optional; skipping it blurs into the missing-comma alias trap, so keep it in shared code.
- Multi-word aliases want quotes — double quotes in standard engines, and SQLite additionally singles.
- ORDER BY respects aliases; WHERE in the standard engines does not.
Workshop task: produce a picking list from tools: project name as item, stock as on_shelf, and sort by the alias in a single query. Then — with malice on purpose — add a WHERE on item and run it both in SQLite and (if reachable) any other engine, and record in one sentence which one forgives you and why that matters in a code review.