Writing Subqueries in SQL

A query inside a query — used whenever the answer to your question depends on another question first.

What & Why

A subquery is a complete SQL query nested inside another query, wrapped in parentheses. The database runs the inner query first, then uses its result to help answer the outer one.

Subqueries can appear in three places: inside WHERE (to filter), inside SELECT (to compute a value), or inside FROM (as a stand-in table). This lesson series covers all three.

See How It Works

BUSINESS QUESTION

Marketing wants campaigns whose spend is above the overall campaign 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	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	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
  name,
  channel,
  spend
FROM marketing.campaigns
WHERE spend > (
  SELECT AVG(spend)
  FROM marketing.campaigns
)
ORDER BY spend DESC, name;

RESULT — campaigns above the 52,500 average

name	channel	spend
Enterprise Search	google_ads	60000.00
Spring Launch	google_ads	55000.00

The scalar subquery returns 52,500 before the outer query filters campaign rows.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice