(mysql.info) comparisons-using-subqueries

Info Catalog

(mysql.info) scalar-subqueries

(mysql.info) subqueries

(mysql.info) any-in-some-subqueries

 
 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  row-subqueries.
 

Info Catalog

(mysql.info) scalar-subqueries

(mysql.info) subqueries

(mysql.info) any-in-some-subqueries