Course contentsShow
← Computer Science
Lesson 1608 of 2,87236. Database FundamentalsFree lesson

Subqueries in FROM Clause

Creating derived tables by placing subqueries in the FROM clause and assigning them table aliases.

Subqueries in FROM Clause

What you'll learn: How to use subqueries in the FROM clause to create temporary, derived tables that you can query just like regular tables.

The Core Idea

Until now, you've seen subqueries inside WHERE, SELECT, and with operators like EXISTS and IN. But you can also place a subquery in the FROM clause itself, treating its result set as if it were a temporary table. This is called a derived table or inline view.

Think of it like this: instead of querying a permanent table stored in your database, you're querying the results of another query. It's like asking "show me all the products" versus "show me the top-selling products from last month, then tell me which of those are in stock."

Syntax and Usage

The pattern looks like this:

SELECT column_name
FROM (subquery) AS alias_name
WHERE condition;

The alias is mandatory — your derived table needs a name so you can reference its columns in the outer query.

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 subquery calculates average salaries per department and groups them. The outer query treats that result as a table called dept_averages and filters it further.

Why Use This?

Derived tables let you break complex queries into logical steps, making them more readable. They're especially useful when you need to aggregate data first, then filter or join on those aggregated results.

Key Takeaway: Subqueries in the FROM clause create temporary derived tables that must be given an alias and can be queried like any other table, enabling multi-step query logic.