What SQL coding standard do you follow? [closed]

Question

Is there any widely used SQL coding standard out there? SQL is little bit different from C/C++ type of programming languages. Really don't know how to best format it for readability.

Answer 1

From a really very nice blog on PostgreSQL, but this topic is applicable in general:

Maintainable queries - my point of view (depesz.com)

...I decided that my priorities for writing maintainable queries:

1. Avoid useless typing.

2. Use aliases for tables/views. Always. And make them sensible aliases.

3. Indent code in some way.

4. Avoid quotations (yes, this is why I hate Django)

5. Use join syntax

I do agree with capitalization of reserved words and every other identifier, except my own.

Answer 2

Google for sql pretty printer or look here. I haven't tried it out myself, but it gives you a good start. Most commercial tools like Toad have a "formatting" option which helps, too.

Answer 3

Anything in blue is upper case SELECT, DELETE, GO, etc

Table names are singular like the table that holds our customers would be the customer table

Linking tables are tablename_to_tablename

use _ between works in table names and parameters

example

BEGIN
    SELECT
        Company.ID AS Company_ID,
        Company.Client_Name,
        Company.Website,
        Office.Office_Name
    FROM
        Company_Office WITH(NOLOCK)
        INNER JOIN Company WITH(NOLOCK) ON Company_Office.Company_ID = Company.ID
    WHERE
END

Answer 4

I like the comma preceding way:

SELECT
      column1
    , column2
    , column3
    , COALESCE(column4,'foo') column4
FROM
    tablename
WHERE
    column1 = 'bar'
ORDER BY 
      column1
    , column2

it makes it the easiest to read and debug in my opinion.

Answer 5

I'm surprised that the coding style I've used for almost 20 years isn't on this list:

  SELECT column1,
         column2,
         column3,
         COALESCE(column4, 'foo') AS column4
    FROM tablename
   WHERE column1 = 'bar'
ORDER BY column1,
         column2

I find this the absolutely most readable, but I admit that it is tedious to type. If right aligning the keywords is too much, I'd opt for left aligning them:

SELECT   column1,
         column2,
         column3,
         COALESCE(column4, 'foo') AS column4
FROM     tablename
WHERE    column1 = 'bar'
ORDER BY column1,
         column2

Answer 6

SELECT c.id
     , c.name
     , c.folder
     , cs.num_users active_members
     , cs.num_videos

  FROM campaign c
  JOIN campaign_stats cs
    ON cs.campaign_id = c.id
  JOIN (SELECT _c.id
             , _c.name

          FROM campaign _c
         WHERE _c.type = 9) t_c 
    ON t_c.id = c.id

 WHERE c.id IN (1,2,3)
   AND cs.num_videos > 10

This works pretty good for us.

This actual query doesn't make much sense since I tried to build it quickly as an example... but that's not the point.

• t_c stands for category table sub-query or "temp category".
• _underscoring of stuff inside sub-queries.
• alias column names to make sense in the context of the query. e.g. "active_members"

• putting commas at the beginning of the new lines makes it easier to build dynamic queries:

  $sql .= ", c.another_column"
  

• everything else is straightforward.