Scalar Results

Data Types and SubtypesData Definition (DDL) Statements

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

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

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
  )

Predicates

A predicate is a simple expression asserting some fact, let’s call it P.If P resolves as TRUE, it succeeds.If it resolves to FALSE or NULL (UNKNOWN), it fails.A trap lies here, though: suppose the predicate, P, returns FALSE.In this case NOT(P) will return TRUE.On the other hand, if P returns NULL (unknown), then NOT(P) returns NULL as well.

In SQL, predicates can appear in CHECK constraints, WHERE and HAVING clauses, CASE expressions, the IIF() function and in the ON condition of JOIN clauses, and anywhere a normal expression can occur.