Movatterモバイル変換

Scalar or column subqueries return a single value or a column of values. Arow 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 tablet2 contains a single row withid = 10, the subquery returns a single row. If this row hascol3 andcol4 values equal to thecol1 andcol2 values of any rows int1, theWHERE expression isTRUE and each query returns thoset1 rows. If thet2 rowcol3 andcol4 values are not equal thecol1 andcol2 values of anyt1 row, the expression isFALSE and the query returns an empty result set. The expression isunknown (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, seeSection 14.4.2, “Comparison Functions and Operators”.

The expressions(1,2) andROW(1,2) are sometimes calledrow 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 tablet1 that also exist in tablet2”:

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, seeSection 10.2.1.22, “Row Constructor Expression Optimization”