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

Using NOT EXISTS in a Double Negative
The SQL EXISTS keyword will be true if any row in the subquery meets the condition . The SQL
NOT EXISTS keyword will be true only if all rows in the subquery fail to meet the condition .
Consequently, the double use of NOT EXISTS can be used to find rows that do not not match a
condition . And, yes, the word not is supposed to be there twice—this is a double negative .
Because of the logic of a double negative, if a row does not not match any row , then it
matches every row ! For example, suppose that at View Ridge the users want to know the name
of any artist that every customer is interested in. We can proceed as follows:

● First, produce the set of all customers who are interested in a particular artist.
● Then take the complement of that set, which will be the customers who are not
interested in that artist.
● If that complement is an empty set, then all customers are interested in the given artist.

By ThE WAy The doubly nested NOT EXISTS pattern is famous in one guise or another
among SQL practitioners. It is often used as a test of SQL knowledge in
job interviews and in bragging sessions, and it can be used to your advantage when
assessing the desirability of certain database redesign possibilities, as you will see in
the last section of this chapter. Therefore, even though this example involves some
serious study, it is worth your while to understand it.

The Double NOT EXISTS Query
The following SQL statement implements the strategy just described:

/* *** SQL-Query-CH08-06 *** */
SELECT A.FirstName, A.LastName

FROM ARTIST AS A

WHERE NOT EXISTS

(SELECT C.CustomerID

FROM CUSTOMER AS C

WHERE NOT EXISTS

(SELECT CAI.CustomerID

FROM CUSTOMER_ARTIST_INT AS CAI

WHERE C.CustomerID = CAI.CustomerID

AND A.ArtistID = CAI.ArtistID));

The result of this query is an empty set, indicating that there is no artist that every customer is
interested in:

Let's see how this works. The bottom SELECT (the third SELECT in the SQL statement)
finds all of the customers who are interested in a particular artist. As you read this SELECT
(the last SELECT in the query), keep in mind that this is a correlated subquery; this SELECT
is nested inside the query on CUSTOMER, which is nested inside the query on ARTIST.
C.CustomerID is coming from the SELECT on CUSTOMER in the middle, and A.ArtistID is
coming from the SELECT on ARTIST at the top.
Now the NOT EXISTS in the sixth line of the query will find the customers who are not
interested in the given artist. If all customers are interested in the given artist, the result of