Literate SQL

Literate programming is an approach to programming introduced by Donald Knuth that, among other ideas, aims to arrange source code in order of human logic so that it can be understood by reading it like a novel: from the beginning to the end.

SQL works in the opposite way: due to the nesting of subqueries we must find and understand the innermost query and then start working from inside out. Not even well-chosen names can improve the recursive reading process notably because names follow the subqueries in SQL.

Even the order of clauses in the simplest query has a forward dependency: the meaning of the select clause depends on the from clause. That actually poses an unsolvable problem for development tools: when typing select, it is not yet know which tables are accessed and it is therefore also unknown which columns are visible. Without from no sensible code completion for select.

SQL:1999 introduced an alternative to the nesting at least: the with clause. In context of literate SQL, the with clause has two important properties: (1) names come first; (2) subqueries can be unnested.

Names first

The importance of meaningful names for software elements cannot be overstated. But even the best, intention revealing name, is useless, if it cannot be seen when needed:

SELECT ...
  FROM (SELECT ...
          FROM ...
       ) intention_revealing_name
   ...

The longer the inner query gets, the more useful a good name becomes, and the further it is pushed out of sight. How tragic.

The with clause puts the name before the code—very much like function declarations work in other programming languages:

WITH intention_revealing_name AS (
     SELECT ...
       FROM ...
     )
SELECT ...
  FROM intention_revealing_name irn
   ...

The topping is that it is still possible to assign a short alias in the from clause (like irn in the above example).

Order of human logic

The second aspect of with is its ability to turn nesting into chaining. This is the way to make complex queries top-down readable.

A with clause can define many queries. Each of them can refer to other queries previously defined in the same with clause. This is exactly what is needed for literate SQL.

The following example drafts a literate query. The first two query definitions are not even shown—their name alone reveals their purpose:

WITH frequently_bought_together (ordered, suggested, times) AS (
     SELECT ...
     )
   , frequently_bought_shortly_after (ordered, suggested, times) AS (
     SELECT ...
     )
   , product_recommendation (ordered, suggested, times) AS (
     SELECT ordered, suggested, SUM(times)
       FROM (SELECT * FROM frequently_bought_together
              UNION ALL
             SELECT * FROM frequently_bought_shortly_after
            ) frequently_needed_together
      GROUP BY ordered, suggested
      ORDER BY SUM(times) DESC
      FETCH FIRST 1 ROW ONLY 
     )
SELECT *
  FROM product_recommendation

The ease of reading is not caused by the top-down structure alone. With meaningless names like x, y, and z the query would still be hard to understand. It is the combination of good names and good structure that brings the literate breakthrough.

As often, it is possible to do too much of it. Small subqueries like frequently_needed_together in the example might not hinder readability. It is important to divide the code into manageable, logically connected units.

Then again it sometimes makes sense to use with clause inside a with query. You are not limited to a global spaghetti with clause: syntactically, with is a prefix for the select keyword. Use it where it helps improving readability.

By now it should be clear why this technique is called “literate SQL”: writing easily readable SQL is an art, not science.

Compatibility

1. Seems like CTE in subquery cannot see global CTEs