Questions: SQL Subqueries and CTEs

Question 1

You run: SELECT * FROM employees WHERE id NOT IN (SELECT manager_id FROM departments). You expect some results, but get zero rows, even though many employees are not managers. What is the most likely cause?

• A. NOT IN cannot be used with subqueries — you must use a JOIN instead
• B. The departments table has at least one NULL in the manager_id column, causing NOT IN to return no rows
• C. The subquery returns too many rows, causing a performance timeout that returns no results
• D. NOT IN requires the subquery to be aliased with AS

Answer: (B)

This is the NULL trap in NOT IN. SQL uses three-valued logic: comparisons with NULL produce UNKNOWN (neither TRUE nor FALSE). When the subquery returns any NULL, the condition `id NOT IN (...)` includes a comparison `id != NULL` which evaluates to UNKNOWN. Since UNKNOWN is not TRUE, the row is excluded — effectively making NOT IN return no results when NULLs are present. The fix is NOT EXISTS: `WHERE NOT EXISTS (SELECT 1 FROM departments WHERE manager_id = e.id)`. NOT EXISTS tests set membership through a correlated subquery, which is NULL-safe.

Question 2

What distinguishes a correlated subquery from a non-correlated subquery?

• A. A correlated subquery appears in the FROM clause; a non-correlated subquery appears in the WHERE clause
• B. A correlated subquery references a column from the outer query and logically re-executes for each outer row; a non-correlated subquery executes once
• C. A correlated subquery always executes faster because the optimizer caches its result per outer row
• D. A correlated subquery is only valid inside a CTE; non-correlated subqueries can appear anywhere

Answer: (B)

A correlated subquery contains a reference to a column from the outer query (e.g., `WHERE department = e.department`), making it dependent on the current outer row. Logically, it re-executes for each outer row — O(n) executions. A non-correlated subquery has no outer reference, executes once, and its result is reused. In practice, modern optimizers often rewrite correlated subqueries as joins, so actual performance may be similar — but the logical distinction governs behavior when the optimizer cannot rewrite.

Question 3

A subquery used as a derived table in the FROM clause must be given an alias.

• T. True
• F. False

Answer: True

In standard SQL, a subquery in the FROM clause (derived table) must be aliased so the outer query can reference it by name. For example: `SELECT * FROM (SELECT id, salary FROM employees) AS emp_data WHERE salary > 50000`. Without the alias `AS emp_data`, most databases throw a syntax error. The alias gives the temporary result set a name that can be used in the outer query's SELECT, WHERE, and JOIN clauses.

Question 4

A correlated subquery typically executes once per outer row, making it slower than an equivalent JOIN regardless of the database optimizer used.

• T. True
• F. False

Answer: False

Modern query optimizers frequently detect correlated subqueries and rewrite them internally as joins. The *logical* execution model is once per outer row, but the *physical* execution plan often differs significantly. The SQL standard defines semantics, not execution strategy. That said, you should check execution plans when performance matters — the optimizer may not always rewrite, and a correlated subquery touching a large table without an index can genuinely perform poorly. Write for clarity first, optimize after profiling.

Question 5

Why should you use NOT EXISTS rather than NOT IN when your subquery might return NULL values?

Model answer: SQL's three-valued logic means any comparison with NULL produces UNKNOWN. In NOT IN, if the subquery returns any NULL, the expression `value NOT IN (..., NULL)` includes `value != NULL`, which is UNKNOWN — causing the entire condition to be UNKNOWN, treated as FALSE, so the row is filtered out. NOT EXISTS tests whether the correlated subquery returns any matching rows at all (a boolean existence check), which is unaffected by NULLs in the data. NOT EXISTS returns TRUE when no matching rows exist, correctly including the outer row in the result.

The practical impact is significant: a query using NOT IN to exclude employees who aren't managers will silently return zero results if any department has a NULL manager_id — a hard-to-debug failure. NOT EXISTS avoids this entirely. This is one of SQL's most notorious NULL-related gotchas, and understanding it requires knowing that SQL's three-value logic (TRUE/FALSE/UNKNOWN) differs from ordinary Boolean logic. When the subquery result is guaranteed to never contain NULLs (e.g., from a NOT NULL column), NOT IN is safe — but NOT EXISTS is always safe.