SQL / SELECTING ROWS
Column and table aliases that keep queries readable
Rename columns and tables inside a single query with AS, qualify columns with a short table alias, and know why WHERE cannot see a SELECT alias.
What you will learn
- Label computed expressions with AS so result columns have usable names
- Attach a short alias to each table in FROM and qualify columns with it
- Reuse an alias in ORDER BY, but repeat the full expression in WHERE
- Double-quote an alias only when it needs spaces or exact capitalisation
Understanding Column and table aliases that keep queries readable
An alias is a name you attach to something for the length of one statement. On a column it names the value that comes out of the query, not the value stored in the table: after SELECT monthly_salary * 12 AS annual_salary the database still holds monthly_salary, and annual_salary exists only in the result you just received. This matters most for expressions, because monthly_salary * 12 has no name of its own, and different engines invent different headers for it. The AS keyword is optional, so SELECT monthly_salary * 12 annual_salary means the same thing, but writing AS makes the intent visible.
A table alias works differently: it renames the table for the rest of the statement, and the original name goes out of scope. Once you write FROM employees AS e, the reference employees.first_name is an error, and e.first_name is the only way to reach that column. That rule is what makes self-joins possible at all, since joining a table to itself gives you two copies that need two different names to tell apart. The same applies to subqueries in FROM, which usually must be given a name before you can refer to their columns.
The one thing that surprises everyone is where an alias is visible. A query is not evaluated top to bottom as written; the engine resolves FROM first, then WHERE, then GROUP BY and HAVING, then the SELECT list, and finally ORDER BY. Column aliases are created by the SELECT list, so WHERE runs before they exist and cannot use them, while ORDER BY runs after and can. Table aliases come from FROM, which is why they are available everywhere, including in WHERE.
CREATE TABLE employees (
id INTEGER,
first_name TEXT,
monthly_salary INTEGER
);
INSERT INTO employees VALUES
(1, 'Ada', 4200),
(2, 'Bruno', 3100),
(3, 'Chen', 5400);
SELECT e.first_name AS name,
e.monthly_salary * 12 AS annual_salary
FROM employees AS e
WHERE e.monthly_salary * 12 > 40000
ORDER BY annual_salary DESC;A column alias is created by the SELECT list and a table alias by the FROM clause, so each one is visible only in the clauses that are resolved afterwards.
Worked examples
Two aliases for one table
A self-join where table aliases are the only way to distinguish the two roles of the same table.
CREATE TABLE staff (
id INTEGER,
name TEXT,
manager_id INTEGER
);
INSERT INTO staff VALUES
(1, 'Rosa', NULL),
(2, 'Kofi', 1),
(3, 'Mira', 1),
(4, 'Tomas', 2);
SELECT worker.name AS employee,
boss.name AS reports_to
FROM staff AS worker
JOIN staff AS boss ON worker.manager_id = boss.id
ORDER BY employee;Example explained
Line 1FROM staff AS worker and JOIN staff AS boss give the same physical table two names, so each row of the join holds one employee and one manager.
Line 2Without the aliases, name and id would be ambiguous and the ON condition could not say which copy each column comes from.
Line 3The column aliases employee and reports_to are needed because both output columns would otherwise be called name.
Line 4ORDER BY employee uses the column alias, which works because ORDER BY is resolved after the SELECT list.
Aliases with spaces
Double quotes let an alias contain spaces and keep its exact capitalisation.
CREATE TABLE surveys (
respondent TEXT,
score INTEGER
);
INSERT INTO surveys VALUES
('r1', 7),
('r2', 9);
SELECT respondent AS "Respondent ID",
score * 10 AS "Score out of 100"
FROM surveys
ORDER BY "Score out of 100" DESC;Example explained
Line 1Double quotes mark an identifier, so "Respondent ID" is a column name that happens to contain a space.
Line 2Unquoted aliases are folded to a single case by most engines; the quoted form keeps Score out of 100 exactly as typed.
Line 3The quotes must be repeated in ORDER BY, because the alias only matches when written the same way.
Line 4Single quotes would create a string literal instead, which PostgreSQL rejects as a syntax error in AS.
The missing comma
A forgotten comma turns the next column name into an alias, and the query still succeeds.
CREATE TABLE cities (
name TEXT,
country TEXT,
population INTEGER
);
INSERT INTO cities VALUES
('Lyon', 'France', 522000),
('Porto', 'Portugal', 231000);
SELECT name country, population
FROM cities;Example explained
Line 1SELECT name country parses as name AS country, because AS is optional.
Line 2The result has two columns, not three: the real country values never appear.
Line 3The header says country while the data is city names, so the mistake is easy to miss when reading output.
Line 4Always writing AS makes this typo a syntax error instead of a wrong answer.
Important notes
An alias exists only for the statement that declares it; it does not rename the column in the table, and the next query knows nothing about it.
Which clauses may use a column alias varies by engine: ORDER BY works everywhere, MySQL also allows aliases in HAVING, and SQLite allows them in WHERE, so write standard SQL if the query has to be portable.
Common mistakes
Filtering with WHERE annual_salary > 40000 after defining annual_salary in the same SELECT: PostgreSQL and MySQL raise an unknown-column error because WHERE is resolved before the select list, and SQLite accepts it as an extension, so the habit only breaks when the query moves to another engine.
Writing FROM employees AS e and then referring to employees.first_name: the alias replaces the table name, so the query fails with a missing-FROM-entry or unknown-column error rather than falling back to the real name.
Dropping a comma between two columns, as in SELECT name country: the second name silently becomes an alias, the query runs, and a column disappears from the result.
Try it yourself
Change, predict, then run
Create a table books(title, pages) with four rows, then write one query that returns title as book and pages * 2 as est_minutes, keeps only rows where the doubled page count exceeds 500, and sorts by est_minutes descending.
Open the SQL workspaceCheck your understanding
In PostgreSQL, SELECT price * quantity AS total FROM orders WHERE total > 100 fails, but changing WHERE to ORDER BY total works. Why?
- WHERE is resolved before the SELECT list creates the alias, while ORDER BY is resolved after it
- WHERE can only compare stored columns, never a calculated value
- The alias must be double-quoted before WHERE can recognise it
- ORDER BY re-runs the query, so the alias is already available the second time
Show answer
Clause resolution order decides alias visibility: FROM and WHERE are handled before the select list exists, and ORDER BY after it, so total is unknown in one place and known in the other. The second option is tempting but wrong, since WHERE price * quantity > 100 is perfectly valid; the problem is the name, not the arithmetic.