Beginner

682. JOIN with Derived Table

Understand how a derived table can be used as one side of a JOIN.

📘 Lesson 682 of 728 ⏱ 14 min 🎯 Intermediate

A derived table is a query result defined inside the FROM clause and treated like a temporary table for that query.

Derived tables allow intermediate calculations to be separated from the main query.

Calculate department salary totals and JOIN them with department names.

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

Used in analytical SQL, MIS reports, dashboards, and complex transformations.

  • A derived table exists only for the duration of the query.
  • It must normally have an alias.
  • It can contain GROUP BY and other query logic.
  • It can be JOINed like a regular table.
  • CTEs can provide an alternative structure.
What is a derived table?

It is a query result defined in the FROM clause and used like a temporary table.

Does a derived table permanently exist?

No. It exists only during execution of the query.

Have you finished this lesson?

Introduction to SQL – All Lessons