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 11 March 2009 20:15 by simonsabin

Comments

11 March 2009 21:54 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?

11 March 2009 22:02 by jamiet

# re: Where should the ON clause go?

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

11 March 2009 22:45 by SSIS Junkie

# Formatting T-SQL

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

12 March 2009 08:34 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

12 March 2009 13:31 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.

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

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

13 March 2009 16:26 by Pythian Group - Blog

# Pythian Group - Blog

Pingback from  Pythian Group - Blog

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

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