Database Redesign - Database Processing: Fundamentals, Design, and Implementation - page 370

In these expressions, W1.Title ( from the top SELECT statement) is being compared
with W2.Title ( from the bottom SELECT statement). The same is true for W1.WorkID and
W2.WorkID. Because of this fact, the DBMS cannot process the subquery portion independent
of the upper SELECT.
Instead, the DBMS must process this statement as a subquery that is nested within the
main query. The logic is as follows: Take the first row from W1. Using that row, evaluate the
second query. To do that, for each row in W2, compare W1.Title with W2.Title and W1.WorkID
with W2.WorkID. If the titles are equal and the values of WorkID are not equal, return the value
of W2.Title to the upper query. Do this for every row in W2.
Once all of the rows in W2 have been evaluated for the first row in W1, move to the second
row in W1 and evaluate it against all the rows in W2. Continue in this way until all rows of W1
have been compared with all of the rows of W2.
If this is not clear to you, write out two copies of the WORK data from Figure 7-16(d) on
a piece of scratch paper. Label one of them W1 and the second W2, and then work through
the logic as described. From this, you will see that correlated subqueries always require nested
processing.

A Common Trap
By the way, do not fall into the following common trap:

/* *** SQL-Query-CH08-03 *** */
SELECT W1.Title, W1.Copy

FROM WORK AS W1

WHERE W1.WorkID IN

(SELECT W2.WorkID

FROM WORK AS W2

WHERE W1.Title = W2.Title

AND W1.WorkID <> W2.WorkID);

The logic here seems correct, but it is not. Compare SQL-Query-CH08-03 to SQL-Query-CH08-02,
and note the differences between the two SQL statements. The result of SQL-Query-CH08-03
when run on the View Ridge Gallery data in Figure 7-16(d) is an empty set:

In fact, no row will ever be displayed by this query , regardless of the underlying data (see if you
can figure out why this is so before continuing to the next paragraph).
The bottom query will indeed find all rows that have the same title and different WorkIDs.
If one is found, it will produce the W2.WorkID of that row. But that value will then be com-
pared with W1.WorkID. These two values will always be different because of the condition

W1.WorkID <> W2.WorkID

No rows are returned because the values of the two unequal WorkIDs are used in the IN
instead of the values of the two equal Titles.

Using Correlated Subqueries to Check Functional Dependencies
Correlated subqueries can be used to your advantage during database redesign. As mentioned,
one application of correlated subqueries is to verify functional dependencies. For example,
suppose we have EMPLOYEE data like that in Figure 8-1 in a database and that we want to
know whether the data conform to the functional dependency:

Department S DeptPhone