Subqueries The most common use of a subquery is in the form: Where  comparison_operator  is one of these operators: For example: MySQL also permits this construct: 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 : 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: 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” . Syntax: 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: 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) . 2685 13.2.11.3 Subqueries with ANY, IN, or SOME 13.2.11.2 Comparisons Using Subqueries non_subquery_operand comparison_operator  ( subquery ) = > < >= <= <> != <=> ... WHERE ’a’ = (SELECT column1 FROM t1) non_subquery_operand  LIKE ( subquery ) SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2); = > < >= <= <> != operand comparison_operator  ANY ( subquery ) operand  IN ( subquery ) operand comparison_operator  SOME ( subquery ) SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2); SELECT * FROM t1 AS t WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);