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

#### 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: Statements
Related concepts
Interaction with the dependency system
CREATE statements
DROP Statements
RENAME statements
SET statements
Related reference
ALTER TABLE statement
GRANT statement
REVOKE statement
CALL (PROCEDURE)
CONSTRAINT clause
DECLARE GLOBAL TEMPORARY TABLE statement
DELETE statement
FOR UPDATE clause
FROM clause
GROUP BY clause
HAVING clause
INNER JOIN
INSERT statement
JOIN operation
LEFT OUTER JOIN
LOCK TABLE statement
ORDER BY clause
Query
RIGHT OUTER JOIN
SelectExpression
SELECT statement
TableExpression
TableSubquery
UPDATE statement
VALUES expression
WHERE clause
WHERE CURRENT OF clause