Database Reference
In-Depth Information
Then we take the value of Title in the second row and compare it with all other rows in the
WORK table, printing out the Title and Copy of any duplicate works. We proceed in this way
until all rows of WORK have been examined.
A Correlated Subquery That Finds Rows with the Same Title
The following correlated subquery performs the action just described:
/* *** SQL-Query-CH08-02 *** */
SELECT
W1.Title, W1.Copy
FROM
WORK AS W1
WHERE
W1.Title IN
(SELECT W2.Title
FROM
WORK AS W2
WHERE
W1.Title = W2.Title
AND W1.WorkID <> W2.WorkID);
The result of this query for the data in Figure 7-16(d) is:
Looking at these results, it is easy to see the nonunique, duplicated Title data that prevents
Title from being used as an alternate key. When you are interpreting these results, note that a
value of
Unique
in the Copy column indicates the original piece of art itself, which is by defini-
tion unique. Numbers such as
142/500
indicate one numbered print from a set of numbered
reproduction prints of that artwork.
This subquery, which is a correlated subquery, looks deceptively similar to a regular, non-
correlated subquery. To the surprise of many students, this subquery and the one above are
drastically different. Their similarity is only superficial.
Before explaining why, first notice the notation in the correlated subquery. The WORK
table is used in both the upper and the lower SELECT statements. In the upper statement, it is
given the alias W1; in the lower SELECT statement, it is given the alias W2.
In essence, when we use this notation, it is as if we have made two copies of the WORK table.
One copy is called W1, and the second copy is called W2. Therefore, in the last two lines of the
correlated subquery, values in the W1 copy of WORK are compared with values in the W2 copy.
What Is the Difference Between Regular and Correlated Subqueries?
Now consider what makes this subquery so different. Unlike with a regular, noncorrelated sub-
query, the DBMS cannot run the bottom SELECT by itself, obtain a set of Titles, and then use
that set to execute the upper query. The reason for this appears in the last two lines of the query:
WHERE
W1.Title = W2.Title
AND
W1.WorkID <> W2.WorkID);
Search WWH ::
Custom Search