Database: SELECT: Correlated Subqueries
Subqueries often are not entirely self-contained and may reference
fields in the outer row that is being examined in a
Column ambiguity resolution
When interpreting column references in the inner subselect, it first looks
at the tables that are in the subselect, then at the outer tables.
If the subselect needs to reference an outer column when it is also
used in the inner subselect, the outer table should have an alias
assigned to it, which is then used in the subselect.