Chapter 7 subqueries

MULTIPLE LEVELS OF NESTING

Thus far, we have studied subqueries nested one level in depth. However, subqueries may themselves contain subqueries. When the WHERE clause of a subquery has as its object another subquery, these are termed nested subqueries. The manager of the project named Remodel ER Suite requires a listing of employees that worked more than 10 hours on the project. A subquery is appropriate because the result table lists only columns from a single table—the employee table. The result table produced by SQL Example 7.11 only displays employee last and first names.

/* SQL Example 7.11 */

SELECT LastName "Last Name", FirstName "First Name"

FROM Employee

WHERE EmployeeID IN
(SELECT EmployeeID

FROM ProjectAssignment

WHERE HoursWorked > 10 AND ProjectNumber IN

(SELECT ProjectNumber

FROM Project

WHERE ProjectTitle = 'Remodel ER Suite') );

Last Name First Name

--------------- ---------------

Bordoloi Bijoy

Klepper Robert

Smith Susan
To understand how this query executes, we begin our examination with the lowest subquery. We execute it independently of the outer queries. It is important to note that this subquery is useful where the project name is known, but the associated project number is not known. SQL Example 7.12 gives the query and result table. The result is a single column from a single row known as a scalar result.
/* SQL Example 7.12 */

SELECT ProjectNumber

FROM Project

WHERE ProjectTitle = 'Remodel ER Suite';

PROJECTNUMBER

-------------

4
Now, let's substitute the project number into the IN operator list for the intermediate subquery and execute it as shown in SQL Example 7.13. The intermediate result table lists three EmployeeID column values for employees that worked more than 10 hours on project 4.
/* SQL Example 7.13 */

SELECT EmployeeID

FROM ProjectAssignment

WHERE HoursWorked > 10 AND ProjectNumber IN (4);

EMPLOYEEID

----------

23100

66432

88505
Finally, we will substitute these three EmployeeID column values into the IN operator listing for the outer query in place of the subquery.
/* SQL Example 7.14 */

SELECT LastName "Last Name", FirstName "First Name"

FROM Employee

WHERE EmployeeID IN ('23100', '66432', '88505');

Last Name First Name

--------------- ---------------

Bordoloi Bijoy

Klepper Robert

Smith Susan
As you can see, the final result table matches the results produced when the entire query with two nested subqueries is executed. This decomposition approach to studying queries can also help you in writing nested subqueries.

Subqueries and Comparison Operators

This section discusses subqueries that use a comparison operator in the WHERE clause.

Comparison Operators and Errors

Table 7.3 provides a listing of the SQL comparison operators and their meaning.

Table 7.3

Comparison Operator	Meaning
=	equal to
<	less than
>	greater than
>=	greater than or equal to
<=	less than or equal to
!=	not equal to
<>	not equal to
!>	not greater than
!<	not less than

The general form of the WHERE clause with a comparison operator is similar to that used thus far in the text. Note that the subquery is again enclosed by parentheses.

WHERE (subquery)
Let us consider an earlier example of the equal sign (=) comparison operator, as follows:
WHERE Salary =

(SELECT MIN(Salary)

FROM Employee);
The most important point to remember when using a subquery with a comparison operator is that the subquery can return only a single or scalar value. This is also termed a scalar subquery because a single column of a single row is returned by the subquery. If a subquery returns more than one value, Oracle will generate the ORA-01427: single-row subquery returns more than one row error message, and the query fails to execute. Let us examine a subquery that will not execute because it violates the "single value" rule. The query in SQL Example 7.15 returns multiple values for the Salary column of the employee table.
/* SQL Example 7.15 */

SELECT Salary

FROM Employee
WHERE Salary > 20000;
SALARY

--------

$22,000

$23,545

$30,550

$27,500

$32,500

$22,325

$23,000

7 rows selected.
Now, if we substitute this query as a subquery in another SELECT statement, then that SELECT statement fails. This is demonstrated in the SELECT statement SQL Example 7.16. The SQL code fails because the subquery uses the greater than (>) comparison operator and the subquery returns multiple values.
/* SQL Example 7.16 */

SELECT EmployeeID

FROM Employee
WHERE Salary >

(SELECT Salary

FROM Employee
WHERE Salary > 20000);
ERROR at line 4:

ORA-01427: single-row subquery returns more than one row

To use a subquery as the object of a comparison operator, you must be familiar enough with the data stored in the relevant tables and with the nature of the programming problem to know with certainty that the subquery will return a scalar value. You can also test the subquery to determine whether it returns a scalar value, although this approach does not always guarantee a scalar value is returned when the data values change over time.

Aggregate Functions and Comparison Operators

As you may recall, the aggregate functions (AVG, SUM, MAX, MIN, and COUNT) always return a scalar result table. Thus, a subquery with an aggregate function as the object of a comparison operator always executes provided you have formulated the query properly.

Suppose that a payroll manager needs a listing of employees who have a salary level that is greater than the average salary for all employees. SQL Example 7.17 uses the AVG aggregate function in the subquery to produce the desired result.

/* SQL Example 7.17 */

SELECT LastName "Last Name", FirstName "First Name", Salary "Salary"

FROM Employee

WHERE Salary >

(SELECT AVG(Salary)
FROM Employee);
Last Name First Name Salary

--------------- --------------- --------

Simmons Lester $22,000

Boudreaux Beverly $17,520

Bock Douglas $16,250

more rows are displayed . . .
The subquery produces a scalar result table with a single value—the average salary of all employees. The outer query then lists employees that have a salary that exceeds the average salary.

Comparison Operators Modified with the ALL or ANY Keywords

The ALL and ANY keywords can modify a comparison operator to allow an outer query to accept multiple values from a subquery. The general form of the WHERE clause for this type of query is:
WHERE [ALL | ANY] (subquery)
The ALL and ANY keywords can produce a result table that has zero, one, or more than one value. Subqueries that use these keywords may also include GROUP BY and HAVING clauses.

ALL Keyword

To understand the ALL keyword, let us examine its effect on the "greater than" (>) comparison operator in a SELECT statement. Suppose our payroll manager needs an employee list of those employees with a salary that is greater than the salary of all of the employees in department 8. In SQL Example 7.18, the ALL keyword modifies the greater than comparison operator to mean greater than all values.
/* SQL Example 7.18 */

SELECT LastName "Last Name", FirstName "First Name",

Salary "Salary"

FROM Employee

WHERE Salary > ALL

(SELECT Salary

FROM Employee
WHERE DepartmentNumber = 8 AND Salary IS NOT NULL);
Last Name First Name Salary

--------------- --------------- --------

Becker Robert $23,545

Jones Quincey $30,550

Barlow William $27,500

Smith Susan $32,500

Quattromani Toni $22,325

Becker Roberta $23,000

6 rows selected.
Conceptually, for each row in the employee table, the inner query creates a final listing of salaries of employees that work in department 8. The WHERE clause eliminates rows for non-salary employees. Recall that a NULL value is an unknown value. Rows with a NULL Salary column value must be eliminated in order for the comparison WHERE Salary > ALL of the outer query to properly select rows. If NULL Salary column values are in the comparison group, then the outer query will return no rows since it will not be possible to determine if a Salary column value is greater than an unknown value. We can determine the salaries of salary employees by executing the inner query by itself.
/* SQL Example 7.19 */

SELECT Salary

FROM Employee
WHERE DepartmentNumber = 8 AND Salary IS NOT NULL;
SALARY

--------

$22,000

$17,520

$5,500
The outer query finds the largest salary value in the list for department 8. This is $22,000. Next, the outer query compares the salary of each employee to this largest value one row at a time. The result table includes only employees that have a salary that is larger than that of anyone who works in department 8. In this case, six employees have such a salary.

Incidentally, this is an example of a situation where you as the SQL programmer need to be familiar with the database and data contained therein. If you did not know that some employees are paid a wage instead of a salary, then you would know that the Salary column can contain NULL values, and you would not know that it is necessary to specify that the Salary column value is NOT NULL.

ANY Keyword

The ANY keyword is not as restrictive as the ALL keyword. When used with the greater than comparison operator, "> ANY" means greater than some value. Let's examine the ANY keyword when used within the SELECT statement in SQL Example 7.20. Here, the firm’s payroll manager needs the employee name and salary of any employee that has a salary that is greater than that of any employee with a salary that exceeds $23,000. This query is not the same as asking for a listing of employees with salaries that exceed $23,000. We shall see how it differs by examining the execution of the SELECT statement in detail.
/* SQL Example 7.20 */

SELECT LastName "Last Name", FirstName "First Name",

Salary "Salary"

FROM Employee

WHERE Salary > ANY

(SELECT Salary

FROM Employee
WHERE Salary > 23000);
Last Name First Name Salary

--------------- --------------- --------

Smith Susan $32,500

Jones Quincey $30,550

Barlow William $27,500
For each employee, the inner query finds a list of salaries that are greater than $23,000. Let's execute the inner query by itself in order to see the listing that is produced.
/* SQL Example 7.21 */

SELECT Salary

FROM Employee
WHERE Salary > 23000;
SALARY

--------

$23,545

$30,550

$27,500

$32,500
A total of four employee salaries are listed in the intermediate result table produced by the inner query, and the smallest of these is $23,545. The outer query looks at all the values in the list and determines whether the employee currently being considered earns more than any of the salaries in the intermediate result table (here, this means more than $23,545). The employees listed in the final result table all earn more than the $23,545 listed in the intermediate result table.

An "= ANY" (Equal Any) Example

The "= ANY" operator is exactly equivalent to the IN operator. For example, to find the names of employees who have male dependents, you can use either IN or "= ANY" – both of the queries in SQL Examples 7.22 and 7.23 produce identical result tables.
/* SQL Example 7.22 */

SELECT LastName "Last Name", FirstName "First Name"

FROM Employee

WHERE EmployeeID IN

(SELECT EmployeeID

FROM Dependent

WHERE Gender = 'M');
/* SQL Example 7.23 */

SELECT LastName "Last Name", FirstName "First Name"

FROM Employee

WHERE EmployeeID = ANY

(SELECT EmployeeID

FROM Dependent

WHERE Gender = 'M');
Last Name First Name

--------------- ---------------

Bock Douglas

Boudreaux Beverly

Simmons Lester

A "!= ANY" (Not Equal Any) Example

As we saw above, the "= ANY" is identical to the IN operator. However, the "!= ANY" (not equal any) is not equivalent to the NOT IN operator.

If a subquery of employee salaries produces an intermediate result table with the salaries $23,545, $27,500, $30,550, and $32,500, then the WHERE clause shown here means "NOT $38,000" AND "NOT $43,000" AND "NOT $55,000."

WHERE NOT IN (23545, 27500, 30550, 32500);
However, the "!= ANY" comparison operator and keyword combination shown in this next WHERE clause means "NOT $23,545" OR "NOT $27,500" OR "NOT $30,550" OR "NOT $32,500."
WHERE != ANY (23545, 27500, 30550, 32500);
Let's consider another situation. Suppose a human resource manager needs a listing of employees that do not have dependents. You might write the following erroneous query:
/* SQL Example 7.24 */

SELECT LastName "Last Name", FirstName "First Name"

FROM Employee

WHERE EmployeeID != ANY

(SELECT DISTINCT EmployeeID

FROM Dependent);

Last Name First Name

--------------- ---------------

Simmons Lester

Boudreaux Beverly

Adams Adam

. . .

24 rows selected.
The query produces output, but the query actually fails because the result table lists every employee in the employee table! This occurs because the inner query returns all EmployeeID values for employees with dependents from the dependent table. Then, for each dependent table EmployeeID value, the outer query finds all employee table EmployeeID values that do not match one of the dependent table EmployeeID values. However, since there are four values in the intermediate result table (there are four employees with dependents), the "!= ANY" operator is like an "OR" operator during the comparison, and of course each employee is not related to each dependent so it is possible to find at least one dependent to which each employee is not related. In this example, the "!= ANY" operator will always return a TRUE value. Remember, when the logical OR operator is used, a value is returned when any of the conditions tested is TRUE.

So, how can we answer the management query? The solution approach was actually covered earlier in this chapter—use the NOT IN operator as is done in SQL Example 7.25.

/* SQL Example 7.25 */

SELECT LastName "Last Name", FirstName "First Name"

FROM Employee

WHERE EmployeeID NOT IN

(SELECT DISTINCT EmployeeID

FROM Dependent);

Last Name First Name

--------------- ---------------

Sumner Elizabeth

Eakin Maxwell

Webber Eugene

. . .

20 rows selected.