06 July 2007 08:10
tonyrogerson
SQL Style - FROM x,y,z or INNER JOIN; We are all Cowboy Coders
In ANSI 92 there are two styles of writing SQL, there is what is commonly referred to as the old or ANSI 89 style, that is the style where you put the join criteria in the WHERE clause; the other style is referred to either ANSI 92 or the new style and it is where you put the join criteria in the FROM clause using INNER JOIN, OUTER JOIN etc..
Coming fresh from yet another battle with that Joe Celko bloke from the public news groups (people who worship him should read no further for I am about to blaspheme) I thought I’d write up this entry. It appears that anybody who writes using the new ANSI 92 style is now a ‘Cowboy Coder’. Wow, well – I guess that means the majority of the developers in IT, me, a few world experts, anybody who has written a book on SQL Server and the doc writers of Books Online are all Cowboy Coders. Thanks to Itzik Ben Gan who has obviously too much time on his hands....
It all starts with Best Practice; in Books Online it clearly states that the recommended style is to use the new ANSI 92. Apparently in DB2 there are some optimisation problems with using the new style (yet to be confirmed, I’m sceptical).
Celko states that “Best Practice is not local, but global”, oh, and apparently I missed the “entire Software Engineering and Structured Programming revolution”, oh, and I'm a Barbarian....
Think about that statement, it's at odds with a MS recommendation and how we all work; Celko’s personal best practice is to use the old ANSI 89 style, but the MS SQL Server best practice is to use the new ANSI 92 style; what do you choose? Apparently choosing the MS SQL Server best practice, you know – that product we all code against would relegate us to being a “Cowboy Coder” for using “local and immediate solutions”.
Oh please, what is this guy on?
Best practice being a global thing is actually right, but local recommendations need to override because they have come from the vendor who made the product and it’s a best practice or recommendation. In life it’s the same, local bylaws change global laws.
Ok, why am I so darn angry about this guy and his stance; well, unfortunately he’s one of those internet bully blokes who likes to dismiss and put down newbies or people that at times struggle with SQL; but it’s more fundamental than that, the majority of folks use the ANSI 92 new style coding, in fact some dev’s will never have seen the old syntax, the new syntax was introduced in SQL Server 6.0 many moons ago. Like many, on seeing the old syntax I always rewrite to the new to clearly understand what is going on (it usually takes a while too!) – the logic behind the query, the join relationships etc... In the old syntax the logic etc... was buried in the WHERE clause, remember the days of forgetting the join condition and causing a Cartesian product?
Anyway, let’s have a look at some styles....
Old ANSI 89 style would go something like this...
select *
from sys.objects as o,
sys.columns as c,
sys.sql_modules as sm
where o.object_id = c.object_id
and sm.object_id = c.object_id
and o.type_desc = 'VIEW'
The join criteria is mixed up in the WHERE clause, what you are actually doing is a cross join query and then restricting the results.
The equivalent new style ANSI 92 query is below
select *
from sys.objects as o
inner join sys.columns as c on o.object_id = c.object_id
inner join sys.sql_modules as sm on sm.object_id = c.object_id
where o.type_desc = 'VIEW'
The join criteria is distinctly separate from the filter, relationships are much clearer.
Formatting is another aspect that is key here...
select *
from sys.objects as o
inner join
sys.columns as c
on o.object_id = c.object_id
inner join
sys.sql_modules as sm
on sm.object_id = c.object_id
where o.type_desc = 'VIEW'
When arguing the syntax you often see Celko format the new style like the above, frankly it’s totally unreadable so I guess that’s where his real problem of migrating to the new syntax is.
I mean, would any of us format like this (sorry, I actually know a few who do – sorry folks, feel free to comment – I’m being adversarial)?
select *
from sys.objects as o,sys.columns as c,sys.sql_modules as sm
where
o.object_id = c.object_id
and
sm.object_id = c.object_id
and
o.type_desc = 'VIEW'
Again,
unreadable, it’s
the equivalent
of me
writing this
sentence
like I’ve
just done.
Crazy!
But one of the real problems with the old style syntax is that people where forever forgetting to put all the join conditions in the WHERE clause so we ended up with Cartesian products...
select *
from sys.objects as o,sys.columns as c,sys.sql_modules as sm
where o.object_id = c.object_id
and o.type_desc = 'VIEW'
I can’t do that with the new syntax, I’d need to mix old and new...
select *
from sys.objects as o
inner join sys.columns as c on o.object_id = c.object_id
inner join sys.sql_modules as sm
where o.type_desc = 'VIEW'
The above gives an Invalid syntax error.
Another problem (thanks SQL Menace) is that if you write your query using the old syntax and you then need to make the inner join an OUTER JOIN you need to rewrite to the new style anyway.
To sum up, really you should all be using the new style, I know it’s a new style if you have come over from Oracle because I think it’s only been a couple of years that this syntax has been in the product, but learn it - get used to it; I remember I truely hated the new syntax until I got my head round it and now I really see the benefits.
And as for Celko, well, I think somebody coined a good phrase that is very appropriate – I think Celko is suffering from Unixitis which is a belief that something is better because it is more obscure and complex, thus making you morally superior because you understand it.
Filed under: SQL Server, Cole Joke