Home

SQL style guide

What is a style guide?

Programming style, also known as code style, is a set of rules or guidelines used when writing the source code for a computer program. It is often claimed that following a particular programming style will help programmers read and understand source code conforming to the style, and help to avoid introducing errors. (Source: Wikipedia)

The SQL style guide below is a work in progress and will evolve as we learn more SQL syntax:

A simple SQL query:

-- this is a comment
SELECT *
FROM table_name -- this is another comment (e.g. TODO)
;

Multiple columns query + LIMIT:

SELECT
  column_name,
  column_name,
  column_name
FROM table_name
LIMIT n_rows
;

Multiple columns query + ORDER BY column name:

SELECT
  column_name,
  column_name,
  column_name
FROM table_name
ORDER BY
  column_name,
  column_name DESC,
  column_name
;

Multiple columns query + ORDER BY column position:

SELECT
  column_name,
  column_name,
  column_name
FROM table_name
ORDER BY 1, 2 DESC, 3
;

With SQL data manipulation functions:

SELECT
  column_name,
  SQL_FUNCTION(column_name) AS alias_column_name, -- e.g. UPPER(column)
FROM table_name
;

With conditional statement CASE WHEN:

SELECT
  column_name,
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE elsecondition
  END AS alias_column_name
FROM table_name
;

With multiple WHERE filters:

SELECT *
FROM table_name
WHERE condition1
  AND condition2 -- river formatting optional, can just use `tab` or `4 spaces`
   OR condition3
;   

With DISTINCT:

SELECT DISTINCT
  column_name,
  column_name  
FROM table_name
;

With GROUP BY aggregations, grouping by column positions:

SELECT
  column_name,
  column_name,
  ...
  AGGREGATION_FUNCTION(column_name),
  AGGREGATION_FUNCTION(column_name)
  ...
FROM table_name
GROUP BY 1, 2 ...
;

With GROUP BY aggregations, grouping by column positions, with filters using HAVING:

SELECT
  column_name,
  column_name,
  ...
  AGGREGATION_FUNCTION(column_name),
  AGGREGATION_FUNCTION(column_name)
  ...
FROM table_name
GROUP BY 1, 2 ...
HAVING aggregation_condition1
   AND aggregation_condition2
    OR aggregation_condition3   
;

With GROUP BY aggregations, grouping by column names:

SELECT
  column_name,
  column_name,
  ...
  AGGREGATION_FUNCTION(column_name),
  AGGREGATION_FUNCTION(column_name),
  ...
FROM table_name
GROUP BY
  column_name,
  column_name ...
;

With GROUP BY aggregations, grouping by column names, with optional filters using HAVING:

SELECT
  column_name,
  column_name,
  ...
  AGGREGATION_FUNCTION(column_name),
  AGGREGATION_FUNCTION(column_name),
  ...
FROM table_name
GROUP BY
  column_name,
  column_name ...
HAVING aggregation_condition1
   AND aggregation_condition2
    OR aggregation_condition3   
;

With JOINs and table aliasing:

SELECT
  table_alias_1.column_name,
  table_alias_2.column_name
FROM table_name_1 table_alias_1  
INNER JOIN table_name_2 table_alias_2
  ON table_alias_1.column_key = table_alias_2.column_key
INNER JOIN table_name_3 table_alias_3
  ON table_alias_1.column_key = table_alias_3.column_key
;

With a sample 2 table UNION or UNION ALL:

SELECT
  column_name,
  column_name,
  ...
FROM table_name_1

UNION [ALL]

SELECT
  column_name,
  column_name,
  ...
FROM table_name_2

ORDER BY
  column_name,
  column_name DESC,
  column_name

LIMIT BY n
;

With sample scalar subquery in the SELECT clause:

SELECT
  column_name,
  (SELECT AGGREGATION_FUNCTION(column_name) FROM table_name) AS alias_column_name
FROM table_name
;  

With sample IN subquery in the WHERE clause:

SELECT *
FROM table_name
WHERE column_name IN (SELECT column_name FROM table_name)
;

With sample table subquery:

SELECT *
FROM (
  SELECT
    column_name,
    column_name
  FROM table_name    
  )
WHERE ...  
GROUP BY ...
;

With common table expressions, using WITH:

WITH cte_name_1 AS (
  SELECT
    column_name,
    column_name,
    ...
  FROM table_name
  )

, cte_name_2 AS (
  SELECT
    column_name,
    column_name,
    ...
  FROM table_name
  )

SELECT *
FROM table_name
INNER JOIN cte_name_1
  ON table_name.column_key = cte_name_1.column_key
INNER JOIN cte_name_2
  ON table_name.column_key = cte_name_2.column_key
;

Do’s:

capitalize SQL reserve keywords (e.g. SELECT, FROM, LIMIT)
lowercase table names and columns (e.g. employees, firstname)
create a new line when starting a new part of the SQL statement
always end a SQL query with a semicolon ; and then hit Enter
indents should be 4 spaces (or a tab if your text editor can standardize tab length)
capitalize SQL functions (e.g. UPPER())
follow deeper multi line indents for CASE WHEN statements

Avoid:

when naming columns, avoid camel casing in table or column names with multiple words, use underscore _ to separate the words. (e.g. use column_name, not ColumnName)

Optional:

Comments are optional, but as your SQL queries get long and complex, this can be helfpul for you to mark TODOs and come back to later.
Some style guides online recommend river formatting. Given that we are using psql and not a GUI tool like PgAdmin which can do auto formatting for you, this style of formatting might be more trouble than it’s worth. However, that decision is up to you.