CODEWITHSUNDEEP
mysqlsqlcoding-style
Treffynnon
Treffynnon

Reputation: 21553

Are there any published coding style guidelines for SQL?

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.

Upvotes: 45

Views: 23959

Answers (6)

mattmc3
mattmc3

Reputation: 18325

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())

Upvotes: 1

Daniel W.
Daniel W.

Reputation: 32280

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?

Upvotes: 3

Treffynnon
Treffynnon

Reputation: 21553

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.

Upvotes: 25

James Wang
James Wang

Reputation: 483

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.

Upvotes: 7

Ollie Glass
Ollie Glass

Reputation: 19993

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.

Upvotes: 5

MattyC
MattyC

Reputation: 204

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.

Upvotes: 0

Related Questions