Where should the ON clause go?

Writing code is a very personal thing. I personally like my SQL to be formatted a certain way.

I put this down to a feature of how your read. I find it amazing and thats how you can specll check without reading the letters. You learn the shape of words and so when the shape is wrong you know something is likely to be spelt wrongly. I believe you use similar skills to read code. You expect certain things to be in certain places and so thats where you look. If the format is different you have to scan the whole block of code.

Similar I guess to how darts players work out numbers. They don't add up they just now that trebbe 18 is 54 and that if they have 150 the out is trebble 20 bull and double top.

Which means code like this I find difficult to read 

select track, level, title, Name, ss.length

  from ConferenceSession cs

  join session           ss  

    on ss.sessionId = cs.SessionId 

  join Speaker           sp  

    on sp.SpeakerId = ss.ownerId

 where cs.Approved = 1

   and cs.ConferenceId = 4

order by length, title, cs.SessionId  desc

I have no easy way of finding the columns that are joined on. I have to scan up and down the On clauses which are interspersed with join clauses and the table names.

Anyway so I like my code formatted like this. with the on clause after the table. I have a big screen and so I'm not too concerned about wrapping text

select track, level, title, Name, ss.length

  from ConferenceSession cs

  join session           ss  on ss.sessionId = cs.SessionId 

  join Speaker           sp  on sp.SpeakerId = ss.ownerId

 where cs.Approved = 1

   and cs.ConferenceId = 4

order by length, title, cs.SessionId  desc

I can easily see the tables being joined to and the columns that they are being joined on.

What do others think?

So to answer some questioners, if I have multiple join predicates I align them

select track, level, title, Name, ss.length

  from ConferenceSession cs

  join session           ss  on ss.sessionId = cs.SessionId 

                            and (ss.approved  = 1 

                              or ss.track     = 'DBA') 

  join Speaker           sp  on sp.SpeakerId = ss.ownerId

 where cs.Approved = 1

   and cs.ConferenceId = 4

order by length, title, cs.SessionId  desc


-
Published Wednesday, March 11, 2009 8:15 PM by simonsabin

Comments

Wednesday, March 11, 2009 9:54 PM by jamiet

# re: Where should the ON clause go?

I personally always write code like this:

select  track

,       level

,       title

,       Name

,       ss.length

from    ConferenceSession cs

join    session ss

on      ss.sessionId = cs.SessionId  

join    Speaker sp

on      sp.SpeakerId = ss.ownerId

where   cs.Approved = 1

and     cs.ConferenceId = 4

order  by

       length

,       title

,       cs.SessionId  desc

but that's just me!! verbose wins over conciseness when I'm butchering T-SQL :)

What do you do if there is more than 1 join

predicate?

Wednesday, March 11, 2009 10:02 PM by jamiet

# re: Where should the ON clause go?

blog formatting may have screwed that up a bit!!!!!

Wednesday, March 11, 2009 10:45 PM by SSIS Junkie

# Formatting T-SQL

Simon Sabin just published a blog post Where should the ON clause go? where he explained his penchant

Thursday, March 12, 2009 8:34 AM by simonsabin

# re: Where should the ON clause go?

I've SQL evolve through the 90s.  I used to be very vertical.  Most code was relatively short.  Finding that now, I have a moniter/tool that lets me see 120 characters wide and I have LOTS more joins.  So I like to be able to see the query at one look...and have been putting more elements on one line.  It also means I may put more clauses on the ON element than just one.  I only use LineFeeds where readability is needed or I would cause someone to scroll

Thursday, March 12, 2009 1:31 PM by unclebiguns

# re: Where should the ON clause go?

Here's a link that show's my preference: wiseman-wiseguy.blogspot.com/.../stored-procedure-trigger-and-udf.html.

I tend to prefer a more vertical style than horizontal.

I have also adjusted a little bit because I am using RedGate SQLRefactor and I haven't taken the time to determine how to get it to format code exactly to my preferences.

Thursday, March 12, 2009 2:42 PM by SSIS Talk » What’s your SQL formatting style?

# SSIS Talk » What’s your SQL formatting style?

Pingback from  SSIS Talk » What’s your SQL formatting style?

Friday, March 13, 2009 4:26 PM by Pythian Group - Blog

# Pythian Group - Blog

Pingback from  Pythian Group - Blog

Wednesday, August 12, 2009 2:33 AM by What???s your SQL formatting style? | ButtonForums

# What???s your SQL formatting style? | ButtonForums

Pingback from  What???s your SQL formatting style? | ButtonForums