SQL / SUBQUERIES AND CTES
Derived tables and giving them an alias
Wrap a SELECT in the FROM clause, give it an alias, and use that alias to filter, sort and join on the columns it projects.
What you will learn
- Put an alias after every FROM subquery; PostgreSQL and MySQL reject one without it.
- Reference derived columns through the alias; the inner table names leave scope.
- Alias expressions like SUM(amount) inside the subquery so the outside can name them.
- Rename all columns at once with AS t(a, b) in PostgreSQL or MySQL 8.0.19+.
Understanding Derived tables and giving them an alias
A derived table is a SELECT written inside the FROM clause of another query. The outer query treats the result like a table, but that result has no name of its own: nothing stores it and it disappears when the statement ends, so SQL makes you supply a name after the closing parenthesis. Leave it out and PostgreSQL answers "subquery in FROM must have an alias" while MySQL raises error 1248, "Every derived table must have its own alias"; SQLite quietly invents a name, which is exactly why a query written there can break when it moves.
The alias opens a new namespace. Outside the parentheses the only columns that exist are the ones the inner SELECT list projected, and the only qualifier that resolves is the alias: r.total, never orders.total. The base tables the subquery read are out of scope, so SELECT orders.region FROM (SELECT region ... FROM orders) AS r fails even though every value came from orders. Treat it as a temporary table whose definition happens to sit inline: the alias is the table name and the inner SELECT list is the column list.
That makes column naming part of the interface rather than cosmetics. An expression such as SUM(amount) with no AS gets a generated name, "sum" in PostgreSQL and the raw expression text in MySQL and SQLite, so alias it inside and the outer query has something stable to say. PostgreSQL and MySQL 8.0.19+ also accept the list form, AS r(region, total), which renames the columns positionally and rejects the query if the count stops matching the SELECT list.
CREATE TABLE orders (id INTEGER, region TEXT, amount INTEGER);
INSERT INTO orders VALUES
(1, 'north', 120),
(2, 'north', 80),
(3, 'south', 200),
(4, 'east', 40),
(5, 'south', 60);
SELECT r.region, r.total
FROM (
SELECT region, SUM(amount) AS total
FROM orders
GROUP BY region
) AS r
WHERE r.total > 100
ORDER BY r.total DESC;A derived table has no name of its own, so the alias you give it plus the columns its SELECT list projects are the entire vocabulary the outer query has for that result.
Worked examples
Naming a computed column
The inner AS name is what the outer WHERE and ORDER BY are allowed to mention.
CREATE TABLE line_items (id INTEGER, qty INTEGER, unit_price INTEGER);
INSERT INTO line_items VALUES (1, 3, 500), (2, 1, 1200), (3, 10, 90), (4, 2, 250);
SELECT li.id, li.total_cents
FROM (
SELECT id, qty * unit_price AS total_cents
FROM line_items
) AS li
WHERE li.total_cents >= 1000
ORDER BY li.total_cents;Example explained
Line 1qty * unit_price AS total_cents gives the expression a name; without AS the column would be called something different in every dialect.
Line 2li is the derived table's name, and li.total_cents resolves only because total_cents appears in the inner SELECT list.
Line 3The outer WHERE runs after the derived table is formed, so it can reuse total_cents, which a plain single-level SELECT cannot do with its own alias.
Line 4li.qty would be an error: qty was never projected, so it does not exist on the other side of the parenthesis.
Joining through the alias
Once aliased, a derived table joins like any table and its alias is the only handle on its columns.
CREATE TABLE departments (id INTEGER, name TEXT);
CREATE TABLE employees (id INTEGER, dept_id INTEGER, salary INTEGER);
INSERT INTO departments VALUES (1, 'sales'), (2, 'ops');
INSERT INTO employees VALUES (1, 1, 50000), (2, 1, 70000), (3, 2, 60000), (4, 2, 64000);
SELECT d.name, pay.headcount, pay.payroll
FROM departments AS d
JOIN (
SELECT dept_id, COUNT(*) AS headcount, SUM(salary) AS payroll
FROM employees
GROUP BY dept_id
) AS pay ON pay.dept_id = d.id
ORDER BY d.name;Example explained
Line 1The ON clause compares pay.dept_id with d.id; both sides are qualified by alias, so neither id is ambiguous.
Line 2pay.headcount works because COUNT(*) was aliased inside; unaliased it would have a dialect-specific name and this reference would fail.
Line 3employees.salary in the outer SELECT would be rejected: employees is a FROM item of the subquery only, and pay replaced it outside.
Line 4Because pay is just a name for a result set, it can sit on either side of the JOIN exactly like departments does.
Renaming every column at once
The alias can carry a column list that names the derived table's output positionally.
CREATE TABLE signups (city TEXT, plan TEXT);
INSERT INTO signups VALUES
('lisbon', 'free'), ('lisbon', 'pro'), ('oslo', 'pro'),
('lisbon', 'free'), ('oslo', 'free'), ('porto', 'pro');
SELECT t.city, t.n
FROM (
SELECT city, COUNT(*)
FROM signups
GROUP BY city
) AS t(city, n)
ORDER BY t.n DESC, t.city;Example explained
Line 1AS t(city, n) names the table and its two columns in order, so COUNT(*) needs no inner AS.
Line 2The list must have exactly as many names as the SELECT list; adding a third inner column without updating the list makes the statement fail instead of silently shifting names.
Line 3Only the outer names survive, so t.count would not resolve even in PostgreSQL, where that would otherwise be the generated name.
Line 4This form works in PostgreSQL and MySQL 8.0.19+; SQLite accepts only the bare alias, so there you must alias the columns inside.
Important notes
The alias is a scoping device, not an order to materialise anything; planners routinely flatten a derived table into the outer query, so adding one changes names, not performance.
A plain derived table cannot see columns of tables listed earlier in the same FROM clause; that needs LATERAL. And if the alias reuses a real table's name, the alias wins for the rest of the query.
Common mistakes
Dropping the alias because SQLite accepted the query: on PostgreSQL it fails with "subquery in FROM must have an alias" and on MySQL with error 1248, so the statement breaks the moment it moves.
Qualifying with the inner table, as in orders.region, after aliasing the derived table as r; PostgreSQL reports a missing FROM-clause entry for orders and the query never runs.
Filtering on r.total when the subquery selected SUM(amount) with no AS total: the value is there under a generated name, so you get an unknown-column error instead of a result.
Try it yourself
Change, predict, then run
Given sales(rep TEXT, amount INTEGER), build a derived table aliased s that groups by rep and projects SUM(amount) AS total, then use only the outer query to keep totals above 500 and sort them highest first, without HAVING.
Open the SQL workspaceCheck your understanding
The statement SELECT orders.region, r.total FROM (SELECT region, SUM(amount) AS total FROM orders GROUP BY region) AS r; is rejected. Why?
- Because region was not given its own AS name inside the subquery.
- Because the outer query needs a GROUP BY that matches the subquery's GROUP BY.
- Because r is the only name for that result; orders exists as a FROM item inside the subquery only.
- Because a derived table cannot contain an aggregate such as SUM unless the outer query has a HAVING clause.
Show answer
The alias replaces the sources the subquery read, so a qualifier naming orders has nothing to bind to outside the parentheses; writing r.region fixes it. Option 0 is tempting but wrong: region already has a name, region, and it is projected — the broken part is the table qualifier, not the column name.