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
-