Contents / Previous / Next


Sub-Queries, Nested Queries (more info: I , II )

Subqueries are similar to SELECT chaining. While SELECT chaining combines SELECTs on the same level in a query, however, subqueries allow SELECTs to be embedded inside other queries:

They can take the place of a constant (Scalar Subqueries),
They can return a list of values for use in a comparison.
They can take the place of a constant yet vary based on the row being processed,

Subqueries are used in the following subquery expressions which return Boolean (true/false) results.


EXISTS ( subquery )

The argument of EXISTS is an arbitrary SELECT statement. 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.

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);

Example "Students in Projects":

SELECT name FROM stud WHERE EXISTS( SELECT 1 FROM assign WHERE stud = stud.id ) ; name ------ fred john lisa (3 rows)


[ NOT ] IN

IN [ NOT ] (scalar form) expression IN (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.


IN [ NOT ] (subquery form)

expression IN (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.

Example "Is fred in Project 1? (--Yes)":

SELECT id, name FROM stud WHERE id in ( SELECT stud FROM assign WHERE id = 1 ) ; id | name ----+------ 1 | fred (1 row)


ANY and SOME

expression operator ANY (subquery) expression operator SOME (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.

SOME is a synonym for ANY. IN is equivalent to = ANY.


ALL

expression operator ALL (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).

NOT IN is equivalent to <> ALL.


Row-wise comparison

(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.