Beginner

431. JOIN with Subquery

Learn how a subquery can be used as part of a JOIN to combine detailed and calculated data.

📘 Lesson 431 of 728 ⏱ 12 min 🎯 Intermediate

A JOIN with a subquery uses the result of an inner query as a temporary dataset that can be joined with another table.

Subqueries allow complex calculations or filtering to be performed before the final tables are combined.

Calculate total sales per customer in a subquery and join that result with the Customers table.

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;

This approach is useful for customer analytics, summary reporting, performance analysis, and complex business calculations.

  • A subquery can produce a temporary result set.
  • The result can be joined like a table.
  • Subqueries can perform calculations before the JOIN.
  • Aliases are required for derived tables in many SQL systems.
  • This technique is useful for complex analysis.
Can a subquery be used in a JOIN?

Yes. A subquery can produce a derived result set that can be joined with another table.

Why use a subquery inside a JOIN?

It allows filtering, grouping, or calculations to be completed before the final join.

Have you finished this lesson?

Introduction to SQL – All Lessons