# 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)

Parent topic: SQL queries