Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2012
    Posts
    45

    Unanswered: Comma join -vs- Inner join

    Greetings all. I periodically have to re-write a query that was written using a comma join back in the day, and now criteria have changed so a LEFT join is appropriate. I have been trying to get users to not use comma joins, not only because it is sometimes difficult to easily identify join criteria versus where criteria when it is all crammed in the where clause, but also because there is the possibility of the comma join syntax not being supported in a future release (and I have not heard anything to indicate this would ever happen, just saying, thats all). However, one user has told me comma joins get better performance versus the same query using INNER JOIN and ON clause, but I cannot find any evidence to support this assertion, not do I have access to any stats. I don't have any pre-conceived notions about it, I'm simply trying to understand if there is any truth to it. Anyone know of a document that compares the two methods? We are using 9.2 on zOS. Thank you.

    Greg

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I don't know of any document, but there was a hot discussion on this topic a few years ago:
    http://www.dbforums.com/db2/1661968-...out-joins.html

    I personally like and understand the old method, but the only queries I write is to access the system catalog tables.

  3. #3
    Join Date
    Aug 2012
    Posts
    45
    Yep, and I personally prefer using JOIN. Anyhow, I only found one blog where someone actually did some performance testing, and both methods produced identical execution plans and had the same performance. I guess it's all what you like. Thanks

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by gsnidow View Post
    [...]However, one user has told me comma joins get better performance versus the same query using INNER JOIN and ON clause
    No, there is absolutely no performance difference. Certainly for DB2 z/OS, I'm 100% sure about this. (Forgot where I got this evidence. Most likely from Terry Purcell.)
    Last edited by Peter.Vanroose; 08-31-12 at 16:46.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I, as well, try to get everyone to use explicit syntax as it does make maintenance much easier. There have been the rare SQL statement that takes a different access path, but can, usually, be corrected.
    Also, some like to keep non-join clauses in the WHERE clause when using explicit syntax, but I much prefer it within the ON clause.

    For instance:
    Code:
    SELECT SOME_COLS
       FROM TABLE1 A
    INNER JOIN TABLE2 B
       ON A.JOIN_COL = B.JOIN_COL
      AND B.COL7 BETWEEN ? AND ?
    WHERE A.COL1 = ?
    The main reason being is that you easily see all conditions in which you see data from table2, also, as you mentioned business rules changing and you now need this to be a LEFT OUTER JOIN. If the predicate were in the WHERE clause it would change what data you see returned by the SQL and you would not have a true outer join, as you would only see data from table1 and table2 where col7 is between ? and ?. Instead of all data from table1 where col1 = ? and nulls supplied for table2 data when there is not a corresponding row in table2. Essentially, negating your outer join.
    Here are examples of the OUTER join.
    Correct method:
    Code:
    SELECT SOME_COLS
       FROM TABLE1 A
    LEFT OUTER JOIN TABLE2 B
       ON A.JOIN_COL = B.JOIN_COL
      AND B.COL7 BETWEEN ? AND ?
    WHERE A.COL1 = ?
    Incorrect method:
    Code:
    SELECT SOME_COLS
       FROM TABLE1 A
    LEFT OUTER JOIN TABLE2 B
       ON A.JOIN_COL = B.JOIN_COL
    WHERE A.COL1 = ?
      AND B.COL7 BETWEEN ? AND ?

    Dave Nance

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    In the query rewrriten phase , the db2 optimizer will change all the inner join to comman join ,SO there is no differece betwwen them,especially for performance。

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    There is 1 situation where DB2 forces you to code the 'old comma style' SQL: when you create a MQT. The JOIN keyword is not accepted there (perhaps solved in V10?)
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Posting Permissions

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