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

Subqueries in the FROM Clause (Derived Tables)

Use subqueries in FROM to create temporary result sets that can be queried like regular tables.

Subqueries in the FROM Clause (Derived Tables)

You'll learn: How to use subqueries in the FROM clause to create temporary result sets that act like regular tables within your query.

What Are Derived Tables?

A derived table is a subquery placed in the FROM clause that creates a temporary, on-the-fly table you can query just like any physical table. Think of it as building a custom dataset mid-query, then immediately using it as your data source.

Instead of querying a permanent table, you're querying the results of another query. This is incredibly powerful when you need to perform multi-step transformations or calculations that can't easily be done in a single flat query.

Why Use Derived Tables?

Derived tables help you:

  • Pre-aggregate data before joining or filtering
  • Break complex logic into readable steps
  • Calculate values that you then use in outer-level filtering or grouping
  • Work around limitations where you can't directly use aggregate results

Basic Syntax

SELECT outer_columns
FROM (
    SELECT inner_columns
    FROM actual_table
    WHERE inner_conditions
) AS alias_name
WHERE outer_conditions;

Critical: You must give the derived table an alias (the AS alias_name part).

Simple Example

SELECT avg_salary_dept
FROM (
    SELECT department, AVG(salary) AS avg_salary_dept
    FROM employees
    GROUP BY department
) AS dept_averages
WHERE avg_salary_dept > 50000;

Here, the inner query aggregates salaries by department. The outer query treats those results as a table called dept_averages and filters departments with high averages.

Key Takeaway: Subqueries in FROM create reusable, temporary result sets that simplify complex queries by breaking them into logical, nested steps—just remember to always alias your derived tables.