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 as well. 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 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);
