What is the difference betweeen:

SELECT a.*,b.* FROM a, b WHERE a.somefield = b.otherfield

and

SELECT a.*,b.* FROM a JOIN b ON a.somefield = b.otherfield

???

Thank you!


EDIT: Sorry, this is the wrong forum, I meant to write in the "beginners" forum...

one of them is an inner join written using the older, deprecated, comma-style join syntax, while the other one is an inner join written using explicit JOIN syntax

Deprecated? You are completely wrong about that. Look at a DB2 explain report (query re-write section) and you will see the "explicit join" syntax will be re-written to the "old" style.

There is no difference.

did i say that? i meant "disrespected"

okay, maybe not deprecated as in an official announcement from the vendor that the feature will no longer be supported as of such-and-such version...

but rather deprecated as in you shouldn't do it this way, even if it works

I like the "old" style. Easier to understand and observe from the first glance. Simplicity at its finest.

No, that is not right either. I always use the old style, and always will. So does the DB2 query rewrite and optimizer. The "old" style will always work, forever.

Explicit JOIN keywords are for donkeys.

and outer joins, presumably

also, rudeness does not look very good on an IBMer, but it is not altogether unexpected, either

I am not a IBM employee. But I was not being rude, just explaining what I think. I take your comments about deprecated code (that will infact will never be deprecated) as more than a little insulting to those of us who prefer the "old" way.

95% of the time, extensive use of OUTER JOINs can cause very poor SQL performance and is often an indication of poor database design (usually normalizing beyond 3NF). In many cases, these can also usually be written without explict OUTER JOIN syntax also.

disagree with impunity. If you want to join tables, do it EXPLICITLY. To me, implicit joins are lazy, weak, and generally written by sub-par developers. I will arm-wrestle on this point. For houses. Or hell, wives. Implicit joins are for girley-boys that would blindely kiss across the dividers in barroom restrooms.

I must state here for the record, that those that hold this viewpoint are indeed , SQL donkey's arses, and will never work in a company in which I am employed.

If you can't be explicit, then you should be unemployed....or at minimum, be tossing french fries across the counter.

I never said anything about "arses".

Do you have something against donkeys? The Democratic Party has had the donkey as their mascot for more than 150 years.

Anyway, in poker terms, donkey is a naive or inexperienced person, not an arse.

I'm with you on this one, Cougar. I'm used to seeing the "older comma-style" / "implicit join" syntax and find it easier to understand.

Even using your definition of the moniker, I still feel my comment is spot on. Why would someone use implicit context when explicit is available? Isn't it better to have People Who Come Later understand what you are trying to do than it is to have someone think "Hmmm...looks like they mean to do this..."?

Color me jaded after 35+ years in the industry, but I tell ya...I would STRONGLY argue that in pretty much ANY 'puter-nerd endeavor, explicit is like a billion times more valuable than implicit.

But that is just me.

I can tell you from my experience (yours may be different) that when I see an SQL statement written by someone else with a lot of explicit JOIN keywords, it usually extremely complex and if there are multiple WHERE clauses and multiple parenthesis (which there often are) then it is very difficult to understand how the WHERE clauses are to be applied. I have always been able to understand a join that is written without a JOIN keyword.

I spend a lot of time looking at other people's SQL when I do SQL Snapshots and performance tuning.

The DB2 query re-write and optimizer converts all of the this JOIN stuff to the "old style" join syntax, so anyone who says it is "going away" or is "to be discouraged" is out of line.