Scalar Subquery

A ScalarSubquery turns a SelectExpression result into a scalar value because it returns only a single row and column value.

Syntax

Usage

You can place a ScalarSubquery anywhere an Expression is permitted. The query must evaluate to a single row with a single column.

Scalar subqueries are also called expression subqueries.

Examples

The AVG function always returns a single value; thus, this is a scalar subquery:

SELECT NAME, COMM
  FROM STAFF
  WHERE EXISTS
    (SELECT AVG(BONUS + 800)
       FROM EMPLOYEE
       WHERE COMM < 5000
       AND EMPLOYEE.LASTNAME = UPPER(STAFF.NAME)
    );

See Also