## 13.2.11.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: 

... WHERE ’a’ = (SELECT column1 FROM t1) 

MySQL also permits this construct: 

non_subquery_operand  LIKE ( subquery ) 

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 rows in table t1  for which the column1  value is equal to a maximum value in table t2 : 

SELECT * 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 of the subquery to a scalar, the subquery must return a scalar. For a comparison of the subquery to a row constructor, the subquery must be a row subquery that returns a row with the same number of values as the row constructor. See Section 13.2.11.5, “Row Subqueries” . 

## 13.2.11.3 Subqueries with ANY, IN, or SOME 

Syntax: 

operand comparison_operator  ANY ( subquery ) operand  IN ( subquery ) operand comparison_operator  SOME ( subquery ) 

Where comparison_operator  is one of these operators: 

= > < >= <= <> != 

The ANY  keyword, which must follow a comparison operator, means “return TRUE  if the comparison is TRUE  for ANY  of the values in the column that the subquery returns.”  For example: 

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2); 

Suppose that there is a row in table t1  containing (10) . The expression is TRUE  if table t2  contains (21,14,7)  because there is a value 7  in t2  that is less than 10 . The expression is FALSE  if table t2 contains (20,10) , or if table t2  is empty. The expression is unknown  (that is, NULL ) if table t2  contains (NULL,NULL,NULL) . 

