Chapter 7 subqueries

Chapter 7

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 operator (=). Okay, so the subquery doesn't appear to be such a big deal. In fact, you could quickly scan the employee table and find the same result. What if the employee table has thousands or even millions of rows? Query 7.3 produces a quick listing, while the manual approach could take hours or even days to complete the task!

You should also note that there are no hard-coded parameters in the query except for the table and column names. The criteria for row selection used for the WHERE clause comes from the result table produced by the subquery. We say that data for the subquery are derived at run time. As such, the value returned by this subquery can, and most probably will, change over time depending on the contents of a table, yet the query will always produce accurate and dependable results.

DEFINITION OF A SUBQUERY

Now that you have seen an example subquery, it may help you to learn the concept by formally defining subqueries.

Formal Definition

As was stated earlier, a subquery is a query inside another query. More formally, it is the use of a SELECT statement inside one of the clauses of another SELECT statement. In fact, a subquery can be contained inside another subquery, which is inside another subquery, and so forth. A subquery can also be nested inside INSERT, UPDATE, and DELETE statements.

The subquery you studied earlier in SQL Example 7.3 is an example of a subquery inside a WHERE clause. This is termed a nested subquery or inner query. The term outer query is sometimes used to refer to the SELECT statement that contains a subquery.

A subquery can be used any place where an expression is allowed providing it returns a single value. This means that a subquery that returns a single value can also be listed as an object in a FROM clause listing. This is termed an inline view because when a subquery is used as part of a FROM clause, it is treated like a virtual table or view (views are covered later in this textbook). We will focus here on the use of subqueries with WHERE and HAVING clauses. Subqueries with FROM clauses will not be covered as the technique is not used very often.

Oracle allows a maximum nesting of 255 subquery levels in a WHERE clause. There is no limit for nesting subqueries expressed in a FROM clause. In practice, the limit of 255 levels is not really a limit at all because it is rare to encounter subqueries nested beyond three or four levels. In fact, the practice of nesting one SELECT statement inside another is the reason for the use of the word structured in the name Structured Query Language.

Subquery Types

There are three basic types of subqueries. We will study each of these in the remainder of this chapter.

1. 
   Subqueries that operate on lists by use of the IN operator or with a comparison operator modified by the ANY or ALL optional keywords. These subqueries can return a group of values, but the values must be from a single column of a table. In other words, the SELECT clause of the subquery must contain only one parameter, that is, only one column name or only one expression or only one aggregate function.
   
2. 
   Subqueries that use an unmodified comparison operator (=, <, >, <>)—these subqueries must return only a single, scalar value.
   
3. 
   Correlated subqueries that, unlike regular subqueries, depend on data provided by the outer query. This type of subquery also includes subqueries that use the EXISTS operator to test the existence of data rows satisfying specified criteria.
   

Subquery Syntax—General Rules

A subquery SELECT statement is very similar to the SELECT statement used to begin a regular or outer query. The complete syntax of a subquery is:
( SELECT [DISTINCT] subquery_select_parameter

FROM {table_name | view_name}

{table_name | view_name} ...

[WHERE search_conditions]

[GROUP BY column_name [,column_name ] ...]

[HAVING search_conditions] )

You should notice a few minor differences between a subquery and regular query syntax. For example, the ORDER BY clause cannot be used in writing the subquery part of a query. There are additional clauses that are restricted, including the COMPUTE and FOR BROWSE clauses, but these are beyond the scope of this text. Subqueries can be nested inside both the WHERE and HAVING clauses of an outer SELECT, or inside another subquery. Additionally, a subquery is always enclosed in parentheses.