Joe Celko's SQL for Smarties, 3rd Edition

16.1. Scalar Subquery Comparisons

Standard SQL allows both scalar and row comparisons, but most queries use only scalar expressions. If a subquery returns a single-row, single-column result table, it is treated as a scalar value in Standard SQL in virtually any place a scalar could appear. For example, to find out if we have any teachers who are more than one year older than the students, I could write:

SELECT T1.teacher_name
  FROM Teachers AS T1
 WHERE
   T1.birthday > (SELECT MAX(S1.birthday) - INTERVAL '365' DAY
                    FROM Students AS S1);

In this case, the scalar subquery will be run only once and reduced to a constant value by the optimizer before scanning the Teachers table.

A correlated subquery is more complex, because it will have to be executed ...