Subqueries & CTEs

1. What is a Correlated Subquery and how does it differ from a regular subquery?

A regular subquery runs once and doesn’t depend on the outer query. A correlated subquery depends on the outer query and runs once for every row.

Regular subquery: The inner query runs first, once. Its result is then used by the outer query.

SELECT name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'IT');

The inner query finds the IT department’s ID once. Then the outer query uses it.

Correlated subquery: The inner query references a column from the outer query.

Because of that, it must run again for every row of the outer query.

Example — find employees who earn more than their department’s average:

SELECT name, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

For every employee, the inner query recomputes the average salary of that employee’s department.

If there are 1000 employees, the inner query runs up to 1000 times.

Key differences table:

	Regular subquery	Correlated subquery
Depends on outer query	No	Yes
Runs how many times	Once	Once per row
Speed	Faster	Slower
Typical use	Lookup a fixed value	Compare each row with a related value

Key takeaway: Correlated subqueries are powerful but slow. Use a JOIN or a window function when performance matters.

2. When would you use a Subquery instead of a JOIN?

Use a subquery when you need to filter or aggregate data before it’s combined, or when you need a value to compare against. Use a JOIN when you want to combine rows from multiple tables side by side.

When a subquery is better:

1. When you need one single value:

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

A subquery cleanly provides the average to compare against. A JOIN can’t easily do this.

2. When you need to aggregate before joining:

SELECT d.department_name, t.total
FROM departments d
JOIN (
  SELECT department_id, COUNT(*) AS total
  FROM employees
  GROUP BY department_id
) t ON d.department_id = t.department_id;

The subquery builds a small summary first, then joins it.

When a JOIN is better: Use a JOIN when you need columns from both tables side by side.

Joins are usually faster and easier to read for that case.

Key differences table:

	Subquery	JOIN
Returns a value to compare	Yes, easily	Awkward
Combines rows side by side	Possible but clunky	Yes
Usually faster	No	Yes
Readability for complex logic	Good	Good

Key takeaway: Use a subquery to compute a value or pre-aggregate. Use a JOIN to combine rows. Many queries can use either — pick the clearer one.

3. What is a Common Table Expression (CTE) and why use it over a subquery?

A CTE is a named, temporary result set that exists only for the duration of a query. It’s easier to read than a subquery and supports recursion, which a normal subquery can’t do.

What a CTE looks like:

WITH it_staff AS (
  SELECT * FROM employees WHERE department_id = 5
)
SELECT * FROM it_staff WHERE salary > 50000;

WITH gives a name to a sub-query. Then the main query uses that name.

Why use a CTE over a subquery:

1. Better readability: You can name the intermediate result. The main query reads like a sentence instead of nested parentheses.

2. Reuse the same result multiple times:

WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT * FROM high_earners
UNION
SELECT * FROM high_earners WHERE department_id = 5;

high_earners is used twice. With a subquery, you’d have to repeat the whole query.

3. Recursion: A CTE can call itself, which lets you build things like a manager chain.

WITH RECURSIVE chain AS (
  SELECT employee_id, manager_id FROM employees WHERE employee_id = 1
  UNION ALL
  SELECT e.employee_id, e.manager_id
  FROM employees e
  JOIN chain c ON e.manager_id = c.employee_id
)
SELECT * FROM chain;

A normal subquery cannot do this.

Key differences table:

	CTE	Subquery
Named	Yes	No
Can reuse result	Yes	No (repeat it)
Supports recursion	Yes	No
Readability	Better	Can get nested

Key takeaway: Use a CTE when a query is complex, when you reuse the same result, or when you need recursion. For simple one-off lookups, a subquery is fine.

4. How do you write a Recursive CTE?

A recursive CTE has two parts: an anchor member (the starting point) and a recursive member (which references the CTE itself), joined by UNION ALL.

The structure:

WITH RECURSIVE name AS (
  -- anchor member: starting rows
  SELECT ...
  UNION ALL
  -- recursive member: refers to the CTE
  SELECT ... FROM name WHERE ...
)
SELECT * FROM name;

Simple example — counting from 1 to 5:

WITH RECURSIVE numbers (n) AS (
  SELECT 1                      -- anchor
  UNION ALL
  SELECT n + 1 FROM numbers
  WHERE n < 5                   -- termination condition
)
SELECT * FROM numbers;

Result:

n
1
2
3
4
5

How it runs step by step:

1. The anchor member returns 1.
2. The recursive member takes 1, adds 1, and returns 2.
3. This repeats until n < 5 is false.
4. All the rows are combined with UNION ALL.

Practical example — finding a manager chain: Employees table:

employee_id	name	manager_id
1	Ali	(none)
2	Bob	1
3	Cam	2

WITH RECURSIVE chain AS (
  SELECT employee_id, name, manager_id FROM employees WHERE employee_id = 3
  UNION ALL
  SELECT e.employee_id, e.name, e.manager_id
  FROM employees e
  JOIN chain c ON e.employee_id = c.manager_id
)
SELECT * FROM chain;

This walks from Cam up to Ali.

Key takeaway: Always include an anchor, a recursive member, and a termination condition. Without the termination condition, the query runs forever.

5. What is the difference between ‘ANY’ and ‘ALL’ operators in SQL subqueries?

ANY returns TRUE if the condition holds for at least one value from the subquery. ALL returns TRUE only if it holds for every value.

ANY — at least one:

SELECT name FROM employees
WHERE salary > ANY (SELECT salary FROM managers);

Returns employees earning more than at least one manager.

ALL — every single one:

SELECT name FROM employees
WHERE salary > ALL (SELECT salary FROM managers);

Returns employees earning more than every manager.

The difference in one example: If managers earn 100, 200, 300:

• > ANY → TRUE if the employee earns more than 100.
• > ALL → TRUE only if the employee earns more than 300.

Key takeaway: ANY = at least one value. ALL = every value. Choose based on whether one match is enough or all must match.