SQL Scalar Fullselects

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

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.

Example 1:

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

This scalar fullselect coded will return either a value or nulls for each DEPTNO value in the DEPT table.  These scalar fullselects are codes as correlated sub selects and will return a value for each specific DEPTNO coming from the DEPT table.  This executes just like a correlated subquery.  This could also be coded as: 

SELECT D.DEPTNO, D.DEPTNAME,
             AVG(E.SALARY) AS DEPT_AVG_SAL
FROM DEPT D  LEFT JOIN EMP E
     ON E. WORKDEPT = D.DEPTNO)
GROUP BY D.DEPTNO, D.DEPTNAME
; 

If no name is provided for the scalar fullselect column in the SELECT clause, then in this example it will show the number 3 since it is the third column in the list.

Note: The left join is needed here in order to include those departments that do not have any employees on the EMP table and will return nulls for those average salaries. An inner join would eliminate those departments that have no rows on the EMP table to calculate an average salary.  

Is the first one with the scalar fullselect more or less efficient than the left outer join? I don’t know. It would have to be tested. But it provides another way to gather the results needed.

Example 2:

List the departments where the average salary is >  ??? some amount,  Here is an example of those with an average > 40000.00.  Also show the number of employees that went into the average.

SELECT D.DEPTNO, D.DEPTNAME,
              (SELECT DEC(AVG(SALARY),7,2) 
               FROM EMP E
               WHERE E. WORKDEPT = D.DEPTNO) AS DEPT_AVG_SAL,
               (SELECT COUNT(*)
               FROM EMP E
              WHERE E. WORKDEPT = D.DEPTNO) AS NUM_EMPS
FROM DEPT D
WHERE (SELECT AVG(SALARY) FROM EMP E
               WHERE E.WORKDEPT = D.DEPTNO)      >  40000.00  
;

Example 3: 

List the departments where the average bonus of its employees is > average salary of its employees.

SELECT D.DEPTNO, D.DEPTNAME,
              (SELECT AVG(BONUS) 
               FROM EMP E
               WHERE E. WORKDEPT = D.DEPTNO) AS DEPT_AVG_BONUS,
              (SELECT AVG(SALARY) 
               FROM EMP E
               WHERE E.WORKDEPT = D.DEPTNO) AS DEPT_AVG_SAL
FROM DEPT D
WHERE (SELECT AVG(BONUS) FROM EMP E
               WHERE E. WORKDEPT = D.DEPTNO)    
               >   
              (SELECT AVG(SALARY) FROM EMP E
               WHERE E. WORKDEPT = D.DEPTNO)   
;

Note: In the sample data there is no department where the average bonus > average salary. But this would show any that did.

Example 4: 

List each departments with the number of employees and the number of projects.

Note:  ORDER BY and FETCH FIRST 1 ROW ONLY can be used in scalar fullselects. If both are coded the ORDER BY always executes first and orders the data before the FETCH FIRST.  

As we look at these examples, there are other ways to get the same results. The purpose of this article is to show the use of scalar fullselects in SQL that at times may be the most efficient. Only testing will tell!