CHAPTER 7
---------------------------------------------------------------------------------------------------------------------
SUBQUERIES

Thus far, you have learned to write queries where all of the information needed to specify retrieval criteria is known at design time. The term design time simply means that you are in the processing of writing or designing a query. This contrasts with run time, which refers to the actual execution and processing of a query. In this chapter, you will expand your understanding of the SELECT statement to include the topic of subqueries. Quite simply, a subquery is a query within a query. Subqueries enable you to write queries that select data rows for criteria that are actually developed while the query is executing at run time.
Objectives
In order to understand the subquery approach to information retrieval, we will first review what you have learned to this point about the SELECT statement. Your learning objectives for this chapter include:

Learn the formal subquery definition and write a subquery.
Learn the subquery restrictions.
Use the IN operator when writing a subquery.
Nest subqueries at multiple levels.
Use comparison operators when writing a subquery.
Use the ALL and ANY keywords when writing a subquery.
Write a correlated subquery including the use of the EXISTS operator.
Use the ORDER BY clause when writing a subquery.
A SUBQUERY EXAMPLE
SQL Example 7.1 queries the employee table. You know at design time that you want to retrieve employee information where employee salaries are at or above $25,000, and employees work in department 3 or 7. Additionally, the actual criteria values used in row selection are hard-coded—$20,000 for the employee monthly salary and departments 3 and 6 for the department number.

/* SQL Example 7.1 */
COLUMN "Last Name" FORMAT A15;
COLUMN "First Name" FORMAT A15;
COLUMN "Dept" FORMAT 9999;
COLUMN "Salary" FORMAT $99,999;
SELECT LastName "Last Name", FirstName "First Name", DepartmentNumber "Dept", Salary "Salary"
FROM Employee
WHERE Salary >= 20000 AND DepartmentNumber IN (3, 6);

Last Name First Name Dept Salary
--------------- --------------- ----- --------
Becker Robert 3 $23,545
Jones Quincey 3 $30,550
Barlow William 3 $27,500
Smith Susan 3 $32,500
Becker Roberta 6 $23,000 more rows are displayed . . .

But suppose you need to write a query where the criteria values to be used in a WHERE clause are unknown at design time. As an example, consider a requirement to list the names of all employees that earn a salary equal to the minimum salary amount paid within your organization. The problem is that at design time, you do not know what the minimum salary amount is! Further, over time, the minimum salary will surely change. You could break this query into two tasks by first writing a query to determine the minimum salary amount, like the query in SQL Example 7.2.

/* SQL Example 7.2 */
COLUMN "Min Salary" FORMAT $999,999;
SELECT MIN(Salary) "Min Salary"
FROM Employee;

Min Salary
----------
$2,200

Next, you could substitute the value $2,200 for the minimum employee salary into the WHERE clause of a second query. However, the subquery approach allows you to combine these two separate queries into one query as is illustrated in SQL Example 7.3.

/* SQL Example 7.3 */
SELECT LastName "Last Name", FirstName "First Name", Salary "Salary"
FROM Employee
WHERE Salary = (SELECT MIN(Salary) FROM Employee);

Last Name First Name Salary
--------------- --------------- --------
Simmons Leslie $2,200
Young Yvonne $2,200

Notice that the subquery is essentially the first query that you would use in a two-part query approach. Also, the subquery is the object of the equal comparison