What are derived tables?

Q: What are derived tables?

SELECT * FROM ( SELECT column1, column2 FROM some_table WHERE condition ) AS derived_table_name; 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. SELECT * FROM ( SELECT department_id, employee_name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM employees ) AS ranked_employees WHERE salary_rank <= 3; // The subquery is a derived table (ranked_employees). // You rank employees by salary within each department. // Then you filter to get the top 3 per department. 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.

Difficulty: 5/10

subqueries, performance optimization, query design

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.

Scenario Questions

0-2 years experience

2-5 years experience

1You added a derived table to a report query, but the performance dropped dramatically. How would you investigate and what alternatives might you consider?

Follow-up Questions