Appendix B
SQL Departures from the Relational Model
Between the idea
And the reality ...
Falls the Shadow
—T. S. Eliot: The Hollow Men (1925)
In this appendix I summarize, mainly for purposes of reference and with little by way of additional commentary, some of the ways in which SQL—by which I mean, as always in this book, the standard version of that language, except where otherwise noted—departs from the relational model. Now, I know there are those who will quibble over specific items in what follows; it’s not easy to compile a list of this kind, especially if it’s meant to be orthogonal (i.e., if an attempt is made to keep the various items all independent of one another). But I don’t think such quibbling is important. What’s important is the cumulative effect, which quite frankly I think is overwhelming.1
SQL fails to distinguish adequately between table values and table variables.
SQL tables aren’t the same as relations (or relvars), because they either permit or require, as the case may be:
Duplicate rows
Nulls
Left to right column ordering
Anonymous columns
Duplicate column names
Pointers
Hidden columns (at least in some products, though not in the standard as such)
Note that all of these differences constitute violations of The Information Principle (see Appendix A).
SQL has no proper table literals.
SQL often seems to think views aren’t tables.
SQL tables—views included!—must have at least one column (no support for TABLE_DEE and TABLE_DUM).
SQL has no support for empty rows or empty keys. (In fact, SQL suffers from numerous defects in connection with empty sets, as documented in Chapter 12.)
SQL has no explicit table assignment operator.
SQL has no explicit multiple table assignment a fortiori (nor does it have an INSERT / DELETE analog).
SQL violates The Assignment Principle in numerous different ways (some but not all of them having to do with nulls).
SQL violates The Golden Rule in numerous different ways (some but not all of them having to do with nulls).
SQL has no proper “table type” notion. As a consequence, its support for table type inference (i.e., determining the type of the result of some table expression) is very incomplete.
SQL has no “=” operator for tables; in fact, it has no proper table comparison operators, as such, at all.
SQL supports “reducible keys” (i.e., it allows proper superkeys to be declared as keys).
SQL’s union, intersection, and join operators aren’t commutative.
SQL’s union, intersection, and join operators aren’t idempotent.
SQL’s intersection operator isn’t a special case of SQL’s natural join operator.
SQL has no proper aggregate operators.
Numerous SQL operators are “possibly nondeterministic.”
SQL supports various row level operators (cursor updates, row level triggers).
Although the SQL standard doesn’t, the dialects of SQL supported in various commercial products do sometimes refer to certain storage level constructs (e.g., indexes).
SQL’s view definitions include mapping information as well as structural information.2
SQL’s support for view updating is weak, ad hoc, and incomplete.
SQL fails to distinguish properly between types and representations.
SQL’s “structured types” are sometimes encapsulated and sometimes not. (This issue wasn’t discussed in the body of this book.)
SQL fails to distinguish properly between types and type generators.
Although the SQL standard does support type BOOLEAN, commercial SQL products typically don’t.
SQL’s support for “=” is seriously deficient. To be more specific, SQL’s “=” operator:
Can give TRUE even when the comparands are clearly distinct3
Can fail to give TRUE even when the comparands aren’t clearly distinct
Can have user defined, and hence arbitrary, semantics (for user defined types)
Isn’t supported at all for the system defined type XML
In some products, isn’t supported for certain other types as well
SQL is based on three-valued logic (sort of), whereas the relational model is based on two-valued logic.
SQL isn’t relationally complete.
The foregoing list is not exhaustive.