9.22. 子查詢
This section describes theSQL-compliant subquery expressions available inPostgreSQL. All of the expression forms documented in this section return Boolean (true/false) results.
9.22.1. EXISTS
EXISTS
The argument ofEXISTS
is an arbitrarySELECT
statement, orsubquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result ofEXISTS
is“true”; if the subquery returns no rows, the result ofEXISTS
is“false”.
The subquery can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery.
The subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion. It is unwise to write a subquery that has side effects (such as calling sequence functions); whether the side effects occur might be unpredictable.
Since the result depends only on whether any rows are returned, and not on the contents of those rows, the output list of the subquery is normally unimportant. A common coding convention is to write allEXISTS
tests in the formEXISTS(SELECT 1 WHERE ...)
. There are exceptions to this rule however, such as subqueries that useINTERSECT
.
This simple example is like an inner join oncol2
, but it produces at most one output row for eachtab1
row, even if there are several matchingtab2
rows:
9.22.2. IN
IN
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result. The result ofIN
is“true”if any equal subquery row is found. The result is“false”if no equal row is found (including the case where the subquery returns no rows).
Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of theIN
construct will be null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values.
As withEXISTS
, it's unwise to assume that the subquery will be evaluated completely.
As usual, null values in the rows are combined per the normal rules of SQL Boolean expressions. Two rows are considered equal if all their corresponding members are non-null and equal; the rows are unequal if any corresponding members are non-null and unequal; otherwise the result of that row comparison is unknown (null). If all the per-row results are either unequal or null, with at least one null, then the result ofIN
is null.
9.22.3. NOT IN
NOT IN
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result. The result ofNOT IN
is“true”if only unequal subquery rows are found (including the case where the subquery returns no rows). The result is“false”if any equal row is found.
Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of theNOT IN
construct will be null, not true. This is in accordance with SQL's normal rules for Boolean combinations of null values.
As withEXISTS
, it's unwise to assume that the subquery will be evaluated completely.
As usual, null values in the rows are combined per the normal rules of SQL Boolean expressions. Two rows are considered equal if all their corresponding members are non-null and equal; the rows are unequal if any corresponding members are non-null and unequal; otherwise the result of that row comparison is unknown (null). If all the per-row results are either unequal or null, with at least one null, then the result ofNOT IN
is null.
9.22.4. ANY
/SOME
ANY
/SOME
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the givenoperator
, which must yield a Boolean result. The result ofANY
is“true”if any true result is obtained. The result is“false”if no true result is found (including the case where the subquery returns no rows).
SOME
is a synonym forANY
.IN
is equivalent to= ANY
.
Note that if there are no successes and at least one right-hand row yields null for the operator's result, the result of theANY
construct will be null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values.
As withEXISTS
, it's unwise to assume that the subquery will be evaluated completely.
9.22.5. ALL
ALL
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the givenoperator
, which must yield a Boolean result. The result ofALL
is“true”if all rows yield true (including the case where the subquery returns no rows). The result is“false”if any false result is found. The result is NULL if the comparison does not return false for any row, and it returns NULL for at least one row.
NOT IN
is equivalent to<> ALL
.
As withEXISTS
, it's unwise to assume that the subquery will be evaluated completely.
9.22.6. Single-row Comparison
Last updated
Was this helpful?