Consider the following supplier and parts scenario (adapted from C. Date, 2005, Database in Depth: Relational Theory for Practitioners): A company has two suppliers S1 and S2. S1 is located in New York and supplies part P1, which is produced in Bangalore. Supplier S2, located in Berlin, supplies part P2 which is also produced in Bangalore. Assume further that the company introduces a new supplier policy which states that if the supplier is located in Paris, then its parts must be produced in a city different from Paris. So the DB admin makes a mental note that whenever he adds a supplier from Paris he must check that the parts are not produced in Paris. Soon after the introduction of this policy the company closes a contract with a new supplier S3 based in Berlin, which is going to supply part P3. But incidentally, the supplier forgot to mention where the production site of P3 is. Since the supplier is not from Paris, the supplier policy will not be violated irrespective of where the part P3 will be produced. So the DB admin specifies the location of S3 as Berlin and leaves the location of P3 as NULL. His supplier and parts tables are now as following:
SUPPLIER:
S_ID LOCATION
S1 New York
S2 London
S3 Berlin
PARTS:
P_ID LOCATION S_ID
P1 Bangalore S1
P2 Bangalore S2
P3 NULL S3
A few days later the DB admin is asked to produce a list of all suppliers whose parts location is not Paris or the parts location differs from the supplier location. He writes the following SELECT statement
SELECT s_id
FROM supplier, parts
WHERE supplier.s_id = parts.s_id AND
(parts.location <> ‘Paris’ OR parts.location <> supplier.location)
and reasons as follows: S1 supplies P1 located in Bangalore, which differs from Paris, so the first disjunct part.location <> ‘Paris’ evaluates to true, so the WHERE clause is true of S1 and P1, and consequently S1 gets listed. S2 supplies P2 which is also located in Bangalore, so again the first disjunct part.location <> ‘Paris’ is true, so the WHERE clause is true of S2 and P2, and consequently S2 also gets listed. Finally, the DB admin reasons, the location of part P3 either is or isn’t Paris. If it isn’t Paris, then again the first disjunct is true, so the WHERE clause would be true of S3 and P3. And if the location of P3 is Paris, then it differs from the supplier S3 location, which is Berlin, so the second disjunct is true. Therefore the WHERE clause would be true whatever the actual location of part P3. So he runs the query, expecting to see all three suppliers S1, S2 and S3. But to his surprise S3 is not listed.
After thinking about this for a moment, the DB admin smacks his forehead, and breathes a sigh of relief. ‘Of course’, he says, ‘S3 should not be listed’. Since the location of P3 is NULL, the disjunct parts.location <> ‘Paris’ will evaluate to UNKNOWN (or whatever you choose to call the third truth value), and the same goes for parts.location <> supplier.location. Consequently the WHERE clause evaluates to UNKNOWN for the tuple <S3, Berlin, P3, NULL, S3>, and therefore S3 won’t be listed in the result set. But it’s much harder to avoid such mistakes.
The reason for this is that the way we think is not the way that SQL ‘thinks’. For us, either there is water on Mars, or there isn’t (there is no third possibility), either the production site of part P3 is Paris or it isn’t. For SQL, however, there is a third way! Using three-valued logic means among other things that we allow for a statement to be neither true nor false. And therefore, we are not (!) allowed to assume that the production site of P3 either is or isn’t Paris. To avoid his mistake, the DB admin should have tested also for the third case, where the truth value of ‘The production site of P3 is Paris’ is neither TRUE nor FALSE. And then he would have realized that in this case the WHERE clause is not true of S3 and P3.
The moral of the story: As long as SQL is based on three-valued logic, we also need to think according to the laws of three-valued logic. Questions which presuppose two-valued logic should not be formulated as queries which presuppose three-valued logic. If we do so, we shouldn’t be surprised if the results are counter-intuitive.