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. Legal operators for row subquery comparisons are:

=  >  <  >=  <=  <>  !=  <=>

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. The row constructor and the row returned by the subquery must contain the same number of values.

In MySQL 5.0, a row constructor is used for comparisons with subqueries that return two or more columns. When a subquery returns a single column, this is regarded as a scalar value and not as a row; a row constructor cannot be used with a subquery that does not return at least two columns. Thus, a query such as SELECT * FROM t1 WHERE ROW(1) = (SELECT column1 FROM t2) fails with a syntax error.

Row constructors are legal in other contexts. For example, the following two statements are semantically equivalent:

SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

Prior to MySQL 5.0.26, only the second of the preceding two expressions could be optimized. (Bug#16081)

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);