Are there any published coding style guidelines for SQL?

Question

Often I will end up with very complex SQL statements and I wondered if there was style guideline out there that dictates a common way of laying various aspects of a query out.

I am look for something descriptive along the lines of Python's PEP8 or Zend Frameworks guidelines and not code by example.

Most of my queries are written for MySQL.

So do you know of a universal style guide? Perhaps you have written one yourself. Please share your guidelines.

@JNK I have read through most of that question before asking mine. It is not actually what I am looking for. I am after a proper published style guide that the rest of the development team and I can all adopt. This is why I mentioned something along the lines of PEP8. — Treffynnon, May 10 '11 at 14:10
@Treffynnon - The last section is what made me link to that question - `Perhaps you have written one yourself. Please share your guidelines.` — JNK, May 10 '11 at 14:12
@JNK Fair enough. However that question has a list of examples attached to it and not really descriptive guidelines like the ones I have linked to in my question. — Treffynnon, May 10 '11 at 14:16
@Treffynnon - Well I can't remove my vote to close, but if others disagree with me then it won't be an issue :) — JNK, May 10 '11 at 14:17
@JNK the possible dupe link is also useful for others who might come across this question as well :-) I probably should have included a link to it in my question! — Treffynnon, May 10 '11 at 14:51

score 19 · Accepted Answer · answered Jul 23 '15 at 08:20

19

Since asking this question I have written a public SQL style guide that is compatible with Joe Celko's SQL Programming Style book under the Creative Commons Attribution-ShareAlike licence.

It is available over at www.sqlstyle.guide or as markdown directly from the GitHub repo.

answered Jul 23 '15 at 08:20

Treffynnon

21,365
6
65
98

Awesome! (And more characters for SO.) – Arthur Aug 05 '21 at 23:16

score 6 · Answer 2 · edited Feb 27 '12 at 10:33

Here are some SQL programming guidelines and best practices we collected:

Do not use SELECT * in your queries.
Always use table aliases when your SQL statement involves more than one source.
Use the more readable ANSI-Standard Join clauses instead of the old style joins.
Do not use column numbers in the ORDER BY clause.
Always use a column list in your INSERT statements.
Don't ever use double quotes in your T-SQL code.
Do not prefix your stored procedure names with “sp_”.
Always use a SQL formatter to format your SQL like Instant SQL Formatter (free and online)

You can check detailed explanation of those best practices in this blog post.

This doesn't answer the question that was asked. – Luke Apr 07 '16 at 10:33 — Luke, Apr 07 '16 at 10:33

Ollie Glass · Answer 3 · 2011-05-10T15:39:15.333

5

Two guides I know of are Joe Celko's SQL Programming Style and the venerable Code Complete.

There's also the SQL-92 standard. It doesn't contain a style section, but you might consider it's style to be implicitly canonical.

edited May 10 '11 at 15:39

answered May 10 '11 at 14:59

Ollie Glass

19,455
21
76
107

score 3 · Answer 4 · edited May 23 '17 at 10:29

3

MySQL has a short description of their more or less strict rules:

https://dev.mysql.com/doc/internals/en/coding-style.html

Most common codingstyle for MySQL by Simon Holywell:

http://www.sqlstyle.guide/

See also this question: Is there a naming convention for MySQL?

edited May 23 '17 at 10:29

Community

1
1

answered Oct 19 '16 at 14:39

Daniel W.

31,164
13
93
151

mattmc3 · Answer 5 · 2019-02-13T17:34:29.317

1

Kickstarter has a style guide here. I have a modified version of that for people who prefer lowercase SQL and Celko's "river".

My style guide is here. Here is a sample:

-- basic select example
select p.Name as ProductName
     , p.ProductNumber
     , pm.Name as ProductModelName
     , p.Color
     , p.ListPrice
  from Production.Product as p
  join Production.ProductModel as pm
    on p.ProductModelID = pm.ProductModelID
 where p.Color in ('Blue', 'Red')
   and p.ListPrice < 800.00
   and pm.Name like '%frame%'
 order by p.Name

-- basic insert example
insert into Sales.Currency (
    CurrencyCode
    ,Name
    ,ModifiedDate
)
values (
    'XBT'
    ,'Bitcoin'
    ,getutcdate()
)

-- basic update example
update p
   set p.ListPrice = p.ListPrice * 1.05
     , p.ModifiedDate = getutcdate()
  from Production.Product as p
 where p.SellEndDate is null
   and p.SellStartDate is not null

-- basic delete example
delete cc
  from Sales.CreditCard as cc
 where cc.ExpYear < '2003'
   and cc.ModifiedDate < dateadd(year, -1, getutcdate())

edited Feb 13 '19 at 17:34

answered Dec 18 '18 at 23:12

mattmc3

17,595
7
83
103

For someone who likes to use tabs to indent, I don't think I can achieve the column of whitespace between keywords and column names. I do appreciate your usage of leading commas though, I love leading commas. – ADJenks Feb 12 '19 at 20:20
@ADJenks This post is my first exposure to leading commas. I am instantly (I think) seeing the benefit. I can't tell you how many times I've modified code and forgotten to either a) strip away a comma prior to the next keyword or b) add a comma in. I imagine this will save me many headaches in the future. Much like reversing logical comparisons: `if ('haley' == $firstName)` Looks strange, but protects against accidentally assigning instead of comparing: `if ('haley' = $firstName)`, as this will throw an error. – elbowlobstercowstand May 12 '23 at 05:13
@elbowlobstercowstand I love those too, they are often called Yoda conditions. – ADJenks May 13 '23 at 22:40
@ADJenks Ahh! Quite clever! A lot from you, I am learning. ;) – elbowlobstercowstand May 14 '23 at 14:22

score 0 · Answer 6 · answered May 10 '11 at 14:18

0

Have you thought about getting your team to use a tool with built in formatting capabilities?Toad for MySql has this. Its not going be a guide as such but a least will bring some consistency.

answered May 10 '11 at 14:18

MattyC

204
2
4

This might be a possibility but we have people on MacOS, Linux and Windows so it would need to be cross platform. Some of us need the editing abilities/short cuts that Vim provides as well. But definitely an alternative as many of us currently use SQLYog (under WINE in some cases). – Treffynnon May 10 '11 at 14:21

Are there any published coding style guidelines for SQL?

6 Answers6

Linked