May 9, 2008 at 8:27 am
The agency that I work has is funded by the city where we work. (We're not city employees, but we work under contract to the city.) The city contracts out to another agency to do some data of our databases; basically they're mining our data and generating reports for the city. This other agency tends to high college students to write their code, for analyzing the data. I am not sure why these students do what they do, but they tend to write very long stored procedures, making use of lots of temporary tables, many cursors, etc. Some of their SP's are a several thousand lines long, but the majority of their SP's are only in the order of several hundreds of lines long.
Anyway, when we write SPs, or any SQL using SELECTS, our code tends to look something like this:
SELECT FirstName, LastName, Address, City, State, ZipCode FROM
Table1 INNER JOIN Table2
ON Table1.ClientNumber = Table2.ClientNumber
INNER JOIN Table3
ON Table1.ClientNumber = Table3.ClientNumber
WHERE LastName = 'Smith'
Whereas the students that work at this other agency tend to write their SQL SELECT statements like this:
SELECT FirstName, LastName, Address, City, State, ZipCode FROM
Table1, Table2, Table3
WHERE Table1.ClientNumber = Table2.ClientNumber
AND Table1.ClientNumber = Table3.ClientNumber
AND LastName = 'Smith'
I am just not used to stringing all of the tables together after the FROM clause and not linking them together with ON and specifying the connections, etc. In fact, I am not really sure what sort of style of SQL that is which the students use. It seems to work, though. Anyway, what sort of style of SQL is it that these students are using, where they don't join anything and just link tables together in the WHERE clause?
Kindest Regards, Rod Connect with me on LinkedIn.
May 9, 2008 at 8:37 am
You are using ANSI standard style coding when using the JOIN...ON clause. The other style is the old way of writing SQL. Works fine in SQL Server 2005 as long as you are doing inner joins. SQL Server 2005 won't support the old style outer joins (*=, *=*, or =*).
😎
May 9, 2008 at 8:52 am
And if you forget a condition in your where clause linking two of the tables together, it is an implicit CROSS JOIN.
May 9, 2008 at 8:58 am
I'd send over an ANSI guide to the students, get them to change. You can take some of their examples, explain how you change their queries to a more standard way of writing, and then see if you can get them to change.
It took me awhile to make the change a few years back, but I'd never use the old style anymore.
May 9, 2008 at 9:10 am
Unless, like me, you get stuck with half of the applications you are dealing with being on an old version of Oracle.
Then you have to constantly switch between the two standards.
May 9, 2008 at 9:49 am
It's also worth pointing out that the ANSI 92 joins will cause problems in SQL Server 2005 and above when doing outer joins. From BOL: The outer join operators (*= and =*) are not supported when the compatibility level of the database is set to 90.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 9, 2008 at 11:38 am
Ansi joins are the way to go, they are much easier to understand for all databases.
Marvin Dillard
Senior Consultant
Claraview Inc
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply