WITH statements

Because the WITH statement works like having a temporary table, the formatting will follow rules similar to the CREATE TABLE statement.

• Between the WITH and the alias there is a tab followed by the word AS and a carriage return
• The round brackets surrounding the inner query indent one tab from the alias
• The inner query indents one tab from the round brackets

Conclusions

This coding style is at first sight complex and with some drawback as it can slow down the coding process because of more keystrokes required for the uppercase and the carriage return. However, when the writing automatism is acquired the coding speed is very fast.

This coding style creates the query using a top down structure where the different part of the query are indented in order to reflecting their level inside the query’s logic. This increases the query readability in particular when dealing with complex queries.

The vertical block structure gives great control on the query’s sub sections. With query editors supporting the partial code execution (e.g. pgadmin3’s query ) is possible to highlight the query’s single sections and execute them immediately.

I developed this style from the induction I received in my first DBA job. My employer enforced along the developers and DBA a similar set of rules which worked perfectly, improving the produc- tivity and the code’s consistency across the company.

Incidentally this matches almost perfectly the way PostgreSQL processes a select. What follow is an excerpt of the PostgreSQL’ manual describing how the select is processed.

SELECT retrieves rows from zero or more tables.
The general processing of SELECT is as follows:

1. All queries in the WITH list are computed.
These effectively serve as temporary tables that can be referenced in the FROM list.
A WITH query that is referenced more than once in FROM is computed only once.

2. All elements in the FROM list are computed.
(Each element in the FROM list is a real or virtual table.)
If more than one element is specified in the FROM list, they are cross-joined together.

3. If the WHERE clause is specified, all rows that do not satisfy the condition
are eliminated from the output.

4. If the GROUP BY clause is specified, or if there are aggregate function calls,
the output is combined into groups of rows that match on one or more values,
and the results of aggregate functions are computed.
If the HAVING clause is present, it eliminates groups that do not satisfy the
given condition.

5. The actual output rows are computed using the SELECT output expressions
for each selected row or row group.

6. SELECT DISTINCT eliminates duplicate rows from the result.
SELECT DISTINCT ON eliminates rows that match on all the specified expressions.
SELECT ALL (the default) will return all candidate rows, including duplicates.

7. Using the operators UNION, INTERSECT, and EXCEPT, the output of more than one
SELECT statement can be combined to form a single result set.
The UNION operator returns all rows that are in one or both of the result sets.
The INTERSECT operator returns all rows that are strictly in both result sets.
The EXCEPT operator returns the rows that are in the first result set but not in the second.
In all three cases, duplicate rows are eliminated unless ALL is specified.
The noise word DISTINCT can be added to explicitly specify eliminating duplicate rows.
Notice that DISTINCT is the default behavior here, even though ALL is the default for SELECT itself.

8. If the ORDER BY clause is specified, the returned rows are sorted in the specified order.
If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

9. If the LIMIT (or FETCH FIRST) or OFFSET clause is specified, the SELECT statement only returns
a subset of the result rows.

10. If FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE or FOR KEY SHARE is specified,
the SELECT statement locks the selected rows against concurrent updates.

Before starting with the book's topic I want to explain how to set up an efficient environment and some good practice which can improve the code's readability and quality. As somebody will notice these methods are completely opposite to the general code style trends. I'll try to give the motivation for each rule. Anyway, in general because the SQL universe is a strange place this requires strange approach. In order to write and read effectively the SQL the coder should gain the capability to get a mental map between the query's sections and the underlying logic. This can be facilitated using a clear and well defined formatting.

Code formatting

The SQL language is based on statements. Each statement is terminated by a specific character, by default ``;'' . In order to understand the logic of the SQL statements is very important to get the statements formatted in the same way the database executes them. We'll look first how to format the SELECT, then the DML and finally the DDL. Each of those queries needs a different structure because of the different logic. The common formatting rules are the following.

There is a tab separator of 7 spaces
All the keywords are in upper case
After a round bracket there is a carriage return and one tab indented
The opening and closing round brackets are indented at the same level

SELECT

When processing a query the parser works backward. It starts from the innermost and lowest complete statements and moves upward in order to get the entire picture. There is one remarkable exception. The WITH statements are computed first because required in the rest of the query. Actually the WITH acts like a temporary table. In order to see immediately the way PostgreSQL processes the query the indention should follow the same logic.
Basic formatting rules.

After the word SELECT there is a carriage return
The columns are separated by a comma and a carriage return
The columns indent one tab from the respective SELECT
The word FROM indents at the same level of SELECT
The relation indent one tab from the word FROM
The word WHERE indents at the same level of SELECT and FROM
A single condition indents one tab from the WHERE
Multiple conditions indent two tabs from the where and the logical operators indent one tab from the where
Between the logical operators and the condition there is one tab
Group by and order by follow the same rules of the select list
The queries joined by UNION, INTERSECT, and EXCEPT indent one tab
The terminator character indents at the same level of the SELECT,FROM and WHERE

Let's format a simple select from one relation and with one or more condition in the where section.

The first thing to do is to change all the keywords in uppercase.

We'll then format the select list.

Finally the from and the where.

Having multiple conditions requires an extra tab in order to keep aligned the logical operators and the conditions.

Joins.

If using the implicit joins the relations are separated by a comma and a carriage return, and indented at the same level
When using explicit joins specify the kind of join [INNER,LEFT OUTER,CROSS etc.]
After the left hand relation there is a carriage return
The join clause indents at the same level of the left hand relation
After the words [INNER,LEFT OUTER,CROSS] JOIN follows the relation's name and a carriage return
The word ON indents one tab from the JOIN and there is a carriage return
The join conditions follow the same rules of the WHERE condition
Nested joins indent one tab from the upper join
The usage of the aliases, with the optional word AS is strongly encouraged

Query with implicit join

Query with explicit inner join

Subqueries

All the subqueries will indent one tab from the surrounding brackets
If the subquery is short in can stay in line with the round brackets (e.g. SELECT now())

Query with subselect in the SELECT's list