scalarSubquery

You can place a scalarSubquery anywhere an expression is permitted. A scalarSubquery turns a selectExpression result into a scalar value because it returns only a single row and column value.

The query must evaluate to a single row with a single column.

Sometimes also called an expression subquery.

Syntax

( query
    [ ORDER BY clause ]
    [ result offset clause ]
    [ fetch first clause ]
)

Examples

-- avg always returns a single value, so the subquery is
-- a scalarSubquery
SELECT NAME, COMM
  FROM STAFF
  WHERE EXISTS
    (SELECT AVG(BONUS + 800)
       FROM EMPLOYEE
       WHERE COMM < 5000
       AND EMPLOYEE.LASTNAME = UPPER(STAFF.NAME)
)
-- Introduce a way of "generating" new data values,
-- using a query which selects from a VALUES clause (which is an 
-- alternate form of a fullselect). 
-- This query shows how a table can be derived called "X" having 
-- 2 columns "R1" and "R2" and 1 row of data.
SELECT R1,R2 
	FROM (VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)