Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    Join Date
    Nov 2010
    Posts
    8

    Question Unanswered: A basic question about JOINs

    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...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ororo View Post
    What is the difference betweeen:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by r937 View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ororo View Post
    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...
    There is no difference.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Marcus_A View Post
    Deprecated?
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by Marcus_A View Post
    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.
    I like the "old" style. Easier to understand and observe from the first glance. Simplicity at its finest.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by r937 View Post
    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

    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Marcus_A View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by r937 View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by Cougar8000 View Post
    I like the "old" style. Easier to understand and observe from the first glance. Simplicity at its finest.
    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.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  11. #11
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by Marcus_A View Post
    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.
    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.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by TallCowboy0614 View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by Cougar8000 View Post
    I like the "old" style. Easier to understand and observe from the first glance. Simplicity at its finest.
    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.

  14. #14
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by Marcus_A View Post
    Anyway, in poker terms, donkey is a naive or inexperienced person, not an arse.
    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.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by TallCowboy0614 View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •