SQL CTE vs Subquery

Often the same query plan under the hood — the difference is almost entirely for the human reading it.

What & Why

A CTE and an equivalent subquery frequently produce the identical result and the identical execution plan. The real difference: a CTE is named and can be reused more than once in the same query; a subquery is inline and read where it sits.

See How It Works

BUSINESS QUESTION

Compare named and inline channel-spend summaries that return the same rows.

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

Compare named and inline versions of the same result tableStep 1 of 2

WITH channel_spend AS (...) SELECT ... FROM channel_spend

channel	total_spend	CTE result
google_ads	115,000.00	KEEP
linkedin	50,000.00	KEEP
email	45,000.00	SKIP

The named channel_spend CTE exposes the three-row intermediate table before filtering.

EXAMPLE QUERY

-- Named intermediate result
WITH channel_spend AS (
  SELECT channel, SUM(spend) AS total_spend
  FROM marketing.campaigns
  GROUP BY channel
)
SELECT channel, total_spend
FROM channel_spend
WHERE total_spend >= 50000
ORDER BY total_spend DESC;

-- Equivalent inline intermediate result
SELECT channel, total_spend
FROM (
  SELECT channel, SUM(spend) AS total_spend
  FROM marketing.campaigns
  GROUP BY channel
) AS channel_spend
WHERE total_spend >= 50000
ORDER BY total_spend DESC;

This lesson's practice is part of Pro.

See what's included →

Advanced business practice