Row Subqueries

The discussion to this point has been of column (or scalar) subqueries -- subqueries which return a single column value. A row subquery is a subquery variant that returns a single row value -- and may 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 above are both TRUE if table t2 has a row where column1 = 1 and column2 = 2.

The expression (1,2) is sometimes called a row constructor and is legal in other contexts too. For example

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

is equivalent to

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, this query answers the request: find all rows in table t1 which are duplicated in table t2:

Retornar