Derived Tables in MySQL

Introduction

In MySQL, sometimes you need to use the result of a query as a temporary table within another query.

This is called a Derived Table.

Derived tables are useful for handling complex queries, aggregations, and intermediate results.

What is a Derived Table

A Derived Table is a subquery used inside the FROM clause.

It acts like a temporary table that exists only during the execution of the query.

Basic Syntax

SELECT column_names FROM ( SELECT column_names FROM table_name ) AS alias_name; 

Important:
A derived table must have an alias.

Example

SELECT * FROM ( SELECT name, age FROM Students WHERE age > 18 ) AS Adult Students; 

This creates a temporary table, AdultStudents and selects data from it.

Example with Aggregation

SELECT city, avg_age FROM ( SELECT city, AVG(age) AS avg_age FROM Students GROUP BY city ) AS CityAvg; 

This calculates the average age per city using a derived table.

Why Use Derived Tables

Derived tables are useful when:

• You need intermediate results.

• You want to simplify complex queries.

• You are working with grouped or aggregated data.

• You want to reuse query results.

Important Rules

• Must include an alias

• Exists only during query execution

• Cannot be indexed

• Used mainly in SELECT queries

Example Scenario

Suppose you want students older than the average age:

SELECT name FROM Students WHERE age > ( SELECT AVG(age) FROM Students ); 

Using derived table:

SELECT name FROM ( SELECT name, age FROM Students ) AS temp WHERE age > (SELECT AVG(age) FROM Students); 

Derived Table vs Temporary Table

Common Mistakes

• Forgetting alias

• Writing complex queries without clarity

• Confusing derived tables with temporary tables

Key Points to Remember

• Derived tables are subqueries in the FROM clause.

• Act like temporary tables during query execution.

• Must have an alias

• Useful for complex queries and aggregations