A scalar subquery is a parenthesized query expression that returns a single value (one row of a single column).

Scalar subqueries can be of the following types:

  • Correlated scalar subquery: Returns a single value for each row of the correlated outer table. A scalar subquery can be a correlated to table(s) in the containing query block where it returns a single value for each outer row.
  • Uncorrelated scalar subquery: Returns a single value to the containing query.

A correlated scalar subquery is used in the same way that a column is used, while an uncorrelated scalar subquery is used in the way that a constant value is used.

Scalar subqueries are not valid in the following cases:

  • When used in ORDER BY and GROUP BY clauses
  • When subqueries include UNION
  • When subqueries include LOB data types

A scalar subquery uses the following syntax:

Syntax


scalar_subquery :: = subquerysubquery :: = (query_expression)
scalar_subquery

Selects exactly one column or expression in its select list.

The data type of a scalar_subquery is same as that of the column of the query expression that it contains.

If the scalar_subquery returns more than one row, an error occurs stating that the subquery returns multiple rows. If the scalar_subquery returns 0 rows, then the scalar_subquery value is NULL.

A scalar subquery expression is valid in most syntax that calls for an expression (expr). Scalar subqueries can be used in the following components of SQL statements:

  • SELECT statement
    • Select list
    • WHERE clause:
    • ON clause predicates
    • HAVING clause
  • INSERT statement
    • VALUES clause
  • UPDATE statement
    • WHERE clause
  • DELETE statement
    • WHERE clause

The following examples illustrate the use of scalar subqueries in components of the SELECT statement:


SELECT d.DeptNo, d.DeptName, (SELECT COUNT (*) FROM pub.Employee e
WHERE e.DeptNo = d.DeptNo) 
AS Num_emp_in_dept FROM Department d;
 

SELECT * FROM pub.Employee e
WHERE (SELECT MIN (EmpNum) + 2 FROM pub.Employee) = (SELECT MIN (EmpNum)FROM pub.Employee e, pub.Department d
WHERE d.DeptCode = e.DeptCode
AND d.DeptName='SALES');
 

SELECT * FROM Employee 
WHERE Salary = (SELECT AVG (Salary) FROM Employee) + 1500;
 

SELECT e.EmpNum, d.DeptCode, d.DeptName FROM pub.Employee e 
INNER JOIN pub.Department d 
ON (SELECT DeptCode FROM pub.Employee where EmpNum = 1) = (Select DeptCode FROM pub.Department WHERE DeptName = ‘Sales');

The following examples illustrate the use of scalar subqueries in components of the INSERT statement:

INSERT INTO pub.Employee
  VALUES (1001, ‘Scott', (SELECT AVG(Salary) FROM pub.Employee), 102);

The following examples illustrate the use of scalar subqueries in components of the DELETE statement:

DELETE FROM pub.Employee
WHERE DeptNum = (SELECT d.DeptNum FROM pub.Department d
WHERE DeptName = ‘Sales')

The following examples illustrate the use of scalar subqueries in triggers and views:

CREATE VIEW V1 AS SELECT * FROM pub.Employee
WHERE (SELECT MIN (Salary) FROM pub.Employee) = (SELECT MIN (Salary) FROM pub.Employee e, pub.Department d
WHERE d.DeptNum = e.DeptNum AND DeptName = ‘Sales')
 
CREATE TRIGGER TRG_TST02902_TEST01 BEFORE INSERT ON tst_trg_01
BEGIN
//Inserting Into tst1
SQLIStatement insert_tst3 = new SQLIStatement ("INSERT INTO tst_trg_03 VALUES ((SELECT AVG(c2) FROM pub.t1))");
insert_tst3.execute();
END

The following examples illustrate the use of scalar subqueries as expressions:


SELECT (Salary + (SELECT AVG (Salary) FROM pub.Employee))AS base_sal FROM pub.Employee;
 

SELECT (SELECT prod_name FROM pub.Products) || OrderId FROM pub.Orders
 

SELECT SUM (CustNum), COUNT (CustNum) FROMPub.MtCustomer
WHERE CASE (SELECT MIN (CustNum) FROM pub.MtCustomer)WHEN (SELECT MIN (CustNum) FROM pub.MtOrder)THEN (SELECT MAX (CustNum) FROM pub.MtCustomer)ELSE (SELECT MIN (CustNum) FROM pub.MtCustomer) END = 2106;
 

SELECT ROUND ((SELECT MIN (CustNum) + 0.5 FROM pub.MtCustomer
WHERE Name = ‘Lift Tours-OEDProducts'),0) FROM pub.MtCustomer;