scalarSubqueryA scalarSubquery, sometimes called an expression subquery, is
a subquery that evaluates to a single row with a single column.scalarSubqueryexpression subquery
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.
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
-- 1 column "R1" and 1 row of data.
SELECT R1
FROM (VALUES('GROUP 1')) AS X(R1)