SQL in a Nutshell, 2nd Edition

Name

SUBQUERY Substatement

Synopsis

A subquery is a nested query. Subqueries may appear in various places within a SQL statement.

Platform	Command
DB2	Supported
MySQL	Supported with limitations
Oracle	Supported
PostgreSQL	Supported
SQL Server	Supported

SQL supports the following types of subquery:

Scalar subqueries

Subqueries that retrieve a single value. These are the most widely supported type of subquery among the various database platforms.

Table subqueries

Subqueries that retrieve more than one values. This type of subquery retrieves more than a row of values.

Nested table subqueries

Subqueries that retrieve more than one column and more than one row.

Scalar and vector subqueries can, on some platforms, appear as part of the expression in a SELECT list of items, WHERE clause, and HAVING clause. Nested table subqueries tend to appear in the FROM clause of SELECT statements.

A correlated subquery is a subquery that is dependent upon a value in an outer query. Consequently, the inner query is executed once for every record retrieved in the outer query. Since subqueries can be nested many layers deep, a correlated subquery may reference any level in the main query higher than its own level.

Different rules govern the behavior of a subquery depending on which clause it appears in. The level of support amongst the database platforms also varies. Some platforms support subqueries in all clauses mentioned earlier (SELECT, FROM, WHERE and HAVING), while others support subqueries in ...