Sub-Queries, Nested Queries

text zoom

Previous: Basic SQL Funtion Constraints in the Table :Next

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

[ 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

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.

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.