Subqueries & CTEs in SQL

From the SQL cheat sheet · Subqueries & CTEs · verified Jul 2026

Subqueries & CTEs

Write nested queries and reusable named expressions.

sql

-- Subquery in WHERE
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- EXISTS
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- CTE (Common Table Expression)
WITH active_users AS (
  SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE age > 25;

💡 CTEs make complex queries readable — define named blocks with WITH, then reference them

⚡ EXISTS is often faster than IN for large subqueries — it stops at the first match

📌 Correlated subqueries run once per outer row — they can be slow on large tables

🟢 Recursive CTEs are the standard way to query hierarchical data (org charts, categories)

subqueriescteexistsrecursive

Back to the full SQL cheat sheet