Choosing the columns is the projection, which is the act of listing the ones that a reader needs. An alias is a nickname that you pin on a projected column, so that the output reads like a deliverable instead of a schema dump. Both of them are one-line moves, but they load the query with a timing rule that surprises the people in the WHERE clause.
The previous lesson in this module covered "How Do You Run Your First SELECT Statement?", and this lesson is the last one in the SQL Basics and Databases module.
What exactly does an alias change, and what does it not?
An alias relabels the result only. It renames nothing on the disk. The tools table from the last lesson still calls its columns name and price, but a query can hand them the friendlier names of item and unit_price while everybody downstream still thinks in the 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.4The keyword AS 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 exactly the same as an omitted AS.
SELECT name item, stock FROM tools LIMIT 3;item stock
---------------- -----
Ball-peen hammer 22
Torpedo level 15
Pliers set 11The semantics are the same and there is no ceremony at all. In a codebase, you prefer the explicit AS. The reviewers can then read any bare pair of identifiers as a bug, because it genuinely is one most of the time.
Now notice the part that the aliases cannot touch. Calling them anywhere does not alter the stored column, and exporting the result to a CSV will carry the alias as the header row.
When does an alias need the quotes around it?
The underscores keep the single-word aliases friction-free, such as unit_price. For a label that has a space, which is useful for the slide-ready or the stakeholder-facing exports, you wrap the alias in quotes.
SQLite accepts the single-quoted strings here, and MySQL tolerates them too. The SQL standard spelling is the double quotes, which is the form that PostgreSQL and SQL Server expect, and it is what I would write into anything that is 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.4The multi-word labels also make the alias the natural place to explain the units or the assumptions. The name price_in_gbp beats price the day that somebody else inherits the spreadsheet. That said, you save the fancy labels for the final layer of a pipeline. The internal staging queries should ship the boring names, so that the next author can reason about them.
Where in a query can you use an alias that you have just defined?
This is the timing rule. A SELECT list is computed after the filters. When the WHERE runs, the alias does not exist yet in the standard, so the portable SQL refers to the original column there.
In the tools table, ordering by the alias is the blessed pattern, because the 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.0Six rows are sorted by their report name. The item behaved exactly like a column, because the ORDER BY inspects the result after the projection.
As a strict-engine contrast, you try the same alias inside the 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 it returned one row. SQLite is the lenient engine, and the standard engines are not. The database administrators who are reviewing the cross-database SQL will flag it.
You write the filter against name and you sort against item, and the query survives any engine.
What makes a good alias name?
Three habits cover the day-to-day honesty. You keep them lower-case with the underscores, so that no engine trips on the case folding. You name the meaning rather than the source, with names like total_paid or days_overdue. And when an alias purely clarifies an abbreviation, you make the expansion evident. Two aliases that disagree about the units invite a support ticket that the query author quietly inherits for ever.
| 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 the output only, and nothing in the storage changes.- The AS is optional, but skipping it blurs into the missing-comma alias trap, so you keep it in the shared code.
- The multi-word aliases want the quotes. They are the double quotes in the standard engines, and SQLite additionally accepts the singles.
- The ORDER BY respects the aliases, and the WHERE in the standard engines does not.
Workshop task: produce a picking list from the tools table. You project the name as item, the stock as on_shelf, and you sort by the alias in a single query. Then, with malice on purpose, you add a WHERE on item, you run it both in SQLite and, if it is reachable, in any other engine, and you record in one sentence which one forgives you and why that matters in a code review.