SQL Subquery in FROM
A subquery that acts as a temporary table — the outer query just selects from it.
What & Why
A subquery in FROM — sometimes called a derived table — produces a full result set that the outer query treats like any other table. It must be given an alias.
See How It Works
BUSINESS QUESTION
Marketing wants channel summaries whose average spend exceeds the overall average.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
EXAMPLE QUERY
SELECT
summary.channel,
summary.avg_spend
FROM (
SELECT channel, ROUND(AVG(spend), 2) AS avg_spend
FROM marketing.campaigns
GROUP BY channel
) AS summary
WHERE summary.avg_spend > (SELECT AVG(spend) FROM marketing.campaigns)
ORDER BY summary.avg_spend DESC, summary.channel;RESULT — channel summaries above the overall average
| channel | avg_spend |
|---|---|
| google_ads | 57500.00 |
Only the google_ads channel average exceeds the overall 52,500 campaign average.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario