Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.4Mb
PDF (A4) - 37.4Mb
PDF (RPM) - 37.0Mb
EPUB - 10.6Mb
HTML Download (TGZ) - 10.3Mb
HTML Download (Zip) - 10.4Mb
HTML Download (RPM) - 9.0Mb
Eclipse Doc Plugin (TGZ) - 11.2Mb
Eclipse Doc Plugin (Zip) - 13.4Mb
Man Pages (TGZ) - 204.7Kb
Man Pages (Zip) - 311.8Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Comparisons Using Subqueries 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:

  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:


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, “Row Subqueries”.

User Comments
Sign Up Login You must be logged in to post a comment.