Scalar or column subqueries 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 (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1
WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
For both queries, if the table t2
contains a
single row with id = 10
, the subquery returns
a single row. If this row has col3
and
col4
values equal to the
col1
and col2
values of
any rows in t1
, the WHERE
expression is TRUE
and each query returns
those t1
rows. If the t2
row col3
and col4
values
are not equal the col1
and
col2
values of any t1
row,
the expression is FALSE
and the query returns
an empty result set. The expression is
unknown (that is, NULL
)
if the subquery produces no rows. An error occurs if the
subquery produces multiple rows because a row subquery can
return at most one row.
For information about how each operator works for row comparisons, see Section 14.4.2, “Comparison Functions and Operators”.
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.
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, so a row constructor cannot be used with a subquery that does not return at least two columns. Thus, the following query fails with a syntax error:
SELECT * FROM t1 WHERE ROW(1) = (SELECT column1 FROM t2)
Row constructors are legal in other contexts. For example, the following two statements are semantically equivalent (and are handled in the same way by the optimizer):
SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
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);
For more information about the optimizer and row constructors, see Section 10.2.1.22, “Row Constructor Expression Optimization”