(mysql.info.gz) Row subqueries

Info Catalog

(mysql.info.gz) ALL subqueries

(mysql.info.gz) Subqueries

(mysql.info.gz) EXISTS and NOT EXISTS subqueries

 
 13.1.8.5 Row Subqueries
 .......................
 
 The discussion to this point has been of scalar or column subqueries,
 that is, subqueries that return a single value or a column of values.
 A _row subquery_ is a subquery variant that returns a single row and
 can thus return more than one column value.  Here are two examples:
 
      SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);
      SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);
 
 The queries here are both `TRUE' if table `t2' has a row where `column1
 = 1' and `column2 = 2'.
 
 The expressions `(1,2)' and `ROW(1,2)' are sometimes called _row
 constructors_. The two are equivalent.  They are legal in other
 contexts, too. For example, the following two statements are
 semantically equivalent (although currently only the second one can be
 optimized):
 
      SELECT * FROM t1 WHERE (column1,column2) = (1,1);
      SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
 
 The normal use of row constructors, though, is for comparisons with
 subqueries that return two or more columns. For example, the following
 query answers the request, "find all rows in table `t1' that also exist
 in table `t2'":
 
      SELECT column1,column2,column3
             FROM t1
             WHERE (column1,column2,column3) IN
                   (SELECT column1,column2,column3 FROM t2);
 

Info Catalog

(mysql.info.gz) ALL subqueries

(mysql.info.gz) Subqueries

(mysql.info.gz) EXISTS and NOT EXISTS subqueries