SQL and Relational Theory, 2nd Edition

CHAPTER 4

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: ...