Using subqueries to combine SELECT statements

You can construct a SELECT statement with a subquery to replace two separate SELECT statements.

Subqueries in SELECT statements allow you to perform various tasks, including the following actions:
  • Compare an expression to the result of another SELECT statement
  • Determine whether the results of another SELECT statement include a specific expression
  • Determine whether another SELECT statement returns any rows

An optional WHERE clause in a subquery is often used to narrow the search condition.

A subquery selects and returns values to the first or outer SELECT statement. A subquery can return no value, a single value, or a set of values, as follows:
  • If a subquery returns no value, the query does not return any rows. Such a subquery is equivalent to a NULL value.
  • If a subquery returns one value, the value is in the form of either one aggregate expression or exactly one row and one column. Such a subquery is equivalent to a single number or character value.
  • If a subquery returns a list or set of values, the values can represent one row or one column.
  • In the FROM clause of the outer query, a subquery can represent a set of rows (sometimes called a derived table or a table expression).