Subquery in FROM clause (derived table) in SQL

Introduction

Sometimes you want to use the result of one query as a temporary table inside another query. This helps break down complex questions into smaller parts.

When you want to calculate some values first and then use them to filter or sort results.

When you need to join a summary table with another table.

When you want to organize your query in steps for better understanding.

When you want to reuse a set of rows temporarily without creating a permanent table.

Syntax

SQL

SELECT columns
FROM (SELECT columns FROM table WHERE condition) AS alias
WHERE outer_condition;

The subquery inside the FROM clause acts like a temporary table.

You must give the subquery an alias (a temporary name) to use it in the outer query.

Examples

This finds departments with an average salary above 50,000 by first calculating averages in the subquery.

SQL

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

This gets customers who spent more than 1000 by summarizing sales first, then joining with customer info.

SQL

SELECT name, total_sales
FROM (SELECT customer_id, SUM(amount) AS total_sales FROM sales GROUP BY customer_id) AS sales_summary
JOIN customers ON sales_summary.customer_id = customers.id
WHERE total_sales > 1000;

Sample Program

This query finds departments where the average salary is more than 60,000 by using a subquery in the FROM clause.

SQL

SELECT department, avg_salary
FROM (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) AS dept_avg
WHERE avg_salary > 60000;

Important Notes

Always give your subquery an alias, or the database will give an error.

Subqueries in FROM can be used anywhere a table name is expected.

Using subqueries here can make complex queries easier to read and maintain.

Summary

Subqueries in the FROM clause create temporary tables for use in the main query.

They help break complex queries into smaller, understandable parts.

Always remember to give the subquery an alias.