Subqueries In MySQL 8.0.19 and later, you can also use  NOT EXISTS  or  NOT EXISTS  with  TABLE  in the subquery, like this: SELECT column1 FROM t1 WHERE EXISTS (TABLE t2); The results are the same as when using  SELECT *  with no  WHERE  clause in the subquery. A  correlated subquery  is a subquery that contains a reference to a table that also appears in the outer query. For example: Notice that the subquery contains a reference to a column of  t1 , even though the subquery’s  FROM  clause does not mention a table  t1 . So, MySQL 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  (5,6)  is not equal to  (5,7) ), so the expression as a whole is  FALSE . Scoping rule:  MySQL evaluates from inside to outside. For example: In this statement,  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 that is  farther out . Beginning with MySQL 8.0.24, the optimizer can transform a correlated scalar subquery to a derived table when the  subquery_to_derived  flag of the  optimizer_switch  variable is enabled. Consider the query shown here: To avoid materializing several times for a given derived table, we can instead materialize—once—a derived table which adds a grouping on the join column from the table referenced in the inner query ( t2.a ) and then an outer join on the lifted predicate ( t1.a = derived.a ) in order to select the correct group to match up with the outer row. (If the subquery already has an explicit grouping, the extra grouping is added to the end of the grouping list.) The query previously shown can thus be rewritten like this: 2689 13.2.11.7 Correlated Subqueries SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2); 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)); SELECT * FROM t1  WHERE ( SELECT a FROM t2  WHERE t2.a=t1.a ) > 0; SELECT t1.* FROM t1  LEFT OUTER JOIN (SELECT a, COUNT(*) AS ct FROM t2 GROUP BY a) AS derived ON t1.a = derived.a  AND  REJECT_IF( (ct > 1), "ERROR 1242 (21000): Subquery returns more than 1 row" ) WHERE derived.a > 0;