A query example this time—“Get part names for parts whose weight is different from that of every part in Paris.” Here’s a straightforward logical (i.e., relational calculus) formulation:

     { PX.PNAME } WHERE FORALL PY ( IF PY.CITY = 'Paris'
                                    THEN PY.WEIGHT ≠ PX.WEIGHT )

This expression can be interpreted as follows: “Get PNAME values from parts PX such that, for all parts PY, if PY is in Paris, then PY and PX have different weights.” Note that I use the terms where and such that interchangeably—whichever seems to read best in the case at hand—when I’m giving natural language interpretations like the one under discussion.

As a first transformation, let’s apply the quantification law:

     { PX.PNAME } WHERE NOT EXISTS PY ( NOT ( IF PY.CITY = 'Paris'
                                              THEN PY.WEIGHT ≠ PX.WEIGHT ) )

Next, apply the implication law:

     { PX.PNAME } WHERE
                  NOT EXISTS PY ( NOT ( NOT ( PY.CITY = 'Paris' )
                                        OR ( PY.WEIGHT ≠ PX.WEIGHT ) ) )

Apply De Morgan:

     { PX.PNAME } WHERE
                  NOT EXISTS PY ( NOT ( NOT ( ( PY.CITY = 'Paris' )
                                   AND NOT ( PY.WEIGHT ≠ PX.WEIGHT ) ) ) )

Tidy up, using the double negation law, plus the fact that NOT (a ≠ b) is equivalent to a = b:

     { PX.PNAME } WHERE NOT EXISTS PY ( PY.CITY = 'Paris' AND
                                        PY.WEIGHT = PX.WEIGHT )

Map to SQL:

     SELECT DISTINCT PX.PNAME
     FROM   P AS PX
     WHERE  NOT EXISTS
          ( SELECT *
            FROM   P AS PY
            WHERE  PY.CITY = 'Paris'
            AND    PY.WEIGHT = PX.WEIGHT )

Incidentally, that DISTINCT is really needed in the opening SELECT clause here! Here’s the result:^[157]

Unfortunately, there’s a fly in the ointment in this example. Suppose there’s at least one part in Paris, but all such parts have a null weight. Then we simply don’t know—we can’t possibly say—whether there are any parts whose weight is different from that of every part in Paris; the query is unanswerable. But SQL gives us an answer anyway ... To be specific, the subquery following the keyword EXISTS evaluates to an empty table for every part PX represented in P; the NOT EXISTS therefore evaluates to TRUE for every such part PX; and the expression overall therefore incorrectly returns all part names in table P.

What’s more, not only is the foregoing SQL result incorrect, but any definite result would represent, in effect, a lie on the part of the system. To say it again, the only logically correct result is “I don’t know”—or, to be more precise and a little more honest about the matter, “The system doesn’t have enough information to give a definitive response to this query.”

What makes matters even worse is that under the same conditions as before (i.e., if there’s at least one part in Paris and those parts all have a null weight), the SQL expression

     SELECT DISTINCT PX.PNAME
     FROM   P AS PX
     WHERE  PX.WEIGHT NOT IN
          ( SELECT PY.WEIGHT
            FROM   P AS PY
            WHERE  PY.CITY = 'Paris' )

—which looks as if it ought to be logically equivalent to the one shown previously (and indeed is so, in the absence of nulls)—will return an empty result: a different, though equally incorrect, result.

The moral is obvious: Avoid nulls!—and then the transformations all work properly.