Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

Scalar Sub-query Expressions


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.


  • 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


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;