sql-formatting.md

SELECT
	  pri.lorem
	, pri.ipsum
	, pri.dolar orci
	, pri.sit
	, pri.amet
	, CASE
		WHEN erat = 'vitae'
		THEN 'suscipit'
		ELSE 'sed'
	  END erat

	, sec.consectetur
	, sec.adipiscing

	, ter.elit

FROM primaryTable pri
INNER
	JOIN secondaryTable sec
	ON pri.lorem = sec.lorem
	AND pri.dolar = sec.dolar
LEFT OUTER
	JOIN tertiaryTable ter
	ON pri.lorem = ter.lorem
	AND sec.adipiscing = ter.adipiscing
WHERE
	pri.amet = 'sapien'
GROUP BY
	  pri.lorem
	, pri.ipsum
	, pri.dolar
	, pri.sit
	, pri.amet

This is my default formatting for SELECT sql queries. The primary focus is on readability, and secondary focus is on being able to debug/develop the statement.

Selected columns are all on their own lines. Comma preceding is a personal preference, but I find it makes things easier to read when columns and aliases are longer. Columns are grouped by tables to make scanning the list of what is being selected easier. Column table groups are in the same order as the tables are selected from and joined. Aliases are spaced after the column definition ends, unless every column has an alias and then I will tab them to line them up sometimes - but generally I find this is a waste of time.

Table joined with the type of join on its own line and the JOIN tablename on the next line and indented. This means that when looking at a query you can quickly glance through and see all of the tables because they are mostly lined up and always have a gap preceding them. This also serves to highlight the type of join, as that is a common place for mistakes. Join conditions are lined up, one condition per line below. This makes it easy to see and not miss the conditions, and also makes it easier to single line comment them out for debugging.

INSERT INTO primaryTable
(
	lorem
	, ipsum
	, dolar
	, sit
	, amet
)
VALUES
  (
	'lorem'
	, 'ipsum'
	, 'dolar'
	, 'sit'
	, 'amet'
)
, (
	'lorem'
	, 'ipsum'
	, 'dolar'
	, 'sit'
	, 'amet'
)
RETURNING lorem;

Inserts with only a couple columns may not have to use this format as it may be easier to keep values on one line, but especially when using parameters or when working with lots of columns this becomes much easier to read and understand. I also generally use a tool that will either put comments behind values to remind you the column or an SQL IDE that will tell you that.

UPDATE primaryTable
SET
	ipsum = 'ipsum'
WHERE
	lorem = 'Lorem'

DELETE
FROM primaryTable
WHERE
	lorem = 'Lorem'

My version:

SELECT    pri.lorem,
          pri.ipsum,
          pri.dolar orci,
          pri.sit,
          pri.amet,
          CASE
            WHEN erat = 'vitae' THEN 'suscipit'
            ELSE 'sed'
          END erat,
          sec.consectetur,
          sec.adipiscing,
          ter.elit

FROM      primaryTable pri
          INNER JOIN secondaryTable sec ON pri.lorem = sec.lorem AND pri.dolar = sec.dolar
          LEFT OUTER JOIN tertiaryTable ter ON pri.lorem = ter.lorem AND sec.adipiscing = ter.adipiscing

WHERE     pri.amet = 'sapien'

GROUP BY  pri.lorem,
          pri.ipsum,
          pri.dolar,
          pri.sit,
          pri.amet

INSERT INTO primaryTable ( lorem, ipsum, dolar, sit, amet ) 

VALUES      ( 'lorem', 'ipsum', 'dolar', 'sit', 'amet' ), 
            ( 'lorem', 'ipsum', 'dolar', 'sit', 'amet' )

RETURNING   lorem;

UPDATE    primaryTable

SET       ipsum = 'ipsum'

WHERE     lorem = 'Lorem'

DELETE

FROM      primaryTable

WHERE     lorem = 'Lorem'

My latest version is similar to mjhagen's, but I nest the JOINs a bit differently as I like to see which table is joined clearly on its own line.

FROM
	primaryTable pri
	INNER JOIN secondaryTable sec
		ON pri.lorem = sec.lorem
		AND pri.dolar = sec.dolar
	LEFT OUTER JOIN tertiaryTable ter
		ON pri.lorem = ter.lorem
		AND sec.adipiscing = ter.adipiscing
WHERE...```