Formatting Clear and Readable SQL Queries

Formatting Clear and readable SQL queries

With large queries I tend to rely a lot on named result sets using WITH. This allows to define the result set beforehand and it makes the main query simpler. Named results sets may help to make the query plan more efficient as well e.g. postgres stores the result set in a temporary table.

Example:

WITH 
  cubed_data AS (
     SELECT 
        dimension1_id,
        dimension2_id,
        dimension3_id,
        measure_id,
        SUM(value) value
     FROM
        source_data
     GROUP BY
        CUBE(dimension1, dimension2, dimension3),
        measure
  ), 
  dimension1_label AS(
     SELECT 
        dimension1_id,
        dimension1_label
     FROM 
        labels 
     WHERE 
        object = 'dimension1'
  ), ...
SELECT 
  *
FROM  
  cubed_data
  JOIN dimension1_label USING (dimension1_id)
  JOIN dimension2_label USING (dimension2_id)
  JOIN dimension3_label USING (dimension3_id)
  JOIN measure_label USING (measure_id)

The example is a bit contrived but I hope it shows the increase in clarity compared to inline subqueries. Named result sets have been a great help for me when I've been preparing data for OLAP use. Named results sets are also must if you have/want to create recursive queries.

WITH works at least on current versions of Postgres, Oracle and SQL Server

How to format a SQL string into a readable block of code that is well formatted and more easy to read

I have used this technique in the past, is this the best method of doing this?

The Key to it is:

A. using the following "aa" procedure which make reading so much easier.

B. If you build the SQL string using a query builder (like in MS Access), it is good to configure a utility to take the built SQL and reformat it so that the resulting SQL looks pretty much exactly like that below. (I use www.sqlinform.com but there are others, or you can do it manually)

C. By adding vbCrLf beofre each line, when the SQL string is built in VBA the resulting string can be output to the immediate window an easily read as it will have line brakes and line up nicely. (It's not rocket science)

Public Sub aa(ByRef a As String, ByVal b As String)
    ' This is deliberately not a function, although it behaves a bit like one
    a = a & vbCrLf & b

End Sub

' The function is called in code like this:

Dim a as string 

a = ""
aa a, "    SELECT CUR.txtLevel      AS [Current]  "
aa a, "         , NLPMi.strFullName AS [Full Name]  "
aa a, "         , NLPMi.DOB         AS [Birthday]  "
aa a, "         , NLPMi.Surname     AS [Surname Name]  "
aa a, "         , TOOLS.txtWCMTool "

aa a, "      FROM ( ( ( tblPeopleWCMSKILLSByYear AS SKILLS"
aa a, "                 LEFT JOIN tblSkillLevels AS CUR  "
aa a, "                        ON SKILLS.bytCurrentID = CUR.atnSkillLevelID
aa a, "               ) "
aa a, "              INNER JOIN [qrylstNames-LPMi] AS NLPMi  "
aa a, "                      ON SKILLS.intPeopleID = NLPMi.atnPeopleRecID
aa a, "             )"
aa a, "            INNER JOIN tblWCMTools AS TOOLS "
aa a, "                    ON SKILLS.intWCMSkillID = TOOLS.atnWCMToolID"
aa a, "           ) "

aa a, "     WHERE ( ( (SKILLS.bytYearID) = YEAR(DATE())-2012 )  "
aa a, "           AND CUR.txtLevel   >= " & MyVariable1 & "  "
aa a, "           AND TOOLS.txtWCMTool = '" & MyVariable2 & "'"
aa a, "           )  "

aa a, "  ORDER BY NLPMi.strFullName"
aa a, "        ", " & MyVariable3 & ""

aa a, "  ;"

Note that:

the brackets for the table joins line up
I use uppercase aliases to re-inforce they are aliases and so that they stand out
Sometimes I add spaces to separate the key areas (SELECT, WHERE, GROUP BY, FROM etc), usually when it is along bit of code and possible is very dynamic (ie when it has a lot of variables that change how it is built )
In the SELECT, ORDER BY and GROUP BY clauses, I favour putting the commas that are needed between columns in front of the column name on the same line.
I don't like database tables have the type in their name ie strFullName. However, this is not my schema!

Harvey

SQL formatting standards

I am of the opinion that so long as you can read the source code easily, the formatting is secondary. So long as this objective is achieved, there are a number of good layout styles that can be adopted.

The only other aspect that is important to me is that whatever coding layout/style you choose to adopt in your shop, ensure that it is consistently used by all coders.

Just for your reference, here is how I would present the example you provided, just my layout preference. Of particular note, the ON clause is on the same line as the join, only the primary join condition is listed in the join (i.e. the key match) and other conditions are moved to the where clause.

select
    ST.ColumnName1,
    JT.ColumnName2,
    SJT.ColumnName3
from 
    SourceTable ST
inner join JoinTable JT on 
    JT.SourceTableID = ST.SourceTableID
inner join SecondJoinTable SJT on 
    ST.SourceTableID = SJT.SourceTableID
where
        ST.SourceTableID = X
    and JT.ColumnName3 = Y
    and JT.Column3 = SJT.Column4

One tip, get yourself a copy of SQL Prompt from Red Gate. You can customise the tool to use your desired layout preferences, and then the coders in your shop can all use it to ensure the same coding standards are being adopted by everyone.

Python SQL query string formatting

Sorry for posting to such an old thread -- but as someone who also shares a passion for pythonic 'best', I thought I'd share our solution.

The solution is to build SQL statements using python's String Literal Concatenation (http://docs.python.org/), which could be qualified a somewhere between Option 2 and Option 4

Code Sample:

sql = ("SELECT field1, field2, field3, field4 "
       "FROM table "
       "WHERE condition1=1 "
       "AND condition2=2;")

Works as well with f-strings:

fields = "field1, field2, field3, field4"
table = "table"
conditions = "condition1=1 AND condition2=2"

sql = (f"SELECT {fields} "
       f"FROM {table} "
       f"WHERE {conditions};")

Pros:

It retains the pythonic 'well tabulated' format, but does not add extraneous space characters (which pollutes logging).
It avoids the backslash continuation ugliness of Option 4, which makes it difficult to add statements (not to mention white-space blindness).
And further, it's really simple to expand the statement in VIM (just position the cursor to the insert point, and press SHIFT-O to open a new line).

SQL Statement indentation good practice

SELECT column1
     , column2
FROM table1
WHERE column3 IN
(
    SELECT TOP(1) column4
    FROM table2
    INNER JOIN table3
    ON table2.column1 = table3.column1
)

I like to have all "," in front, this way I never search them when an error at line X from the SQL editor.

This is an example for those who do not use this type of writting SQL statement. Both contain an error of a missing comma.

SELECT sdcolumn123
 , dscolumn234
 , sdcolumn343
 , ffcolumn434
 , sdcolumn543
 , bvcolumn645
  vccolumn754
 , cccolumn834
 , vvcolumn954
 , cvcolumn104
FROM table1
WHERE column3 IN
(
    ...
)

SELECT sdcolumn123, dscolumn234, asdcolumn345, dscolumn456, ascolumn554, gfcolumn645 sdcolumn754, fdcolumn845, sdcolumn954, fdcolumn1054
FROM table1
WHERE column3 IN
(
    ...
)

I found easier and more quick at the first example. Hope this example show you more my point of view.

Java - How to format an SQL statement without getting hit by Java's String immutability?

I think that behind the scenes, you're getting a StringBuilder / StringBuffer when you use + anyway. Per the official Javadoc on String:

String concatenation is implemented through the StringBuilder(or StringBuffer) class and its append method.

How do I force MS Access to retain its SQL formatting?

I found a hack, here. The secret lies in enclosing your query inside a dummy query. Like so:

SELECT * FROM (SELECT   <-----here
Table1.Field1,
Table1.Field2
FROM
Table1

WHERE
Table1.Field2 = "Yes") AS query1 <-----here

I've indicated where and how you wrap the code. The only issue I can see is if you wanted to edit the code in design view, then it doesn't seem to pick the enclosed code up at all.

How to pretty format the printing of SQL Queries in SQLAlchemy?

There's a couple of options to try:

Pygments
sqlparse
format-sql

Formatting Clear and Readable SQL Queries