Course contentsShow
← Data Science
Lesson 972 of 2,14525. SQL Subqueries and CTEsFree lesson

Multiple Subqueries in FROM with Joins

Combine multiple derived tables using joins to create complex queries from intermediate results.

Multiple Subqueries in FROM with Joins

You'll learn: How to combine multiple derived tables (subqueries in the FROM clause) using joins to build sophisticated queries from intermediate results.

The Core Idea

Think of this as a multi-step assembly line: each subquery produces its own temporary result set, and then you join these "mini-tables" together just like you would regular tables. This lets you break complex problems into manageable pieces, compute different aggregations or filters independently, and then unite them.

Instead of trying to solve everything in one massive query, you create multiple derived tables in the FROM clause and join them on common keys. Each subquery acts as an independent calculation that you can reference and combine.

How It Works

You place multiple subqueries in the FROM clause, give each an alias, and join them using any join type you've learned (INNER, LEFT, RIGHT, etc.). The join condition connects them using columns they share.

SELECT 
    a.category,
    a.total_sales,
    b.total_orders,
    a.total_sales / b.total_orders AS avg_order_value
FROM 
    (SELECT category, SUM(amount) AS total_sales
     FROM sales
     GROUP BY category) AS a
INNER JOIN
    (SELECT category, COUNT(*) AS total_orders
     FROM orders
     GROUP BY category) AS b
ON a.category = b.category;

Here, one subquery calculates total sales per category, another counts orders per category, and we join them to compute average order value—a metric requiring data from both calculations.

Key Takeaway: Multiple subqueries in FROM with joins let you decompose complex analytical questions into separate, manageable calculations, then stitch the results together using familiar join syntax to create powerful composite views of your data.