CODEWITHSUNDEEP
sqlcoding-style
Mr Shoubs
Mr Shoubs

Reputation: 15389

Effect of style/format on SQL

Ignoring version, what are the best practices for formatting SQL code?

I prefer this way (method A):

select col from a inner join b on a.id = b.id inner join c on b.id = c.id

a colleague prefers another (method B):

select col from a inner join (b inner join c on b.id=c.id) on a.id = b.id

I'd like to know if there is any difference - the query optimiser appears to generate the same execution plan for both. So maybe it is just readability?

This is the first time I've seen SQL written using method B, does anyone else write SQL like this? Personally I find it really difficult to read method B.

EDIT: Please note the code is on one line and in upper case to make both more comparable for the purpose of this question.

Upvotes: 5

Views: 253

Answers (3)

Chris Diver
Chris Diver

Reputation: 19812

My personal preference is

SELECT col1, col2, col3, 
       col4, col5
FROM a
  INNER JOIN b ON a.id = b.id  
  INNER JOIN c ON b.id = c.id
WHERE a.col1 = 1

I think consistency is key, I prefer your way over your colleagues for readability.

Upvotes: 1

Oded
Oded

Reputation: 498972

I think A is more readable, and most sample code out there uses that style. Both parse the same and product the same query plan, so as far as SQL Server is concerned, there is no difference.

I normally also uppercase keywords and indent for readability:

SELECT col 
FROM a 
  INNER JOIN b 
    ON a.id = b.id  
  INNER JOIN c 
    ON b.id = c.id

Upvotes: 1

Prutswonder
Prutswonder

Reputation: 10064

Method B is a subselect-like syntax, but it is parsed the same way as method A. There's no harm in using it. I personally prefer method A too, because it can be read in a lineair fashion.

Upvotes: 1

Related Questions