13.2.8.2. Comparisons Using Subqueries
The most common use of a subquery is in the form:
non_subquery_operand comparison_operator (subquery)
Where comparison_operator is one of
these operators:
= > < >= <= <>
For example:
... 'a' = (SELECT column1 FROM t1)
At one time the only legal place for a subquery was on the
right side of a comparison, and you might still find some old
DBMSs that insist on this.
Here is an example of a common-form subquery comparison that
you cannot do with a join. It finds all the values in table
t1 that are equal to a maximum value in
table t2:
SELECT column1 FROM t1
WHERE column1 = (SELECT MAX(column2) FROM t2);
Here is another example, which again is impossible with a join
because it involves aggregating for one of the tables. It
finds all rows in table t1 containing a
value that occurs twice in a given column:
SELECT * FROM t1 AS t
WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);
For a comparison performed with one of these operators, the
subquery must return a scalar, with the exception that
= can be used with row subqueries. See
Section 13.2.8.5, “Row Subqueries”.
