Correlated Subquery

The NULL issues discussed in the previous entry on subquery can be side-stepped with correlated subqueries. Correlated subqueries are different to subqueries in that for each outer row, the query tries to find a matching inner row. If such an inner row exists, then the outer row is returned. For example,

select *
from t1
where exists
(select *
from t2
where t1.a_col = t2.a_col);

The above query can be optimized as follows:

select *
from t1
where exists
(select '1'
from t2
where t1.a_col = t2.a_col);

Notice that the inner query projects the value '1' if there is a match instead of the whole row, which could have dozens of columns.

What happens if the WHERE clause has NOT EXISTS instead of EXISTS, and the inner query returns NULL? In that case, the outer query returns nothing, which is the expected behavior. Presto, the perils of NULLs are nullified!