SQL and Relational Theory

Chapter 4. No Duplicates, No Nulls

IN THE PREVIOUS CHAPTER, I SAID THE FOLLOWING (APPROXIMATELY):

• Relations never contain duplicate tuples, because the body of a relation is a set (a set of tuples) and sets in mathematics don’t contain duplicate elements.

• Relations never contain nulls, because the body of a relation is a set of tuples, and tuples in turn never contain nulls.

I also suggested that since there was so much to be said about these topics, it was better to devote a separate chapter to them. This is that chapter. Note: By definition, the topics in question are SQL topics, not relational ones; in what follows, therefore, I’ll use the terminology of SQL rather than that of the relational model (for the most part, at any rate). As you’ll soon see, I’ll also adopt a simplified, though I hope self-explanatory, shorthand notation for rows.

What’s Wrong with Duplicates?

There are numerous practical arguments in support of the position that duplicate rows (“duplicates” for short) should be prohibited. Here I want to emphasize just one—but I think it’s a powerful one.^[31] However, it does rely on certain notions I haven’t discussed yet in this book, so I need to make a couple of preliminary assumptions:

1. I assume you know that relational DBMSs include a component called the optimizer, whose job is to try to figure out the best way to implement user queries and the like (where “best” basically means best performing).

2. I assume you also know that one of the things optimizers do is what’s ...