Course contentsShow
← Data Science
Lesson 971 of 2,14525. SQL Subqueries and CTEsFree lesson

Aliasing Derived Tables

Learn the required syntax for naming derived tables and referencing their columns in the outer query.

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.