FirebirdSQL logo
 Data Types and SubtypesData Definition (DDL) Statements 

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 (see below).

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 query returns more columns or rows, a run-time error will occur (“Multiple rows in a singleton select…​”).

Note

Although it is reporting a genuine error, the message can be slightly misleading.A “singleton SELECT” is a query that must not be capable of returning more than one row.However, “singleton” and “scalar” are not synonymous: not all singleton SELECTS are required to be scalar;and single-column selects can return multiple rows for existential and quantified predicates.
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
  )