This term relates to any SELECT that returns a single value (one column, one row). It can be used as an expression in the SELECT, WHERE, or HAVING parts of an SQL query. If the scalar fullselect returns more than 1 row or 1 value, an error occurs. If nothing is found based on the query logic, a null is returned.
The error for more than 1 row returned would be: ‘SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. SQLSTATE=21000’
The error for more than 1 value returned would be: Multiple columns are returned from a subquery that is allowed only one column.. SQLCODE=-412, SQLSTATE=42823
This allows for:
1. Another way to get back results where the output contains both detail data and aggregate data on the same row
2. Gathering aggregate data for comparison in WHERE logic.
3. Selecting a value to be used for logic
SELECT D.DEPTNO, D.DEPTNAME,
(SELECT AVG(SALARY) ==> or SELECT DEC(AVG(SALARY),7,2)
FROM EMP E
WHERE E.WORKDEPT = D.DEPTNO) AS DEPT_AVG_SAL
FROM DEPT D