SQL Subquery Basics

SQL Subquery Basics helps analysts nest one SELECT inside another query and use its result as an input.

What & Why

Every subquery follows three mechanical rules: it's wrapped in parentheses, it runs to completion before the outer query uses its result, and it returns one of three shapes — a single value, a single column of many rows, or a whole table — which determines where it's allowed to appear.

See How It Works

BUSINESS QUESTION

Marketing wants campaigns whose spend is above 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	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
  id AS campaign_id,
  name,
  spend
FROM marketing.campaigns
WHERE spend > (SELECT AVG(spend) FROM marketing.campaigns)
ORDER BY spend DESC, campaign_id;

RESULT — campaigns above average

campaign_id	name	spend
4	Enterprise Search	60000.00
1	Spring Launch	55000.00

The average of the four representative campaign spends is 52,500.

Advanced business practice