In my last job, we worked on a very database-heavy application, and I developed some formatting standards so that we would all write SQL with a common layout. We also developed coding standards, but these are more platform-specific so I'll not go into them here.
I'm interested to know what other people use for SQL formatting standards. Unlike most other coding environments, I haven't found much of a consensus online for them.
To cover the main query types:
select
ST.ColumnName1,
JT.ColumnName2,
SJT.ColumnName3
from
SourceTable ST
inner join JoinTable JT
on JT.SourceTableID = ST.SourceTableID
inner join SecondJoinTable SJT
on ST.SourceTableID = SJT.SourceTableID
and JT.Column3 = SJT.Column4
where
ST.SourceTableID = X
and JT.ColumnName3 = Y
There was some disagreement about line feeds after select, from and where. The intention on the select line is to allow other operators such as "top X" without altering the layout. Following on from that, simply keeping a consistent line feed after the key query elements seemed to result in a good level of readability.
Dropping the linefeed after the from and where would be an understandable revision. However, in queries such as the update below, we see that the line feed after the where gives us good column alignment. Similarly, a linefeed after group by or order by keeps our column layouts clear and easy to read.
update
TargetTable
set
ColumnName1 = @value,
ColumnName2 = @value2
where
Condition1 = @test
Finally, an insert:
insert into TargetTable (
ColumnName1,
ColumnName2,
ColumnName3
) values (
@value1,
@value2,
@value3
)
For the most part, these don't deviate that far from the way MS SQL Server Managements Studio / query analyser write out SQL, however they do differ.
I look forward to seeing whether there is any consensus in the Stack Overflow community on this topic. I'm constantly amazed how many developers can follow standard formatting for other languages and suddenly go so random when hitting SQL.
I am of the opinion that so long as you can read the source code easily, the formatting is secondary. So long as this objective is achieved, there are a number of good layout styles that can be adopted.
The only other aspect that is important to me is that whatever coding layout/style you choose to adopt in your shop, ensure that it is consistently used by all coders.
Just for your reference, here is how I would present the example you provided, just my layout preference. Of particular note, the ON clause is on the same line as the join, only the primary join condition is listed in the join (i.e. the key match) and other conditions are moved to the where clause.
select
ST.ColumnName1,
JT.ColumnName2,
SJT.ColumnName3
from
SourceTable ST
inner join JoinTable JT on
JT.SourceTableID = ST.SourceTableID
inner join SecondJoinTable SJT on
ST.SourceTableID = SJT.SourceTableID
where
ST.SourceTableID = X
and JT.ColumnName3 = Y
and JT.Column3 = SJT.Column4
One tip, get yourself a copy of SQL Prompt from Red Gate. You can customise the tool to use your desired layout preferences, and then the coders in your shop can all use it to ensure the same coding standards are being adopted by everyone.