14th November 2019: PostgreSQL 12.1, 11.6, 10.11, 9.6.16, 9.5.20, and 9.4.25 Released!

Development Versions:
devel

This documentation is for an unsupported version of PostgreSQL.

You may want to view the same page for the current version, or one of the supported versions listed above instead.

You may want to view the same page for the current version, or one of the supported versions listed above instead.

This section describes the SQL-compliant subquery expressions available in PostgreSQL. All of the expression forms documented in this section return Boolean (true/false) results.

EXISTS (subquery)

The argument of EXISTS is an
arbitrary SELECT statement, or *subquery*.
The subquery is evaluated to determine whether it returns any
rows. If it returns at least one row, the result of EXISTS is TRUE; if the subquery returns no rows,
the result of EXISTS 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 far 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 any side-effects (such as calling sequence functions); whether the side-effects occur or not may be difficult to predict.

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 uninteresting. A common coding
convention is to write all EXISTS tests in the form `EXISTS(SELECT 1 WHERE ...)`. There are exceptions
to this rule however, such as subqueries that use INTERSECT.

This simple example is like an inner join on col2, but it produces at most one output row for each tab1 row, even if there are multiple matching tab2 rows:

SELECT col1 FROM tab1 WHERE EXISTS(SELECT 1 FROM tab2 WHERE col2 = tab1.col2);

expressionIN (value[, ...])

The right-hand side of this form of IN is a parenthesized list of scalar expressions. The result is TRUE if the left-hand expression's result is equal to any of the right-hand expressions. This is a shorthand notation for

expression=value1ORexpression=value2OR ...

Note that if the left-hand expression yields NULL, or if there are no equal right-hand values and at least one right-hand expression yields NULL, the result of the IN construct will be NULL, not FALSE. This is in accordance with SQL's normal rules for Boolean combinations of NULL values.

Note:This form of IN is not truly a subquery expression, but it seems best to document it in the same place as subquery IN.

expressionIN (subquery)

The right-hand side of this form of IN 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 of IN is TRUE if any equal subquery row is found. The result is FALSE if no equal row is found (including the special 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 the IN construct will be NULL, not FALSE. This is in accordance with SQL's normal rules for Boolean combinations of NULL values.

As with EXISTS, it's unwise to assume that the subquery will be evaluated completely.

(expression,expression[, ...]) IN (subquery)

The right-hand side of this form of IN is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand list. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result. The result of IN is TRUE if any equal subquery row is found. The result is FALSE if no equal row is found (including the special case where the subquery returns no rows).

As usual, NULLs in the expressions or subquery 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 row results are either unequal or NULL, with at least one NULL, then the result of IN is NULL.

expressionNOT IN (value[, ...])

The right-hand side of this form of NOT IN is a parenthesized list of scalar expressions. The result is TRUE if the left-hand expression's result is unequal to all of the right-hand expressions. This is a shorthand notation for

expression<>value1ANDexpression<>value2AND ...

Note that if the left-hand expression yields NULL, or if there are no equal right-hand values and at least one right-hand expression yields NULL, the result of the NOT IN construct will be NULL, not TRUE as one might naively expect. This is in accordance with SQL's normal rules for Boolean combinations of NULL values.

Tip:x NOT IN yis equivalent toNOT (x IN y)in all cases. However, NULLs are much more likely to trip up the novice when working with NOT IN than when working with IN. It's best to express your condition positively if possible.

expressionNOT IN (subquery)

The right-hand side of this form of NOT IN 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 of NOT IN is TRUE if only unequal subquery rows are found (including the special 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 the NOT IN construct will be NULL, not TRUE. This is in accordance with SQL's normal rules for Boolean combinations of NULL values.

As with EXISTS, it's unwise to assume that the subquery will be evaluated completely.

(expression,expression[, ...]) NOT IN (subquery)

The right-hand side of this form of NOT IN is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand list. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result. The result of NOT IN is TRUE if only unequal subquery rows are found (including the special case where the subquery returns no rows). The result is FALSE if any equal row is found.

As usual, NULLs in the expressions or subquery 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 row results are either unequal or NULL, with at least one NULL, then the result of NOT IN is NULL.

expressionoperatorANY (subquery)expressionoperatorSOME (subquery)

The right-hand side of this form of ANY 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 given
`operator`, which must yield a
Boolean result. The result of ANY is
TRUE if any true result is obtained. The result is FALSE if no
true result is found (including the special case where the
subquery returns no rows).

SOME is a synonym for ANY. 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 the ANY construct will be NULL, not FALSE. This is in accordance with SQL's normal rules for Boolean combinations of NULL values.

As with EXISTS, it's unwise to assume that the subquery will be evaluated completely.

(expression,expression[, ...])operatorANY (subquery) (expression,expression[, ...])operatorSOME (subquery)

The right-hand side of this form of ANY is a parenthesized subquery, which must return
exactly as many columns as there are expressions in the left-hand
list. The left-hand expressions are evaluated and compared
row-wise to each row of the subquery result, using the given
`operator`. Presently, only
`=` and `<>`
operators are allowed in row-wise ANY
queries. The result of ANY is TRUE if
any equal or unequal row is found, respectively. The result is
FALSE if no such row is found (including the special case where
the subquery returns no rows).

As usual, NULLs in the expressions or subquery 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 there is at least one NULL row result, then the result of ANY cannot be FALSE; it will be TRUE or NULL.

expressionoperatorALL (subquery)

The right-hand side of this form of ALL 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 given
`operator`, which must yield a
Boolean result. The result of ALL is
TRUE if all rows yield TRUE (including the special case where the
subquery returns no rows). The result is FALSE if any false
result is found.

NOT IN is equivalent to `<> ALL`.

Note that if there are no failures but at least one right-hand row yields NULL for the operator's result, the result of the ALL construct will be NULL, not TRUE. This is in accordance with SQL's normal rules for Boolean combinations of NULL values.

As with EXISTS, it's unwise to assume that the subquery will be evaluated completely.

(expression,expression[, ...])operatorALL (subquery)

The right-hand side of this form of ALL is a parenthesized subquery, which must return
exactly as many columns as there are expressions in the left-hand
list. The left-hand expressions are evaluated and compared
row-wise to each row of the subquery result, using the given
`operator`. Presently, only
`=` and `<>`
operators are allowed in row-wise ALL
queries. The result of ALL is TRUE if
all subquery rows are equal or unequal, respectively (including
the special case where the subquery returns no rows). The result
is FALSE if any row is found to be unequal or equal,
respectively.

As usual, NULLs in the expressions or subquery 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 there is at least one NULL row result, then the result of ALL cannot be TRUE; it will be FALSE or NULL.

(expression,expression[, ...])operator(subquery) (expression,expression[, ...])operator(expression,expression[, ...])

The left-hand side is a list of scalar expressions. The
right-hand side can be either a list of scalar expressions of the
same length, or a parenthesized subquery, which must return
exactly as many columns as there are expressions on the left-hand
side. Furthermore, the subquery cannot return more than one row.
(If it returns zero rows, the result is taken to be NULL.) The
left-hand side is evaluated and compared row-wise to the single
subquery result row, or to the right-hand expression list.
Presently, only `=` and `<>` operators are allowed in row-wise
comparisons. The result is TRUE if the two rows are equal or
unequal, respectively.

As usual, NULLs in the expressions or subquery 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 the row comparison is unknown (NULL).