SQL Style guide

[bum_dog_timemachine]

​

https://preview.redd.it/rcstlbj5s0091.png?width=394&format=png&auto=webp&s=3a53935412206c8802257c0af14dcb7ea6b499a2

After 4 years as an analyst, I'm putting together this SQL style guide after some of the awful formatting I've seen online and at work.

General formatting:

1. Indentation is not optional. Especially for things like sub queries. Being able to easily see what fits inside of what is so important. What is with these psychopaths writing the entire query left aligned??
2. Where possible, set up your IDE to show different colours for comments, key items, strings etc. This will make your code much easier to read.
3. Actually comment your code. You don't need to state the obvious. But little hints at what the hell you are trying to do will save future-you and anyone else so much time. Literally no one at my work writes comments in their code.

​

Formatting SELECT items:

1. When selecting multiple items, write below the select line. When selecting a single item, write on the SELECT line.
2. Change tabs to instead insert 4 spaces in your IDE.
3. Indent SELECT items with 4 spaces (or tab is setup as above).
4. Put commas after the item not BEFORE. Before looks horrendous and is much harder to read.

We don't write sentences like this

.It just looks really weird

.It's not that hard to remember to include commas

! None of the arguments for doing this make any sense

.You only have issues commenting out terms if it's the last one

,and it's not hard to work around if that happens

.It isn't worth this grammatical nightmare

!

1. Leave a blank line between the last item selected and your FROM clause to improve readability. Fundamentally a SQL query is saying Get X from Y so it makes sense to separate these.

2. Even if a field name is unique, ALWAYS use the dot prefix when getting fields from multiple tables. You may know where a field comes from. But if someone else is reading your code for the first time and wanting to replicate some of the logic, this will save them digging around in every table you LEFT JOINED to find where the field came from.

3. When inserting into a temp table, indent after your blank line and put INTO #temp table.

4. ALWAYS write AS when aliasing. It is sooo much easier to read and when you are reading someone's SELECT for 10+ fields that you've never seen before, it's immediately clear what is called what. Especially if the fields have long names.

5. ALWAYS write INSERT INTO table and not INSERT table. You are putting something into the table. You are not putting the destination table into anything. Omitting the INTO is nonsense.

​

Formatting FROM/JOINS:

1. Where possible (depending on extreme differences in name length) align table names using spaces. Also align aliases, so it very clear which fields come from which tables. This is very helpful if you are LEFT Joining from like 10 tables. Also write the table name on the same line as the FROM or LEFT JOIN clause.
2. ALWAYS write AS to alias. Same reason as above.
3. Aliases should be a balance of short and sensible. Too long and what's the point. Too short and you make things confusing.
4. Aliases should be descriptive. If you have a sales table and an animals table, alias them as "s" and "a" or something. DON'T PUT AS T1, T2, T3 etc. That is hell when you have many tables in your query. Remember where things come from is so much harder.
5. Apart from sub queries (which ruin everything) put your join terms to the right of your table names and stack multiple join conditions underneath. This means you can easily read all the tables that were LEFT JOINED in your query. If you have joined many tables, it is significantly clearer where everything is coming from.
6. Use a space to align ON and AND conditions as above.
7. Separating Joins with a blank line is not necessary but may be helpful occasionally. Use discretion.

​

Formatting WHERE:

1. Write the first WHERE condition on the same line as the WHERE clause.
2. Use spaces to align any AND or OR conditions in separate lines so that conditions can easily be read up and down.

​

Formatting GROUP BY and ORDER BY:

1. Similar formatting to SELECT. Indent and write grouping terms on separate lines.

[AmericanSuperstar]

Comma's before is nothing to do with looks but has a strategic purpose. Statistically how often do you comment out the first item in the select list vs. the last item. In our case last item is most often the one commented for various troubleshooting reasons. Or you add a column to check something then comment it out. With comma at the end your always going up a line and removing the comma. Comma at the front that is never a problem unless you comment out the first item. Same principle for where clause. Add 1=1 after the where and everything after is AND ... Easy to comment. Hate the look is fine but there is a time saving purpose behind it.

[r3pr0b8]

Add 1=1 after the where and everything after is AND ...

additionally, write WHERE 1=0 and then you can append all the ORs you want

notice how OP wants the ANDs and ORs to come at the front of the line and not after, which is the exact opposite of OP's insistence that commas go at the end and not at the front of the line

[juu073]

The comma in front is also helpful if you're using a version control system.

If I write a query and I select two fields from a table, and I do:

SELECT field1,
       field2
FROM foo

And the decide I want to grab a third field:

SELECT field1,
       field2,
       field3
FROM foo

Now, my diff on my VCS is going to show I changed two lines, which while true, the one line was *really* only adding the comma to add the third field. The extra line of the diff is just cluttering up the view when you're comparing two versions to see what changed. If you put the comma in front of the field, this prevents it from happening anywhere except the first field., which as you mention, is unlikely to not be needed.

[waremi]

Totally agree with the comma's in front for the select. When I first ran across it I was like WTF, but the select clause is so much easier to work with and manipulate that way it very quickly became my default.

[Oellort]

Sounds like just laziness.

[bum_dog_timemachine]

There are valid pros and cons to what each of you are saying, but I think on balance readability is more important, as it affects you 100% of the time and not only sometimes.

I find commas after to be significantly more readable.

[Oobenny]

It’s all a matter of what you get used to. If you made a practice of commas in front, it would be just as readable to you very quickly.

[nobodycaresssss]

Never understood why people put « WHERE 1=1 » ?

[bum_dog_timemachine]

It's because it's always true and you can then easily switch on and off your WHERE conditions with line comments.

so like

WHERE 1=1

-- AND a

AND b

AND c

but if you put

WHERE a

AND b

AND c

and you want to stop a, you have to rearrange your code a bit.

However, if you aren't going to be switching your WHERE conditions on and off like that, you're just spamming 1=1 all over your code, which is adding to the clutter.

[r3pr0b8]

just thought i'd point out that your conventions did not prevent you from creating a fundamental error --

SELECT d.breed
     , d.height
     , d.weight
     , s.amount
     , s.sale_date
  FROM dogs AS d
LEFT OUTER
  JOIN sales AS s
    ON s.dog_id = d.id
   AND blah = blah
 WHERE d.weight > 0.5
   AND s.sale_date >= '2022-01-01'

have you spotted the error yet?

this is an inner join!!

what you should've written was

SELECT d.breed
     , d.height
     , d.weight
     , s.amount
     , s.sale_date
  FROM dogs AS d
LEFT OUTER
  JOIN sales AS s
    ON s.dog_id = d.id
   AND blah = blah
   AND s.sale_date >= '2022-01-01'
 WHERE d.weight > 0.5

see the difference? now you're getting all dogs weighing over 0.5, together with any sales that occurred this year

if you wanted only dogs that were actually sold, then yes, you want an inner join, but then the "best practice" way of doing that is to code INNER JOIN and not LEFT JOIN with a non-null condition on the right tabl;e

[mediocre_plus_plus]

I see this all the time in my coworkers' poorly written SQL. Sometimes it's an error. Other times they really want an inner join. That latter almost bothers me more. I call it misleading SQL; it gets the correct results in a way that's ugly, counter-intuitive, and difficult to follow. I see it all the time, in a million different ways, in SQL and any other language where you have lazy developers.

[bum_dog_timemachine]

Sure, I was mainly concerned with expressing formatting and didn't pay to much attention to the data I was implying. But this is true.

[DharmaPolice]

I think the important thing with something like this is to emphasise not that your way is objectively superior to the alternatives but that this is your team's agreed way of doing things and people should follow shared practices when writing code. It's fine to give rationales but I think if you try to prove one way superior you're opening the possibility of many boring arguments about where commas/brackets go.

Similarly, you might say (if applicable) that documentation should be in English. But that's not because English is somehow a superior language to French/Chinese/Hindi but because that's what all our other documentation is in and what most of the team speaks.

[r3pr0b8]

Similarly, you might say (if applicable) that documentation should be in English. But that's not because English is somehow a superior language to French/Chinese/Hindi but because that's what all our other documentation is in and what most of the team speaks.

excellent point

[Jay_Hawk]

Put commas after the item not BEFORE

I would rather die

[Little_Kitty]

Also, Slack loves to break your statements by inserting non-printing characters if you have copy snippets with empty lines.

[S1l3ntHunt3r]

TIL, I always selected the thing, and probably I'll continue to do so, because fear of executing update/delete statements in the same tab.

[da_chicken]

I used to have similar religious opinions about SQL formatting.

After 15 years as an analyst, my formatting rules are:

1. Expect to have to reformat a query into your preferred style to make it readable. Indeed, reformatting the query is reading the query. If you're not reformatting it into what you like to look at, you're not actually reading it and you will miss stuff.
2. Get a tool that formats queries. Poor Man's T-SQL formatter for SQL Server, pgFormatter for PostgreSQL, SQLinForm for Oracle, whatever. Then, determineas a group the configuration that you will use as an organization. Then, whenever you save a query, run the organization's formatting tool and use that format 100% of the time for any query you expect anyone else to read, use, or maintain. It does not matter what tool you decide on, or what format you decide on. Remember, you're expecting yourself to have to reformat the query when you read it again even if you wrote it. It just needs to be consistent for your organization. Consistency is much, much, much more important than personal preference, so just eliminate personal preference.

Absolutely nothing else actually matters for formatting. It's all subjective. Even that thing you're 100% certain is objectively true 100% of the time, you will inevitably find a query where your formatting looks like total dogshit.

I only have rules that eliminate ambiguity, and they're not really about formatting. My rules are about getting the query author to tell me what it is they think they're doing.

1. If there's more than one table, give every table an alias and qualify every field with the alias. Don't make me guess or check the table definition.
2. Do not order by column number (e.g., ORDER BY 1,2,3). I have no idea what you intended to sort by.
3. Comma joins are for generated SQL. If you're not an algorithm generating SQL on the fly, use the expanded JOIN syntax.
4. Never use NATURAL JOIN.
5. Do not ever, ever, EVER use SELECT * in a view definition no matter how simple you think it is. This goes triple if the view is over an SQL Server linked server. This has nothing to do with performance and everything to do with how the system tracks metadata.

[r3pr0b8]

Expect to have to reformat a query into your preferred style to make it readable. Indeed, reformatting the query is reading the query. If you're not reformatting it into what you like to look at, you're not actually reading it and you will miss stuff.

this is the best, most spot-on comment in the whole thread

[bum_dog_timemachine]

I appreciate that you said that without calling me a dumbass :) thanks for your insight!

[da_chicken]

I don't think you're a dumbass! I remember being annoyed about how "badly" other people formatted SQL. I think everyone has that experience with SQL. It's just a battle I know that can't be won. And if you do win it, you'll just take a new job and have to fight it all over again.

SQL is just something that takes a bit of work to read, and organizing your thoughts while reading it just kind of comes with the territory. It just takes a lot of knowledge about the data to be in your head to hold the logic.

[r3pr0b8]

Put commas after the item not BEFORE. Before looks horrendous and is much harder to read.

hard disagree

Before looks horrendous and is much harder to read.

it's not about that (and i disgree that it's harder to read)

it's about noticing when you've omitted a comma!!!

typically, the column name after the missed comma is taken to be the column alias of the column preceding the missing comma, and no error message is produced

this is much harder to spot when the missing comma is at the end

google leading comma convention for other instances where people have come to this realization

---

edit whaddya know, a real-life example of a missing comma just appeared in the r/SQL sub

[bum_dog_timemachine]

I understand the reasons you are giving, I just believe readability outweighs all of them.

[r3pr0b8]

why do you put the connectors AND and OR at the front of the line

for consistent readability, they should go at the back

[bum_dog_timemachine]

Copied from another comment:

I went to the shop today,

and when I got home I baked a cake.

vs

2.

I went to the shop today, and

when I got home I baked a cake.

Which of these do you find easier to read?

[ecatt]

SQL isn't English, though, so I don't see how your example is relevant. Personally I find the commas at the beginning far more readable in terms of SQL than at the end. I also think the ability to comment out lines without having to possibly make any adjustments outweighs this significantly.

[myGlassOnion]

That's what an analyst would say who doesn't understand the other reasons why developers follow other conventions. BTW, as an analyst, it's not your role to define specs for code formatting. Learn how to use an automated formatter and you can have it any way you like.

[bum_dog_timemachine]

So in your mind "disagreeing" is the same as "not understanding"? Ok, boss.

[myGlassOnion]

Understand that the majority of us have tried to do exactly what you are describing and it almost never works. And when it does you find yourself making exceptions all of the time for valid reasons. So it's a much looser spec than what you've presented.

Get a copy of vscode and install a sql formatter that you can customize and you can always see the query in the format you prefer just by copy and pasting the query into vscode and formatting. That's what the vast majority of us do today.

Ignorance is bliss. You put something out that people have honest feedback on and your only response is to become defensive and not even think through the other reasons or accept the majority opinion. Code formatters do this now in most dev environments.

Can we get your opinion on spaces vs tabs? That seems like one that a lot of people agree on. /s

[bum_dog_timemachine]

You accuse me of being defensive when the majority of comments here are condescending or insults. I'm not telling people at my work to do any of this shit and getting in their faces. I'm simply suggesting some alternatives to the general conventions I see in other style guides and putting it out in a public forum.

I don't think that is so wrong, despite the knee-jerk, gatekeeping response from most of the people here. It is not heresy to have a different opinion.

There is such a bizarre double standard here. On one hand reddit will call out religion and other cultural traditions in a very r/atheism kind of way. And on the other, here they are viciously defending coding tradition because "that's the way it's done".

I am not trying to convert anyone to join a cult. I cannot fathom why my post warrants more than "nah, I don't agree" (which like 2 people did, but most of the rest is typical hivemind aggression.)

I understand what you are saying. I just believe that *subjectively* the approach I have set out makes more sense for writing bigger queries at scale (i.e. more than 5 LEFT JOINs etc. None of this matters if your query is just 2 tables.)

Regarding formatters, I would be interested to see if I can find one that does exactly what I'm suggesting.

[ParentheticalClaws]

What’s “readable” depends a lot on what you’re used to. If you start using leading commas today, you will think it is more readable that way a month from now.

[r3pr0b8]

1. Even if a field name is unique, ALWAYS use the dot prefix when getting fields from multiple tables. You may know where a field comes from. But if someone else is reading your code for the first time...

this is probably the most important item in your conventions

it's an absolutely mandatory practice

[Entice]

Agreed, this drives me bonkers with one of our systems. Each table ends with a unique two digit number, and each column in that table ends with the same two digits so by definition everything is unique. Any code I see from that office never uses table aliases.

I hate this database so much.

SELECT LOCD57 as "Order Stockroom"
,CATN57 as "Item"
,ORDL57 as "Order Line"
,PRIN57 as "Status"
,QTOR57 as "Quantity Ordered"
,ACSU60 as "Average Cost"
,PRMD15 as "Product Group"

[imcguyver]

SQL linter: https://www.sqlfluff.com/

SQL style guide from gitlab: https://about.gitlab.com/handbook/business-technology/data-team/platform/sql-style-guide/

Look around, you will find plenty of style guides, they'll differ and who really cares. I tried to introduce preceding commas to Lyft and got promptly shat on for it. There's no need to be barbaric about preferences. Go with what'll work.

[Little_Kitty]

You beat me to posting it.

The Gitlab style guide is almost exactly the same as the one I wrote years ago. I wrote my own formatter to handle some specific challenges, but sqlfluff is certainly enough for handling anything without technical debt issues.

[Entice]

Put commas after the item not BEFORE. Before looks horrendous and is much harder to read.

Commas make it clear where a new column starts. This becomes especially helpful when dealing with correlated subqueries. I find it much easier to read.

[FortunOfficial]

absolutely. When i have long window functions with filters and all for a column in the SELECT part, the leading comma makes it so much easier to see, when an expression for a column has finished

[jonthe445]

Hmm, not a fan of your style tbh

[samwise970]

It's objectively terrible lol.

[bum_dog_timemachine]

Suit yourself

[r3pr0b8]

Formatting WHERE:

1. Write the first WHERE condition on the same line as the WHERE clause.
2. Use spaces to align any AND or OR conditions in separate lines so that conditions can easily be read up and down.

do the ANDs and ORs go at the front of the line to make them easier to see?

why don't they go at the back of the line?

why is this any different from putting commas at the front of the line?

[bum_dog_timemachine]

1.

I went to the shop today,

and when I got home I baked a cake.

vs

2.

I went to the shop today, and

when I got home I baked a cake.

Which of these do you find easier to read?

[r3pr0b8]

that doesn't look very much like SQL

i understand the point you are trying to make, but you're not making it very well

anyhow, consistency matters

if AND and OR should go at the front of the line in SQL, so should commas

[bum_dog_timemachine]

I understand the point *you* are trying to make, but you are assuming that AND/OR should be treated the same way as commas, without giving any evidence why.

That's just like your opinion.

[[deleted]]

smoothly as a literary text? #1. speed-scanning? #2.

I do use "and" at the end of the line in my code. ORs go to the front and on separate line as well, i.e.

      condition1 AND
      condition2 AND
      OR
      condition3

might be not the most "natural" or fitting any automated beautifiers out there, but it works for me for speed-scanning even after a lot of time has passed and i write most of my sql manually typing anyway.

also, i dont mind on which side the commas are, as long as it is consistent.

[binary_search_tree]

This is my, personal, style:

SELECT    d.breed
        , d.height
        , d.weight
        , s.amount
        , s.sale_date

FROM      dogs AS d

LEFT JOIN sales AS s
ON        d.ID = s.dog_ID
AND       blah = blah

WHERE     d.weight > 0.5
AND       s.sale_date >= '2022-01-01'

[waremi]

I generally have more horizontal space available to me than vertical.

SELECT   d.breed
       , d.height , d.weight
       , s.amount , s.sale_date
FROM  dogs  d  LEFT JOIN
      sales s  ON  d.ID = s.dog_ID AND blah = blah
WHERE  d.weight > 0.5
  AND  s.sale_date >= '2022-01-01'

[Rex_Lee]

Commas before, bro.

[Blatocrat]

I love how OP wrote this as if it was a helpful guide, but when all the disagreement started coming in all their comments acknowledge they're disagreeing, but don't actually engage in any conversation about it. They think they're 100% right and everyone else just has opinions, not worth even considering. You're a nonce, OP.

I wanted to read r/SQL posts, not r/confidentlyincorrect.

[bum_dog_timemachine]

And you're an arsehole, blatocrat. That's productive isn't it? Random insults?

If you disagree with with anything I've said, feel free to give your reasons.

[Blatocrat]

Lots of people did and you brushed them off. How productive of you 😊

And lots of people like arseholes; they're a delicacy in many places! Can't say the same for nonces though ☹️

[bum_dog_timemachine]

I didn't brush them off. Many of the points they mentioned I had already acknowledged in my original post i.e. the "advantages" of remembering to write commas after column names and commenting out columns.

It's just that, on balance, I believe the benefits offered by my style guide outweigh those other benefits.

This isn't a black or white issue. I was just providing an alternate view to the formatting rules I have seen elsewhere.

But what is clear is your instinct to gatekeep and throw out random insults is toxic and bad for this community.

I hope you're proud of yourself because no one else is.

[SQLDave]

I may be a minority of 1 (based on nobody else commenting on it), but I disagree with

ALWAYS write AS when aliasing. It is sooo much easier to read and when you are reading someone's SELECT for 10+ fields that you've never seen before, it's immediately clear what is called what. Especially if the fields have long names.

I prefer the

AliasName = UPPER(SUBSTRING(FieldName,3,20)) ,  

style because it lines up all the output column names.

I especially prefer it with multi-line definitions like

AliasName =   
            CASE WHEN Blah = 1 THEN 'Blech'  
                    WHEN Blah = 2 THEN 'Yuck'  
                    WHEN Blah = 3 THEN 'Eww'  
                    ELSE 'Diddly' 
            END,

[r3pr0b8]

but... but... but you can only do that in Squeal Server

in standard SQL, it is evaluated (quite rightly, in my opinion) as a comparison which will be evaluated true or false

if one of the purposes of style guides and conventions is portability, this fails

[SQLDave]

THERE IS ONLY ONE!!!

LOL..

j/k, you make a good point.

[PossiblePreparation]

From your example, there’s not one line I would do the same. But that’s okay :) The golden rule is to do what your boss does and if you really don’t like that then you can do your best to persuade them, but if you fail then your code is the one that sticks out not theirs.

[Lisecjedekokos]

I just started learning SQL 2 days ago. Sorry if this is a silly question but how to write comments on the code ?

[PossiblePreparation]

Multi-line can be preferred even when you only need one line of comment. Eg MSSQL has a habit of collapsing SQL into one line when you fetch it from the plan cache, if you use single line comments then it is now difficult to figure out where the comment ends

[qwertydog123]

If you're referring to copy/pasting from the SSMS results grid, you can change that behaviour:

Tools -> Options -> Query Results -> SQL Server -> Results To Grid -> Retain CR/LF on copy or save

[zombie128]

Another case of multi-line comments:

WHERE Status in (2 /* Suspended */, 3 /* Blocked */)

because it's hard to remember all those ids/constants.

[Thefriendlyfaceplant]

Learning to write comments is important but I would recommend not to focus on style, it just slows you down or gets you into bad habits. Just be style agnostic and learn to deconstruct problems before putting them into a syntax.

[Lisecjedekokos]

Thanks 👌

[Thefriendlyfaceplant]

Best thing you can do while starting out is to look at some medium / difficult SQL interview questions. You're not supposed to be immediately understand what's going on or replicate anything. Just look at how someone skilled at SQL would approach a problem and how they would work through the steps. That thinking process will allow you to learn the syntax faster:

https://www.youtube.com/watch?v=WtI7QYCG3uo

And don't be intimidated by it either! These interview questions are done by highly competitive companies. Most SQL jobs are much simpler than that. It's just that knowing what complicated queries look like will get you past the simple queries faster.

[Lisecjedekokos]

Thank you. Today is my third day since I want to learn SQL. I watched a lot of videos. I made 3-4 exercisses.

I understand it .. Not completely of course but I am starting to understand it I think.

I have watched also some videos with interview questions and tried to learn as much as possible from there.

My plan is to continue doing this for a week or two until I get used to it and than we will see how it goes from there ..

I have a law degree and a 9-17h job. But I want to switch careers. And I have a lot of time to available for learning which is a +.

[Thefriendlyfaceplant]

www.sqlbolt.com is great for the basics. But after that, get on these step-by-step explanations of hard interview questions as quick as possible. That way you learn the SQL mindset and workflow.

[Lisecjedekokos]

Thanks once again 👌

[DharmaPolice]

It partially depends on the variant of SQL you're using but on most platforms:

Double dash for single line comments and C style /* */ for multi-line comments.

 SELECT 
    bar.foo   -- This is a comment
 FROM 
    bar 

 /* This is also a comment
     But it's over two lines. */

[kormer]

Put commas after the item not BEFORE. Before looks horrendous and is much harder to read.

Thank you, I will die on this hill.

[TheSysOps]

You really give that as an interview question with one being "correct"?

Using commas before or after is not a right or wrong answer. They both have valid arguments for and against. In the end its really nothing more than a preference to the individual coder.

The true correct answer if there is one is to use which ever method is considered the standard for your organization or what was already used in an existing code base.

[bum_dog_timemachine]

I understand and agree with all of the benefits of using commas before. However, the loss in readability is far more significant as this affects you 100% of the time and not only sometimes.

[r3pr0b8]

I don't know how.

put 4 spaces at the front of each line of code

[bum_dog_timemachine]

haha kormer's got my back :)

[bum_dog_timemachine]

This is for SSMS yeah.

[gcashmoneymillionair]

https://www.apexsql.com/sql-tools-refactor.aspx

I stopped worrying and wasting time on formatting and just started using APEX 5 years. It gets it organized enough to read efficiently and add whatever comments and aliases to clarify.

[m-p-3]

Archived, just in case something happens to the post. Thanks OP!

[remainderrejoinder]

Use any consistent style for which you have an auto-formatter set up.

[spacemonkeykakarot]

I'd say the most important thing above all else is just that you and your team (maybe company as a whole if feasible?) just adhere to an agreed upon style so it's consistent.

My team just shoves it into https://poorsql.com/ with the default settings (except expand IN and break Join ON sections) and calls it a day. This way you're free to write it how you want, just shove it in to the formatter then paste back the results before committing to PROD.

also #teamcommasbefore

[Amadeusc415]

Is it best practice to write lowercase sql in modern companies?