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?
Simplify complex queries by breaking them into manageable parts.
Perform aggregations or transformations and reuse the result.
Filter or order data in the subquery before applying further logic
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:
Derived tables must have an alias (AS derived_table_name).
You can't refer to a derived table more than once unless it's a Common Table Expression (CTE) or temporary table.
They exist only during the execution of the query.