A scalarSubquery, sometimes called an expression subquery, is a subquery that evaluates to a single row with a single column.
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.
( query [ ORDER BY clause ] [ result offset clause ] [ fetch first clause ] )
-- 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)