Beginner

681. JOIN with Subquery

Learn how to JOIN a table with the result produced by a subquery.

📘 Lesson 681 of 728 ⏱ 14 min 🎯 Intermediate

A subquery can produce a temporary result set that is then JOINed with another table.

Subqueries can simplify complex calculations before combining the result with detailed data.

Calculate customer sales totals and JOIN them with customer details.

SQL 
SELECT
    c.CustomerName,
    s.TotalSales
FROM Customers c
INNER JOIN (
    SELECT
        CustomerID,
        SUM(OrderAmount) AS TotalSales
    FROM Orders
    GROUP BY CustomerID
) s
    ON c.CustomerID = s.CustomerID;

Used in reporting, analytics, dashboards, and financial summaries.

  • A subquery can act as a derived table.
  • The subquery must expose columns used by the JOIN.
  • Aggregation can be performed before the JOIN.
  • The result can be easier to control at a specific grain.
  • A CTE can sometimes improve readability.
Can a subquery be JOINed?

Yes. A subquery in the FROM clause can produce a derived table that can be JOINed.

Why aggregate before JOINing?

Pre-aggregation can prevent unnecessary row multiplication and produce one row per required business key.

Have you finished this lesson?

Introduction to SQL – All Lessons