A scalar subquery is a subquery expression that can return a maximum of one value.
As is true of other types of subqueries, there are two types of scalar subqueries:
A correlated scalar subquery returns a single value for each row of its correlated outer table set.
A noncorrelated scalar subquery returns a single value to its containing query.
You can think of a correlated scalar subquery as an extended column of the outer table set to which it is correlated. In the same way, you can think of a noncorrelated scalar subquery as a parameterized value.
Accordingly, you can specify a correlated scalar subquery in a statement in the same way as a column, while you can specify a noncorrelated scalar subquery in the same way as you would a parameterized value wherever a column or parameterized value is allowed. This includes specifying a scalar subquery as a standalone expression in various clauses of a DML statement, or specified within an expression.
Scalar subqueries are compliant with the ANSI SQL:2011 standard.
The rules and restrictions are:
The following DML statements support scalar subqueries.
The recursive statement of a recursive query and the SELECT AND CONSUME statement do not support scalar subqueries.
You cannot specify a correlated scalar subquery in the condition of a SET or WHERE clause for the Upsert form of UPDATE.
For example, the following WHERE clause evaluates to TRUE for those rows in salestable that do not match any rows in prodtable on prod_no:
SELECT … FROM salestable AS s
WHERE (SELECT 1
FROM prodtable AS p
WHERE s.prod_no = p.prod_no) IS NULL;
You can specify a scalar subquery in the seed statement of a recursive view definition, but not in its recursive statement.
You cannot specify a scalar subquery in a join index definition.
For example, the following INSERT statement returns an error:
INSERT INTO t1 (1,2,3 (SELECT d2
FROM t2
WHERE a2=t1.a1));
A value list is a list of simple values that can be either constants or parameterized values. Therefore you can only specify a noncorrelated scalar subquery that returns a value of a primitive data type in a value list. You cannot specify a scalar subquery that returns a column of UDT or LOB data in a value list.
SELECT (SELECT ssq2+a1 FROM t1) AS ssq1,
(SELECT MAX(b3) FROM t3) AS ssq2
FROM t2;
You cannot nest the forward reference in another query block that is defined within the referencing expression. For example, the following statement, which includes a forward reference to ssq2 from the SSQ expression ssq1, is nested in a SELECT block within ssq1, and returns an error indicating incorrect use of a subquery:
SELECT (SELECT (SEL ssq2) + a1 FROM t1) AS ssq1,
(SELECT MAX(b3) FROM t3) AS ssq2
FROM t2;
To avoid the error, switch the two expressions in the select-list as shown below:
SELECT (SELECT MAX(b3) FROM t3) AS ssq2,
(SELECT (SEL ssq2) + a1 FROM t1) AS ssq1
FROM t2;
For more information about scalar subqueries, see“Specifying Subqueries in Search Conditions” on page 111.