Chapter 7 subqueries

Subquery Restrictons

Clause Restrictions

Generally speaking, a SELECT clause of a subquery must contain only one expression, only one aggregate function, or only one column name. Additionally, the value(s) returned by a subquery must be join compatible with the WHERE clause of the outer query. This last point is made clearer through the example SELECT statement shown in SQL Example 7.4. This query lists the names of employees that have dependents. The domain of values in the dependent table's EmployeeID column is all valid employee identifying numbers. Thus, the values returned from this column in the dependent table in the subquery are join compatible with values stored in the employee table's EmployeeID column of the outer query because they are the same type of data and the data values come from the same domain of possible values.
/* SQL Example 7.4 */

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

FROM Employee

WHERE EmployeeID IN

(SELECT EmployeeID

FROM Dependent);

Last Name First Name

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

Bock Douglas

Bordoloi Bijoy

Boudreaux Beverly

Simmons Lester

Notice that the LastName and FirstName columns are not stored in the dependent table. These columns only occur in the employee table. The strategy used in developing this query first focuses on producing an intermediate result table that contains the EmployeeID values for those employees who have dependents. This is accomplished by the subquery.

Next the outer SELECT statement then qualifies the employee names to be displayed (employee rows) through use of the IN operator. Any employees with an EmployeeID value that is IN the list produced by the subquery have their data row displayed in the final result table.

Data Type Join Compatibility

In addition to concerns about the domain of values returned from a subquery, the data type of the returned column value(s) must be join compatible. Join compatible data types are data types that are automatically converted when matching data in criteria conditions. For example, it would not make any sense to compare the values stored in the EmployeeID column of the employee table to values stored in BirthDate column of the dependent table because the EmployeeID column is of data type CHAR and the BirthDate column is of data type DATE.

As we learned previously, there are a fairly small number of Oracle data types, but these data types enable you to store all types of data. Although Oracle data types vary somewhat from those specified by the ANSI standard for SQL, the Oracle server maps the ANSI standard data types to Oracle's data types. For example, the Oracle Server will automatically convert among any of the following ANSI numeric data types when making comparisons of numeric values because they all map into the Oracle NUMBER data type:

• 
  int (integer)
  
• 
  smallint (small integer)
  
• 
  decimal
  
• 
  float
  

Remember that Oracle does not make comparisons based on column names. Columns from two tables that are being compared may have different names as long as they have a shared domain and the same data type or convertible data types.

Join Compatible Data Columns

Table 7.1 shows columns from two different tables with different column names. Both of these columns store student identification values. They are join compatible because they store the same data type.

Table 7.1

Column Name	Data Type
StudentNumber	CHAR
StudentID	CHAR

Not Join Compatible

Table 7.2 shows two columns with different types that have the same column name. Even though the column names are identical, and even though both columns store student identification values, one column is CHAR and the other is NUMBER. Because these data types are different, the Oracle server fails to process the join request—they are not join compatible.

Table 7.2

Column Name	Data Type
StudentID	CHAR
StudentID	NUMBER

Other Restrictions

There are additional restrictions for subqueries:

• 
  The DISTINCT keyword cannot be used in subqueries that include a GROUP BY clause.
  
• 
  Subqueries cannot manipulate their results internally. This means that a subquery cannot include the ORDER BY clause, the COMPUTE clause, or the INTO keyword. Although we have not covered use of the INTO keyword in earlier chapters, quite simply, the INTO keyword is used to create temporary tables.
  
• 
  Columns of a result table can only include columns from a table named in the FROM clause of the outer query—if a table name appears only in a subquery, then the result table cannot contain columns from that table.
  

SUBQUERIES and the IN Operator

IN Operator

The IN operator should be familiar to you. In earlier chapters you use it to write queries that defined row selection criteria based on the use of lists of data enclosed in parentheses. The only difference in the use of the IN operator with subqueries is that the list does not consist of hard-coded values. The query shown in SQL Example 7.5 illustrates the use of hard-coded department numbers of 1 and 7 as values of the IN keyword.
/* SQL Example 7.5 */

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

DepartmentNumber "Dept"

FROM Employee

WHERE DepartmentNumber IN (1, 7);
Last Name First Name Dept

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

Eakin Maxwell 1

Bock Douglas 1

Boudreaux Betty 7

more rows are displayed . . .
The WHERE clauses of a query that includes the IN operator used with a subquery takes the general form:
WHERE [NOT] IN (subquery)
Now let us turn to an example with a subquery where the IN keyword operates on a list of values returned by an inner subquery. In this situation, the values are not hard-coded—they can vary over time. Suppose that a manager in the human resources department requires a listing of employees that have male dependents, but not a listing of the actual dependents themselves. We can write a subquery that retrieves the EmployeeID column value from the dependent table where the dependent's gender is male. The outer query will then list employees that have an EmployeeID column value that is found in the listing produced by the subquery, as illustrated in SQL Example 7.6.
/* SQL Example 7.6 */

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

FROM Employee

WHERE EmployeeID IN

(SELECT EmployeeID

FROM Dependent

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

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

Bock Douglas

Boudreaux Beverly

Simmons Lester
Let's review the conceptual steps involved in evaluating the query. First, the subquery returns the EmployeeID column values of those employees that have male dependents from the dependent table.
/* SQL Example 7.7 */

COLUMN EmployeeID FORMAT A10;

SELECT EmployeeID

FROM Dependent

WHERE Gender = 'M';
EMPLOYEEID

----------

67555

33355

01885
There are three male dependents, and the intermediate result table produced by the subquery lists the EmployeeID column values of the employees to which these dependents belong. Next, these EmployeeID values are substituted into the outer query as the listing that is the object of the IN operator. So, from a conceptual perspective, the outer query now looks like the following:
/* SQL Example 7.8 */

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

FROM Employee

WHERE EmployeeID IN ('67555', '33355', '01885');

Last Name First Name

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

Bock Douglas

Boudreaux Beverly

Simmons Lester
The preceding queries, like many subqueries, can also be formulated as join queries. As you will recall from your earlier study of join queries, a join query will join the employee and dependent tables based on the common domain of values stored in the employee table's EmployeeID column and the dependent table's EmployeeID column. The SQL Example 7.9 illustrates this join query approach.
/* SQL Example 7.9 */

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

FROM Employee e JOIN dependent d ON (e.EmployeeID = d.EmployeeID)

WHERE d.Gender = 'M';

Last Name First Name

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

Bock Douglas

Boudreaux Beverly

Simmons Lester
Both the join and the subquery version will produce identical result tables, although the order of the rows in the result table may differ. Each query is equally correct. This begs the question, "Which approach is most appropriate?" The general, basic rules of thumb are:

• 
  Use a subquery when the result table displays columns from a single table.
  
• 
  Use a join query when the result displays columns from two or more tables.
  
• 
  Use a join query when the existence of values must be checked with the EXISTS operator—a join query may perform better than a subquery. The EXISTS operator is discussed later in this chapter.
  

NOT IN Operator

Like the IN operator, the NOT IN operator can take the result of a subquery as the operator object. Earlier we produced a listing of employees with dependents. Suppose that a human resource manager, in order to meet a government reporting requirement, requires a listing of employees who do not have any dependents. The NOT IN operator is especially good for producing this type of result table as shown in SQL Example 7.10.
/* SQL Example 7.10 */

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

FROM Employee

WHERE EmployeeID NOT IN

(SELECT EmployeeID

FROM Dependent);

Last Name First Name

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

Sumner Elizabeth

Eakin Maxwell

Webber Eugene

more rows are displayed . . .
The subquery produces an intermediate result table containing the EmployeeID column values of employees who have dependents in the dependent table. Conceptually, the outer query compares each row of the employee table against the result table. If the employee identifying number is not found in the result table produced by the inner query, then it is included in the final result table.