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.