4.1.5. Subqueries

A subquery is a special form of expression that is actually a query embedded within another query. Subqueries are written in the same way as regular SELECT queries, but they must be enclosed in parentheses. Subquery expressions can be used in the following ways:

• To specify an output column in the SELECT list

• To obtain values or conditions for search predicates (the WHERE, HAVING clauses).

• 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)

Correlated Subqueries

A subquery can be correlated. A query is correlated when the subquery and the main query are interdependent. To process each record in the subquery, it is necessary to fetch a record in the main query; i.e., the subquery fully depends on the main query.

Sample Correlated Subquery

SELECT *
FROM Customers C
WHERE EXISTS
  (SELECT *
   FROM Orders O
   WHERE C.cnum = O.cnum
     AND O.adate = DATE '10.03.1990');

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 as the output column in a SELECT list:

   SELECT
     e.first_name,
     e.last_name,
     (SELECT
          sh.new_salary
      FROM
          salary_history sh
      WHERE
          sh.emp_no = e.emp_no
      ORDER BY sh.change_date DESC ROWS 1) AS last_salary
   FROM
     employee e

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

   SELECT
     e.first_name,
     e.last_name,
     e.salary
   FROM
     employee e
   WHERE
     e.salary = (
                 SELECT MAX(ie.salary)
                 FROM employee ie
                )