Last updated on April 18th, 2012
Old SQL syntax vs. New ANSI syntax.
The short: to answer the question of whether you should use SQL vs ANSI, use ANSI.
Let me tell you a story…
A young man is frantically programming a website. This is his first ever real site he has worked on so the learning curve is fairly steep. The website is to be used as an interface to a database storing a couple hundred thousand records.
Being quite used to desktop programming, this junior programmer is impatient and is coding using the old trial-and-error approach.
One query he is working on involves just a few tables being joined together. Pretty simple stuff… It went something like,
SELECT * FROM specimens SP, box B WHERE SP.box_fk = B.box_id
Then in a frenzied dash of debugging, he realizes he doesn’t want these tables joined so he comments out the last line,
SELECT * FROM specimens SP, box B -- WHERE SP.box_fk = B.box_id
He then faithfully tests his changes on his test database, notices no strange behavior and moves it over to the production side.
In his usual frantic pace, he executes the above statement three or four times from the webpage with no response. So he closes it, puzzled for a second, and then brushing it from his mind, he begins working on another project.
About 3 and a half hours later, his supervisor comes running in asking why he’s getting a million phone calls and emails from IT saying his queries are shutting down the production databases.
And ever since this unfortunate incident, this young programmer used the ANSI syntax religiously.
So what happened?
1) A cross join.
Commenting out the last line resulted in each record in each table joining with each other to produce a enormous amount of records. Clearly not what he wanted.
2) The test database contained a small amount of records in it. Small enough in fact that the rows produced by cross join could be successfully returned. However the production database contained hundreds of thousands of records; this cross join probably was trying to pull millions of rows back.
By the way the new ANSI syntax (the good and holy way) of the same query would look like this,
SELECT * FROM specimen SP LEFT OUTER JOIN box B ON SP.box_id = B.box_fk
Two things I learned.
1) Make sure to have a decent amount of test data in your development database.
2) Use the new ANSI syntax.