Beginner

518. JOIN with Derived Table

Understand how a subquery in the FROM clause can be treated as a temporary table and joined with other data.

📘 Lesson 518 of 728 ⏱ 13 min 🎯 Advanced

A derived table is a subquery placed in the FROM clause that produces a temporary result set which can be referenced like a table within the outer query.

Derived tables help break complex logic into manageable stages.

Calculate department salary totals first and then join the results to department details.

SQL 
SELECT
    d.DepartmentName,
    s.TotalSalary
FROM Departments d
INNER JOIN (
    SELECT
        DepartmentID,
        SUM(Salary) AS TotalSalary
    FROM Employees
    GROUP BY DepartmentID
) s
    ON d.DepartmentID = s.DepartmentID;

Used in reporting, data transformation, ranking, aggregation, and complex analytical queries.

  • A derived table is created in the FROM clause.
  • It must normally have an alias.
  • It can contain aggregation and filtering.
  • It can be joined like a regular table.
  • It helps organize complex SQL logic.
What is a derived table?

A derived table is a subquery in the FROM clause that acts as a temporary result set for the outer query.

Why use a derived table?

It can separate complex calculations into a logical intermediate result that can then be joined or filtered.

Have you finished this lesson?

Introduction to SQL – All Lessons