Inside a relational database management system, the principal persisted data structure is considered a logical relation. Operations performed against that data within the RDBMS result in a logical relation too. In other words, everything is a table. To step away a little from the term “table,” let’s use the word “grid.” Tables are grids, similar to spreadsheets that have rows, columns, and headers. In dealing with a relational database, everything is a grid. Obviously, tables are grids, views are grids, and the result of any query is a grid. This grid-consistency is an aspect of a term called “relational closure” supported by the DBMS. Relational closure is one of those characteristics that define the database management system as truly relational, and the most fun aspect of relational closure is that “grids are everything.” There are other aspects of relational closure concerned with having the minimum amount of redundancy, but those other aspects are not nearly as much fun.
Everything is a Grid
Since everything is a grid, any grid can serve in place of any grid. Tables, views, and queries may be blurred together. This facet is why a view may be described as simply a “named” query. As one composes SQL Select statements, the author may use another query in place of any table or view. Even individual columns may be replaced with queries of varying complexity—if the result fits in data-type-wise. These replaced columns could be those within a Select clause, or those within a Where clause—anywhere one might find a column or a value. Because of this easy replacement, any query can be built up infinitely, similar to layering bricks in a wall. The wall is yet another grid comprised of bricks that are also grids themselves. Larger grids can consume sizable chunks, or any element can be sub-divided into more and more tiny grids. Queries can easily be wrapped around a new query. In doing this wrapping and replacing of columns and tables, many pages worth of SQL can be strung out to create an extraordinarily intricate single statement whenever desired. However, this practice of instantiating complex SQL is not widely recommended.
Weaving a large web of interlocked SQL is a powerful way to exploit the data within an RDBMS. Spinning such code can be used for good, such as when providing a very customized solution to a user’s awkward data request. And this same power can be used for ill, such as when creating SQL statements that are so wild and deeply layered that trying to unsnarl things to make a small change requires some heavy-duty analysis and SQL skills. Often both the good and the ill arrive together.
Benefits of the Longer Route
Of course, many databases do have a limit on the length of SQL statements, but you might be surprised how far one can go. This characteristic of relational closure is useful when the data needs are both specific and complex. However, engineers should use caution when considering jumping down this rabbit hole. If one finds oneself playing Beowulf to a Grendel-like query of many queries sewn together, ripping off an arm is still a good approach.
You must step through deconstructing the larger query into the smaller query elements that comprise it, and then put it back together and, as you go, identify the places that changes will need to be made. A detailed analysis in this fashion helps the developer come to an understanding of the many things happening within the monster query. Alternatively, the initial coder may choose to keep many of the elements separate, and iterate through steps one-by-one, perhaps using temporary tables along the way to hold intermediary results. Although stepping through the needed logic in this fashion may be a bit more tedious, this longer route allows for a slightly easier maintenance path for others to follow.