🔒 Locked

Quick SQL question

Started by capn_midnight Oct 5, 2006 at 9:56 AM 4 replies 900+ views

Original Post

Oct 5, 2006 at 9:56 AM

Since this isn't game related, this doesn't fit in any of the technical forums. I'm debugging an application right now, trying to make everything work properly. It's a classic dailyWTF case, the orginal development company went under a few years ago and the original developer moved to Canada or something. Anyway, I see a lot of queries of the form: SELECT A.*, B.Col1, B.Col2 FROM Table1 AS A INNER JOIN Table2 AS B ON (A.PrimaryKey = B.KeyToTable1) WHERE (blah blah blah) How is that different from: SELECT A.*, B.Col1, B.Col2 FROM Table1 AS A, Table2 AS B WHERE A.PrimaryKey = B.KeyToTable1 AND (blah blah blah) Because so far the results are the same, and I think my version is cleaner

leiavoia

Oct 5, 2006 at 10:03 AM

The first version is the more standards complient, technically. That said, i think the syntax is confusing and i use the second version if i don't have to perform any trickier table joins.

They do perform differently in certain situations though, so you might want to benchmark them.

capn_midnight

Oct 5, 2006 at 10:10 AM

Quote:
Original post by leiavoia
The first version is the more standards complient, technically. That said, i think the syntax is confusing and i use the second version if i don't have to perform any trickier table joins.

They do perform differently in certain situations though, so you might want to benchmark them.

the data set is small (around 10MB total), each query retrieves only a small portion of it at any one time, and performance isn't an issue anyway.

Thanks.

[Edited by - capn_midnight on October 31, 2006 11:10:24 AM]

Wan

Oct 5, 2006 at 10:11 AM

Your version equals a cross join, but because of the where clause, actually produces a inner join.
Personally, I'd use the first version. It's clearer what kind of join (inner/outer/right/left/cross etc.) it is. But it is really a matter of personal preference.

capn_midnight

Oct 5, 2006 at 10:13 AM

Quote:
Original post by WanMaster
Your version equals a cross join, but because of the where clause, actually produces a inner join.
Personally, I'd use the first version. It's clearer what kind of join (inner/outer/right/left/cross etc.) it is. But it is really a matter of personal preference.

(...refreshes self on differences in join types...)
ah, see, when I took DBMS in college, we were instructed to create inner joins using the where clause in that fashion. I seeme to remember Oracle 6i not having an inner join keyword at the time, idk. Thanks.

eedok

Oct 5, 2006 at 10:22 AM

The first ones syntax is supposed to be more resistant to cartesian joins than using the where clause to do the join.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.