SQL and Relational Theory, 2nd Edition

WHAT ABOUT ORDER BY?

The last topic I want to address in this chapter is ORDER BY (just ORDER, in Tutorial D). Now, despite the title of this chapter, ORDER BY isn’t actually part of the relational algebra; in fact, as I pointed out in Chapter 1, it isn’t a relational operator at all, because it produces a result that isn’t a relation (it does take a relation as input, but it produces something else—namely, a sequence of tuples—as output). Please don’t misunderstand me here; I’m not saying ORDER BY isn’t useful; however, I am saying it can’t sensibly appear in a relational expression^[110] (unless it’s treated simply as a “no op,” I suppose). By definition, therefore, the following expressions, though legal, aren’t relational expressions as such:

That said, I’d like to point out that for a couple of reasons ORDER BY is actually a rather strange operator. First, it effectively works by sorting tuples into some specified sequence—and yet “<“ and “>“ aren’t defined for tuples, as we know from Chapter 3.^[111] Second, it’s not a function. All of the operators of the relational algebra described in this book—in fact, all read-only operators, as that term is usually understood—are functions, meaning there’s always just one possible output for any given input. By contrast, ORDER BY can produce several different outputs from the same input. As an illustration of this point, consider the effect ...