Subqueries in SELECT statements

A subquery (the inner SELECT statement, where one SELECT statement is nested within another) can return zero or more rows or expressions. Each subquery must be delimited by parentheses, and must contain a Projection clause and a FROM clause. A subquery can itself contain other subqueries.

The database server supports subqueries in the following contexts:

A SELECT statement nested in the Projection clause of another SELECT statement
a SELECT statement nested in the WHERE clause of another SELECT statement
a SELECT statement nested in the FROM clause of another SELECT statement.

You can also specify a subquery in various clauses of the INSERT, DELETE, MERGE, or UPDATE statements where a subquery is valid.

Subqueries in the Projection clause or in the WHERE clause can be correlated or uncorrelated. A subquery is correlated when the value that it produces depends on a value produced by the outer SELECT statement that contains it. For more information, see Correlated subqueries.

Any other kind of subquery is considered uncorrelated. Only uncorrelated subqueries are valid in the FROM clause of the SELECT statement.