Correlated Subqueries
A correlated subquery is a subquery which contains a reference to a column which is also in the outer query. For example:
SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);
Notice, in the example, that the subquery contains a reference to a column of t1, even though the subquery's FROM clause doesn't mention a table t1. So MariaDB looks outside the subquery, and finds t1 in the outer query.
Suppose that table t1 contains a row where column1 = 5 and column2 = 6; meanwhile table t2 contains a row where column1 = 5 and column2 = 7. The simple expression ... WHERE column1 = ANY (SELECT column1 FROM t2) would be TRUE, but in this example the WHERE clause within the subquery is FALSE (because 7 <> 5), so the subquery as a whole is FALSE.
Scoping rule: MariaDB evaluates from inside to outside. For example:
SELECT column1 FROM t1 AS x WHERE x.column1 = (SELECT column1 FROM t2 AS x WHERE x.column1 = (SELECT column1 FROM t3 WHERE x.column2 = t3.column1));
In the above, x.column2 must be a column in table t2 because SELECT column1 FROM t2 AS x ... renames t2. It is not a column in table t1 because SELECT column1 FROM t1 ... is an outer query which is further out.
For subqueries in HAVING or ORDER BY clauses, MariaDB also looks for column names in the outer select list.
MySQL's unofficial recommendation is: avoid correlation because it makes your queries look more complex, and run more slowly.