Beginner

611. JOIN with Subquery

Learn how a subquery can be used as a temporary result and then JOINed with another table.

📘 Lesson 611 of 728 ⏱ 14 min 🎯 Advanced

A subquery can produce a result set that is treated like a derived table. That result can then be JOINed with other tables.

Complex business logic can be separated into smaller steps, making analytical queries easier to build and understand.

Calculate total sales per customer and then JOIN the totals with customer information.

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, customer analytics, financial summaries, and complex data transformations.

  • A subquery can act as a derived table.
  • The derived result can be JOINed normally.
  • Aggregation can be performed before the JOIN.
  • Aliases are required for derived tables in many SQL systems.
  • This pattern can simplify complex reporting queries.
Can a subquery be used in a JOIN?

Yes. A subquery can produce a derived table that can be JOINed with another table.

Why aggregate inside a subquery before JOINing?

It can reduce the data to the required level before combining it with the main table.

Have you finished this lesson?

Introduction to SQL – All Lessons