CTE vs subquery

Compare CTE and subquery.

CTE vs subquery

A CTE is a readable form of a subquery. It breaks complex subqueries into parts.

-- Alt sorgu
SELECT * FROM (SELECT * FROM customers WHERE status = 'active') sub;
-- CTE
WITH active AS (SELECT * FROM customers WHERE status = 'active')
SELECT * FROM active;

Both forms give the same result.

All columns are selected from (SELECT * FROM customers WHERE status ….

CTE vs subquery — details

A CTE is more readable. You can reuse the same CTE multiple times; in a subquery this is hard.

Performance is usually the same in most databases; the difference is for the person reading and maintaining the query. Named steps instead of deeply nested subqueries make bugs easier to find.

Exercise CTE vs subquery

Convert a subquery to a CTE: WITH active AS (SELECT ...).

Show example solution

WITH active AS (SELECT * FROM customers WHERE status = 'active')
SELECT * FROM active;

Test yourself

What advantage does a CTE have over a subquery?
- Speed
- Memory
- Security
- Readability
Show answer

Readability — A CTE is more readable.

Write CTE

____ active AS (SELECT * FROM customers WHERE status = 'active')
SELECT * FROM active;

Show answer

WITH active AS (SELECT * FROM customers WHERE status = 'active')
SELECT * FROM active;

CTE vs subquery: match the terms

CTE

readable
subquery

nested
reuse

multiple
clean

organized

Next lesson: CTE with JOIN

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies