Subqueries & CTEs — quiz

A subquery referencing the outer query is:

1. Scalar
2. Correlated
3. Derived
4. Recursive

Answer: Correlated — Correlated subqueries are evaluated per outer row.

A correlated COUNT subquery in SELECT is usually better as:

1. A CTE
2. A LEFT JOIN with GROUP BY
3. A UNION
4. A window function only

Answer: A LEFT JOIN with GROUP BY — One aggregation pass beats one query per row.

Derived tables in FROM usually require:

1. An index
2. An alias
3. A GROUP BY
4. A WHERE clause

Answer: An alias — Most dialects reject an unaliased derived table.

Pre-aggregating before a join prevents:

1. Null values
2. Row multiplication
3. Slow sorting
4. Type errors

Answer: Row multiplication — One row per key means nothing fans out.

A recursive CTE requires:

1. An index
2. An anchor member, UNION ALL and a recursive member
3. A window function
4. A temporary table

Answer: An anchor member, UNION ALL and a recursive member — Plus something that guarantees termination.

The main advantage of a CTE over nested subqueries is:

1. Always faster
2. Readable top-to-bottom structure and reuse
3. Avoids indexes
4. Allows aggregates

Answer: Readable top-to-bottom structure and reuse — Performance depends on whether the engine inlines it.

Safest way to find customers with no orders:

1. NOT IN
2. NOT EXISTS
3. != ANY
4. CROSS JOIN

Answer: NOT EXISTS — NOT EXISTS is null-safe; NOT IN is not.

Why is SELECT 1 used inside EXISTS?

1. It is faster to parse
2. The column list is never evaluated
3. It returns a boolean
4. It is required syntax

Answer: The column list is never evaluated — EXISTS only cares whether a row exists, not what it contains.