Subqueries in FROM clause (derived tables) in MySQL

Introduction

Subqueries in the FROM clause let you use the result of one query as a temporary table inside another query. This helps break complex problems into smaller parts.

When you want to summarize data first, then use that summary in another query.

When you need to join a query result with another table.

When filtering or sorting depends on calculations done in a subquery.

When you want to organize your query for better readability by separating steps.

Syntax

MySQL

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

The subquery inside FROM must have an alias (a temporary name).

The outer query treats the subquery result like a regular table.

Examples

Find departments with average salary above 50000 by first calculating averages in the subquery.

MySQL

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

Join departments with their total sales calculated in the subquery.

MySQL

SELECT d.department_name, s.total_sales
FROM departments d
JOIN (SELECT department_id, SUM(sales) AS total_sales FROM sales GROUP BY department_id) AS s
ON d.department_id = s.department_id;

Sample Program

This query finds customers who placed more than 3 orders by counting orders per customer in a subquery, then joining with the customers table to get names.

MySQL

SELECT name, total_orders
FROM (SELECT customer_id, COUNT(*) AS total_orders FROM orders GROUP BY customer_id) AS order_counts
JOIN customers ON order_counts.customer_id = customers.id
WHERE total_orders > 3;

Important Notes

Always give an alias to the subquery in the FROM clause, or MySQL will give an error.

Subqueries in FROM can be nested, but keep queries simple to avoid confusion.

Use derived tables to improve query clarity and break down complex logic.

Summary

Subqueries in FROM act like temporary tables for the outer query.

They help organize complex queries by separating steps.

Always provide an alias for the subquery result.