Beginner

476. JOIN with Subquery

Learn how a subquery can be used as a data source in a JOIN.

📘 Lesson 476 of 728 ⏱ 13 min 🎯 Intermediate

A subquery can return a temporary result set that can be treated like a table and joined with another table.

Subqueries can simplify complex analysis by preparing or aggregating data before it is joined to another dataset.

Calculate total sales per customer first, then join those totals to the customer table.

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

Useful for reporting, customer analysis, pre-aggregation, and complex business calculations.

  • A subquery can act as a derived table.
  • The derived result can be joined.
  • Pre-aggregation can simplify the main query.
  • The subquery normally requires an alias.
  • Complex logic can be separated into stages.
Can a subquery be used in a JOIN?

Yes. A subquery can produce a derived table that can be joined to another table.

Why use a subquery before a JOIN?

It can prepare, filter, or aggregate data before the final JOIN.

Have you finished this lesson?

Introduction to SQL – All Lessons