PostgreSQL: SubQuery Expressions
I kind of skipped over the functions/operators section of the PostgreSQL documentation when reading it. I want to review the sections that I think would be useful to me.
References
Note: Comparing NULL and NULL in PostgreSQL with a Boolean operator in PostgreSQL results in NULL, not TRUE or FALSE.
Notes
These SQL-compliant subquery expressions are available in PostgreSQL - all expressions return Boolean results.
EXISTS
EXISTS (subquery)
The argument of EXISTS is an arbitrarySELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the results 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 long enough to determine whether at least one row is returned, not all the way to completion. A common convention for subqueries is SELECT 1 WHERE... since it doesn't matter what is in the rows that are returned.
IN
expression|row_constructor IN (subquery)
expression- 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 of
INistrue
if any equal subquery is found. The result isfalse
is no equal row is found.
- 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 of
row_constructor- The left hand side is a row constructor and the right-hand side is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result. The result of
INistrue
if any equal subquery row is found. The result isfalse
if no equal row is found.
- The left hand side is a row constructor and the right-hand side is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result. The result of
NOT IN
expression|row_constructor NOT IN (subquery)
Basically the opposite of IN.
ANY/SOME
expression operator ANY (subquery)
expression operator SOME (subquery)
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 given operator, which must yield a Boolean result. The result of ANY (and SOME, which is a synonym for ANY) is true
if any result is obtained. The result is false
is no true result is found.
ALL
exppression|row_cosntructor operator ALL (subquery)
The right-hand side is a parenthesized subquery.
expression- 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 ofALListrue
if all rows yield true. The result isfalse
if any false result is found. The result isNULLif no comparison with a subquery row returns false, and at least one comparison returnsNULL
- The left hand expression is evaluated and compared to each row of the subquery result using the given
row_constructor- The
row_constructorand subquery must return the same number of columns. The results are evaluated and compared row-wise to each row of the subquery result, using the givenoperator. The result ofALListrue
if the comparison returns true for all subquery rows. The result isfalse
if the comparison returns false for any subquery rows. The result isNULLif no comparison with a subquery row returns false, and at least one comparison returns NULL.
- The
Single-Row Comparison
row_constructor operator (subquery)
row_constructor and subquery must return equal number of columns. The subquery cannot return more than one row. The left hand side is evaluated and compared row-wise to the single subquery result row. See this section of the documentation for the meaning of a row constructor comparison.
Comments
You have to be logged in to add a comment
User Comments
There are currently no comments for this article.