9. Tally Tables

In Chapter 8, “Cartesian Products,” you read about Cartesian Products and how they can provide necessary data for SQL statements.

Another useful tool is the tally table, usually a table with a single column of sequential numbers, with values starting from 1 (or 0) to a maximum number appropriate for the situation. It can also be a single column of sequential dates that cover a range of interest, or something more complex to aid in “pivoting” a set of summaries. These allow us to solve problems that cannot be solved with Cartesian Products because Cartesian Products are dependent on actual values in the underlying tables, whereas tally tables cover all possibilities. In this chapter we show you examples of such problems and how ...

Get Effective SQL: 61 Specific Ways to Write Better SQL, First Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.