Rivers vs Axis

by Maksim Gramin

It’s hard to find at least two people who would format the same SQL query in the same way. Everyone has their own style and their own arguments. And everyone is absolutely sure that this is the only right way.

That was to be expected, because of the declarative nature of SQL. In imperative program languages, we define and control the order of statements execution, and it affects how we format our code. But in SQL query we don’t know at all the order of execution in advance and it deprives us of an important reference point. As a result, we have a lot of formatting options.

But almost all of these options have one common unpleasant detail. In typography, it is called a river and it is considered to be bad typography. Let’s look at a simple query. The river (marked red) tore our query into two jagged parts and makes the code more difficult to read.

Rivers

But legendary Joe Celko in his book “Joe Celko’s SQL Programming Style” sad: let’s turn our rivers into axis. Same query but with the axis instead the river:

Axis

Let’s look at a more complicated query with subqueries:

Axis

We immediately visually detect three axis and three corresponding queries. This allows us to quickly and easily find out what this query does.

In this post I use my way to build an axis (maybe not the best), surely you can find other methods for this.

PS

There are hot Reddit discussion about rivers vs axis.

PPS

sqlfluff is amazing tool for linting and formatting your SQL queries. Unfortunately sqlfluff recognizes queries with axis like bad queries and it’s very sad. Please vote to this issue https://t.co/YArKsaqUaM

Maksim Gramin

Maksim has been working with various database sysytems for about 15 years. He was a SQL developer on big distributed teams, writing tons of nontrivial SQL-queries and implementing tons of complex and tricky business logic on the database side. And he also worked as a “classical” Java developer and architect, where people interacted with any databases through ORM’s.

For now Maksim is a database consultant and researcher.

See all posts by Maksim Gramin »

Comments

✎ Edit this page on GitHub