SQL and Relational Theory, 3rd Edition

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

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

  1. Duplicate rows

  2. Nulls

  3. Left to right column ordering

  4. Anonymous columns

  5. Duplicate column names

  6. Pointers

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