What are derived tables?

Derived tables in MySQL are temporary result sets created by a subquery in the FROM clause of a SQL query. Think of them as virtual tables generated on-the-fly for use within the main query.

Syntax:

What are they used for?

1
Simplify complex queries by breaking them into manageable parts.
2
Perform aggregations or transformations and reuse the result.
3
Filter or order data in the subquery before applying further logic
4
Avoid repeating subqueries by computing once and referring to it.

Suppose you want to find the top 3 highest-paid employees in each department.

Points to remember:

1
Derived tables must have an alias (AS derived_table_name).
2
You can't refer to a derived table more than once unless it's a Common Table Expression (CTE) or temporary table.
3
They exist only during the execution of the query.