CTE vs subquery
- SQL course
- Advanced
- Lesson 4/81
- 4 min
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.
Practice CTE vs subquery
Convert a subquery to a CTE: WITH active AS (SELECT ...).
Starter code
-- convert to CTE
WITHSample solution
WITH active AS (SELECT * FROM customers WHERE status = 'active')
SELECT * FROM active;Expected output
id name status 1 Ayşe active
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What advantage does a CTE have over a subquery?
- Speed
- Memory
- Security
- Readability Correct answer
Explanation: A CTE is more readable.
2 Write CTE
____ active AS (SELECT * FROM customers WHERE status = 'active')
SELECT * FROM active;WITH active AS (SELECT * FROM customers WHERE status = 'active')
SELECT * FROM active;Explanation: Every CTE is defined with WITH; the SELECT after it uses the name like a table.
3 CTE vs subquery: match the terms
| Term | Meaning |
|---|---|
CTE |
readable |
subquery |
nested |
reuse |
multiple |
clean |
organized |