Some simple SQL Rules

[via Jeff at]

I really liked this list of rules that Jeff has put together and got quite a kick out of them as I was nodding my head as I read each one. The first one about always fully qualifying column names caused me a few head aches a few weeks ago on a multi developer project. One of the developers added a new column and fixed and tested their stored procedures, not realising that they had broken the stored procs of one of the other developers who had not fully specified their column names.

But the one that prompted me to put this post together was “Avoid RIGHT OUTER JOINS” . I agree with Jeff here, if  you find yourself adding right outer joins to a query *stop* and re-write it. It is so much harder to maintain a statement that has a mix of right and left joins. If I have to do this I always endup having to sit down and figure out which is the “core” table and more often that not (unless the change is trivial) I end up restructuring the joins so that they are all left joins.

Print | posted on Wednesday, March 15, 2006 4:53 PM