SQL Reference Guide

Scalar Sub-query Expressions

Description

A scalar sub-query expression, denoted by grammar symbol scalar_subquery_expr, is a query_expression that returns only one value, that is, a value for one row and one column. See Query Expressions for more information on query_expression.

Scalar sub-query expressions can be specified in select lists, search conditions and arguments of scalar functions.

Examples

• Scalar sub-query in a select list

SELECT e.ename, ( SELECT d.dname

FROM dept d

WHERE d.deptno = e.deptno)

FROM emp e;

• Scalar sub-query in a search condition

SELECT ename

FROM emp

WHERE (SELECT MAX(deptno)

FROM dept) = deptno;

• Scalar sub-query as an argument to a scalar function.

SELECT e.ename, LEN ((SELECT d.dname

from dept d

WHERE d.deptno = e.deptno))

FROM emp e;