• To specify an output column in the SELECT list

• To produce a set that the enclosing query can select from, as though were a regular table or view. Subqueries like this appear in the FROM clause (derived tables) or in a Common Table Expression (CTE)

Sample Correlated Subquery

When subqueries are used to get the values of the output column in the SELECT list, a subquery must return a scalar result.

Scalar Results

Subqueries used in search predicates, other than existential and quantified predicates, must return a scalar result; that is, not more than one column from not more than one matching row or aggregation. If the result would return more, a run-time error will occur (“Multiple rows in a singleton select…​”).

Subquery Examples

1. A subquery in the clause for obtaining the employee’s maximum salary and filtering by it: