Aliasing Derived Tables
What you'll learn: How to properly name derived tables and reference their columns when using subqueries in the FROM clause.
Why Aliasing is Required
When you create a derived table (a subquery in the FROM clause), SQL requires you to give it an alias—a temporary name. Unlike regular tables that already have names, derived tables are anonymous result sets that need identification for the query to work.
Think of it like this: if you're introducing someone at a party, they need a name. You can't just say "this person over here." Similarly, SQL needs a way to refer to your derived table.
Basic Syntax
SELECT alias_name.column_name
FROM (
SELECT column1, column2
FROM original_table
WHERE condition
) AS alias_name
The AS alias_name part immediately after the closing parenthesis is mandatory (though AS itself is optional in most databases).
Referencing Columns
Once aliased, you reference columns from the derived table using either:
- Just the column name:
SELECT column1 - Qualified with the alias:
SELECT dt.column1
SELECT dt.product_name, dt.total_sales
FROM (
SELECT product_name, SUM(revenue) AS total_sales
FROM sales
GROUP BY product_name
) AS dt
WHERE dt.total_sales > 10000
Here, dt is our alias. We must use the column names defined inside the subquery (total_sales, not the original revenue).
Common Mistake
Forgetting the alias causes an immediate syntax error:
-- ❌ WRONG - No alias
SELECT product_name
FROM (SELECT product_name FROM products)
Key Takeaway: Every derived table must have an alias immediately following its closing parenthesis; you then reference its columns using names defined within the subquery, optionally qualified by the alias.