4.1 To deal with this argument properly would take more space than we have here, but it all boils down to what’s sometimes called The Principle of Identity of Indiscernibles (see Appendix A). Let a and b be any two entities—for example, two pennies. Well, if there’s absolutely no way whatsoever of distinguishing between a and b, then there aren’t two entities but only one! Now, it might be true for certain purposes that the two entities can be interchanged, but that fact isn’t sufficient to make them indiscernible (there’s a logical difference between interchangeability and indiscernibility, in fact, and arguments to the effect that “duplicates occur naturally in the real world” tend to be based on a muddle over this difference). A detailed analysis of this whole issue can be found in the paper “Double Trouble, Double Trouble” (see Appendix G).

4.2 Before we can answer this question, we need to pin down exactly what WHERE and UNION mean in the presence of duplicates. The paper “The Theory of Bags: An Investigative Tutorial” (see Appendix G) goes into details on such matters; here let me just say that if we adopt the SQL definitions, then the law certainly doesn’t apply. In fact, it doesn’t apply to either UNION ALL or UNION DISTINCT! By way of example, let T be an SQL table with just one column—C, say—containing just two rows, each of them containing just the value v. Then the following expressions produce the indicated results:

     SELECT C
     FROM   T
     WHERE  TRUE
     OR     TRUE

Result: v * 2.

     SELECT C
     FROM   T
     WHERE  TRUE
     UNION  DISTINCT
     SELECT C
     FROM   T
     WHERE  TRUE

Result: v * 1.

     SELECT C
     FROM   T
     WHERE  TRUE
     UNION  ALL
     SELECT C
     FROM   T
     WHERE  TRUE

Result: v * 4.

Note: If the various (implicit or explicit) ALLs in the foregoing expressions were all replaced by DISTINCT, it would be a different story. What do you conclude?

4.3 Remarks similar to those in the answer to the previous exercise apply here also. Again I’ll skip the details; I’ll just say for the record that, first, the answer depends, of course, on what definitions we adopt for UNION and INTERSECT for bags as opposed to sets; second, with the SQL definitions, the law doesn’t apply. I’ll leave development of a counterexample to you.

4.4 As far as I can see, the only way to resolve the ambiguity is by effectively defining a mapping from each of the (multiset) argument tables to a proper set, and likewise defining a mapping of the (multiset) result table—i.e., the desired cartesian product—to a proper set. (The mappings involve attaching a unique identifier to each row.) It seems to me, in fact, that the standard’s failed attempt at a definition here serves only to emphasize the point that one of the most fundamental concepts in the entire SQL language (viz., the idea that tables should permit duplicate rows) is fundamentally flawed—and cannot be repaired without, in effect, dispensing with the concept altogether.

4.5 I don’t think the problem can be fixed.

4.6 No answer provided!

4.7 The question was: Do you think nulls occur naturally in the real world? Only you can answer this question—but if your answer is yes, I think you should examine your reasoning very carefully. For example, consider the statement “Joe’s salary is $50,000.” That statement is either true or false. Now, you might not know whether it’s true or false; but your not knowing has nothing to do with whether it actually is true or false. In particular, your not knowing is certainly not the same as saying “Joe’s salary is null”! “Joe’s salary is $50,000” is a statement about the real world. “Joe’s salary is null” is a statement about your knowledge (or lack of knowledge, rather) of the real world. We certainly shouldn’t keep a mixture of these two very different kinds of statements in the same relation, or in the same relvar.

Suppose you had to represent the fact that you don’t know Joe’s salary in some box on some paper form. Would you enter a null, as such, into that form? I don’t think so! Rather, you would leave the box blank, or put a question mark, or write “unknown,” or something along those lines. And that blank, or question mark, or “unknown”—or whatever—is a value, not a null (recall that the one thing we can be definite about regarding nulls is that they aren’t values). Speaking for myself, therefore, no, I don’t think nulls do “occur naturally in the real world.”

4.8 True (though not in SQL!). Null is a marker that represents the absence of information, while UNKNOWN is a value, just as TRUE and FALSE are values. So there’s a logical difference between the two, and to confuse them as SQL does is a logical mistake (I’d like to say it’s a big logical mistake, but all logical mistakes are big by definition).

4.9 Yes, it does; SQL’s analog of MAYBE p is p IS UNKNOWN.

4.10 In 2VL there are exactly 4 monadic connectives and exactly 16 dyadic connectives, corresponding to the 4 possible monadic truth tables and 16 possible dyadic truth tables. Here are those truth tables (I’ve indicated the ones that have common names, such as NOT, AND, and OR):^[198]

In 3VL, by contrast, there are 27 (3 to the power 3) monadic connectives and 19,683 (3 to the power 3²) dyadic connectives. (In general, in fact, nVL has n to the power n monadic connectives and n to the power n² dyadic connectives.) Many conclusions might be drawn from these facts; one of the most immediate is that 3VL is vastly more complex than 2VL (much more so, probably, than most people, including those who think nulls are a good thing, realize, or at least admit to).

4.11 Classical 2VL supports (among other things) NOT, AND, and OR and is thus truth functionally complete, because all possible 2VL connectives can be expressed in terms of NOT and either AND or OR (see the answer to Exercise 10.4 later in this appendix for further explanation). And it turns out that SQL’s 3VL—under an extremely charitable interpretation of that term!—is also truth functionally complete. The paper “Is SQL’s Three-Valued Logic Truth Functionally Complete?” (see Appendix G) discusses this issue in detail.

4.12 It’s not a tautology in 3VL, because if bx evaluates to UNKNOWN, the whole expression also evaluates to UNKNOWN. But there does exist an analogous tautology in 3VL: viz., bx OR NOT bx OR MAYBE bx. Note: This state of affairs explains why, in SQL, if you execute the query “Get all suppliers in London” and then the query “Get all suppliers not in London,” you don’t necessarily get (in combination) all suppliers; you have to execute the query “Get all suppliers who may be in London” as well. Note the implications for query rewrite; note too the potential for serious mistakes (on the part of both users and the system, I might add—and there’s some history here). To spell the point out: It’s very natural to assume that expressions that are tautologies in 2VL are also tautologies in 3VL, but such is not necessarily the case.

4.13 It’s not a contradiction in 3VL, because if bx evaluates to UNKNOWN, the whole expression also evaluates to UNKNOWN. But there does exist an analogous (slightly tricky!) contradiction in 3VL: viz., bx AND NOT bx AND NOT MAYBE bx. Note: As you might expect, this state of affairs has implications similar to those noted in the answer to the previous exercise.

4.14 In 3VL (at least as realized in SQL), r JOIN r isn’t necessarily equal to r, and INTERSECT isn’t a special case of JOIN. Why so? Because in SQL, believe it or not, two nulls don’t “compare equal” for join but do “compare equal” for intersection. (I take this state of affairs to be just another of the vast—infinite?—number of absurdities that nulls inevitably seem to lead us into.) However, TIMES is still a special case of JOIN, as it is in 2VL.

4.15 Here are the rules: Let x be an SQL row. Suppose for definiteness and simplicity that x has just two components, x1 and x2 (in left to right order, of course!). Then x IS NULL is defined to be equivalent to x1 IS NULL AND x2 IS NULL, and x IS NOT NULL is defined to be equivalent to x1 IS NOT NULL AND x2 IS NOT NULL. For the given row, both of these expressions evaluate to FALSE, and it follows that the row in question is neither null nor nonnull ... What do you conclude from this state of affairs?

By the way: At least one reviewer commented at this point that he’d never thought of a row being null. But rows are values (just as tuples and relations are values), and hence the idea of some row being unknown makes exactly as much sense as, say, the idea of some salary being unknown. Thus, if the concept of representing an unknown value by a “null” makes any sense at all—which of course I don’t think it does—then it surely applies to rows (and tables, and any other kind of value you can think of) just as much as it does to scalars. And as this exercise demonstrates, SQL tries to support this position—at least for rows—but fails. (Of course, it ought logically to support it for tables, too, but in that case it doesn’t even try. I mean, there’s no such thing as a “null table” in SQL.)

4.16 No. Here are the truth tables:

4.17 No. For definiteness, consider the case in which x is an SQL row. Suppose (as in the answer to Exercise 4.15 above) that x has just two components, x1 and x2. Then x IS NOT NULL is defined to be equivalent to x1 IS NOT NULL AND x2 IS NOT NULL, and NOT (x IS NULL) is defined to be equivalent to x1 IS NOT NULL OR x2 IS NOT NULL. What do you conclude from this state of affairs?

4.18 The transformation isn’t valid, as you can see by considering what happens if EMP.DNO is null (were you surprised?). The implications, once again, are that users and the system are both likely to make mistakes (and again there’s some history here).

4.19 The query means “Get suppliers who are known not to supply part P2” (note that known not, and note also the subtle difference between that phrase and not known); it does not mean “Get suppliers who don’t supply part P2.” The two formulations aren’t equivalent (consider, e.g., the case where the only SP row for part number P2 in table SP has a null supplier number).

4.20 No two of the three statements are equivalent. Statement a. follows the rules of SQL’s 3VL; statement b. follows the definition of SQL’s UNIQUE operator; and statement c. follows SQL’s definition of duplicates. In particular, if k1 and k2 are both null, then a. gives UNKNOWN, b. gives FALSE, and c. gives TRUE (!). Here for the record are the rules in question:

Note: Throughout the foregoing, “equal” refers to SQL’s own, somewhat idiosyncratic definition of the “=” operator (see Chapter 2). Subsidiary exercise: Do you think these rules are reasonable? Justify your answer.

4.21 The output from INTERSECT ALL and EXCEPT ALL can indeed contain duplicates, but only if duplicates are present in the input; unlike UNION ALL, therefore, these two operators never “generate” duplicates.

4.22 Yes! (We don’t want duplicates in the database, but that doesn’t mean we never want duplicates anywhere else. As I said in the body of the chapter, there’s a logical difference between logic and rhetoric.)

4.23 A very good question.

4.24 Well, I don’t know about you, but I have quite a few comments myself!

Finally, the six SQL expressions (or would-be expressions):

4.25 No answer provided.