My SQL coding style, briefly explained

Databases
1

here’s my SQL coding style, briefly explained

if i have not explained something clearly, please ask me to clarify

if you’d like to add to the topic with your own preferences, please do

when writing SQL, i like to align keywords and expressions against a vertical “river” of space

like this –

           | | 
           | | 
  KEYWORDS | | expressions
      ---> | | <--- 
           | |

keywords right aligned on the left side of the river, expressions left aligned on the right

so for example –

SELECT table1.jobno
     , table1.fixed
     , COALESCE(table1.cost, table2.cost) AS cost
  FROM table1
LEFT OUTER 
  JOIN table2 
    ON table2.jobnumber = table1.jobno
   AND table2.status = 'ok'

minor exceptions are allowed such as LEFT OUTER extending through the space, but note how JOIN and ON and AND are aligned to make the river easier to see

this vertical river of space is a strong visual aid in understanding the structure of an SQL statement

leading commas (which is a separate discussion, and a good one) are placed on the left side of the river to stand out just like ANDs and ORs do, re-inforcing the structure, with elements at the same logical level stacked, rather than listed

(yes this can make for some tall SELECT clauses)

notice how i’ve structured the join –

  FROM table1
LEFT OUTER 
  JOIN table2 
    ON table2.jobnumber = table1.jobno

some people write this as –

  FROM table1
LEFT OUTER 
  JOIN table2 
    ON table1.jobno = table2.jobnumber

of course, these are exactly equivalent, but notice how your eyes (and brain) have to do a quick switcheroo

(verify this yourself when you next read someone else’s query and they’ve done it that way)

so when writing a JOIN, i align that new joined table’s columns on the left, and thus make it easier to instantly see how it’s being joined to what went before

this is re-inforced when there are additional join conditions for that table –

  FROM table1
LEFT OUTER 
  JOIN table2 
    ON table2.jobnumber = table1.jobno
   AND table2.status = 'ok'

notice how table2 and its columns are easy to see as a group

and the joined column sticks out on the right, making it easier to reference the table above that it’s joining to

finally, indentation for nested structure levels

i won’t say much beyond the fact that everything shifts over!

for example, a subquery –

SELECT table1.jobno
     , table1.fixed
     , spec.price
     , COALESCE(table1.cost, table2.cost) AS cost
  FROM table1
LEFT OUTER 
  JOIN table2 
    ON table2.jobnumber = table1.jobno
   AND table2.status = 'ok'
LEFT OUTER
  JOIN ( SELECT job
              , MAX(price) AS price
           FROM table3
          WHERE estimate = 'y'
         GROUP
             BY job 
       ) AS spec
    ON spec.job = table1.jobno

a subquery in the FROM clause is sometimes called a derived table

notice where the ON clause for the derived table is aligned

see the second river?

7 Likes
2

I use Phpmyadmin, copy and paste the PHP generated SQL into the Phpmyadmin edit box then click Format.

Formatted statements then posted back into a PHP Heredoc string and suitable PHP values replace hard-nosed values.

3

PhpStorm : Ctrl+Alt+L

4

example?

1 Like
5

hmmm… does this format the SQL? could you give an example?

7

Yes. There are options in settings->editor->code style->sql to set how you want the formatting to be.

SElecT username, password, first_name    , last_name
     , email
    from users
    wheRe user_id = ?

Ctrl+Alt+L

SELECT username
     , password
     , first_name
     , last_name
     , email
    FROM users
    WHERE user_id = ?
8

I never noticed the “format” button in PHPMyAdmin before.
This is what it did with the example query:-

SELECT
    table1.jobno,
    table1.fixed,
    COALESCE(table1.cost, table2.cost) AS cost
FROM
    table1
LEFT OUTER JOIN table2 ON table2.jobnumber = table1.jobno AND table2.status = 'ok'
1 Like
9

this is great, i could live with it

i like the keyword caps… was leading comma an option?

10

thank you

i’ve never liked phpmyadmin and this example did nothing to change my mind

1 Like
11

Yes, leading comma is an option. You can pretty fine grain the formatting any way you want it.

1 Like
12

Me either. There are so many other options available there is no reason to use it…unless you are on shared hosting that does not allow remote access.

13

Pretty neat, the only complaint I have is how LEFT OUTER is breaking the flow. Is it possible to go this way instead?

  FROM table1
  LEFT OUTER 
  JOIN table2 
    ON table2.jobnumber = table1.jobno
   AND table2.status = 'ok'

Having whitespace between “left” and “outer” fit with the flow.

14

What I like best is the line by line validation shown by some sort of red icon at the start of the line.

Also the detailed error message explanation at the foot of the page.

Ideal for experimenting :slight_smile:

16

surely

17

It’s one man sharing and explaining his style, he’s not laying down rules that everyone has to follow :stuck_out_tongue:
Do what you want, how you want.

I do like trying to line things up… though I have to ask, @r937, WHEN do you go about lining things up? I can imagine that trying to stick to the style would get frustrating for me when I have to add a keyword that suddenly changes every line of the query because it’s too long… (though, admittedly, there’s not many keywords that are longer than 6 characters… hmm.)

18

nailed it ;o)

20

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.