Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: Joins (AND conditions, if failed, should be neglected)

    Hi,

    I have a situation where say I have three conditions in my query.

    Select tbl1.user, tbl2.orgname from tbl1, tbl2 where tbl1.userid = ? and tbl1.location = ? and tbl1.org = tbl2.org

    There is a situation where tbl1.org may not have a mapping to tbl2.org.

    In this case i would still like to bring out the user and the orgname as blank instead of having the query not return any value at all.

    Is there a way in which outer joins could be used to achieve this, if yes then how?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Joins (AND conditions, if failed, should be neglected)

    Yes .. Outer joins can do what you want ...

    For the 'How?' part of your question , please refer to the IBM DB2 SQL Reference and/or Grahame Berchall's DB2 SQL Cookbook

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2004
    Location
    Kentucky
    Posts
    3

    Re: Joins (AND conditions, if failed, should be neglected)

    Originally posted by sanjum
    Hi,

    I have a situation where say I have three conditions in my query.

    Select tbl1.user, tbl2.orgname from tbl1, tbl2 where tbl1.userid = ? and tbl1.location = ? and tbl1.org = tbl2.org

    There is a situation where tbl1.org may not have a mapping to tbl2.org.

    In this case i would still like to bring out the user and the orgname as blank instead of having the query not return any value at all.

    Is there a way in which outer joins could be used to achieve this, if yes then how?
    The how part should look like this

    select tbl1.user,tbl2.orgname
    from tbl1 left outer join tbl2
    on tbl1.org = tbl2.org
    where tbl1.userid = ?
    and tbl1.location = ?

    The fields selected for tbl2 that do not have a mapping will contain null values.

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525

    Re: Joins (AND conditions, if failed, should be neglected)

    Originally posted by brknlady
    select tbl1.user,tbl2.orgname
    from tbl1 left outer join tbl2
    on tbl1.org = tbl2.org
    where tbl1.userid = ?
    and tbl1.location = ?
    Probably slightly better like this (not tested)...
    Code:
    select tbl1data.user,tbl2.orgname 
    from ( select user, org
              from tbl1
              where userid = ?
                and location = ?
            ) tbl1data left outer join tbl2 
                             on tbl1data.org = tbl2.org
    The 'on' join will not exclude any rows in an outer join, so you would be better reducing the data set up front in a nested table select.

    Damian
    Last edited by Damian Ibbotson; 01-31-04 at 14:42.

  5. #5
    Join Date
    Jan 2004
    Location
    Kentucky
    Posts
    3

    Re: Joins (AND conditions, if failed, should be neglected)

    Originally posted by Damian Ibbotson
    Probably slightly better like this (not tested)...
    Code:
    select tbl1data.user,tbl2.orgname 
    from ( select user, org
              from tbl1
              where userid = ?
                and location = ?
            ) tbl1data left outer join tbl2 
                             on tbl1data.org = tbl2.org
    The 'on' join will not exclude any rows in an outer join, so you would be better reducing the data set up front in a nested table select.

    Damian
    Very good point. Thanks for the refinement...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    probably slightly better? no, only possibly slightly better

    probably no difference whatsoever

    in any relational database worthy of the name, a true case of six-o'-one or half-dozen-o'-t'other

    leave the optimization to the optimiser

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

  7. #7
    Join Date
    Jan 2004
    Posts
    7
    Hi,

    Thanks a lot guys.

    The query is working perfectly now.

  8. #8
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Originally posted by r937
    probably slightly better? no, only possibly slightly better

    probably no difference whatsoever

    in any relational database worthy of the name, a true case of six-o'-one or half-dozen-o'-t'other

    leave the optimization to the optimiser
    I would have to disagree with you on that point. If the WHERE clause specified a field in the join it is likely that the optimiser would apply that filter before the outer join.

    Typically, a WHERE clause *would* be applied after the join. I'm sure there are circumstances where the optimiser would also be able to use the table stats to rewrite the query, possibly where fields are indexed but I couldn't say for sure.

    Bottom line, it's a bit naive to think that the optimiser will always do the work for you. Sometimes it helps if you can write decent SQL yourself!

    Damian

  9. #9
    Join Date
    Mar 2003
    Posts
    343
    My understanding is that in an indexed situation, a nested select performs not as well since once the data is in the BP it looses it's indexed access property. Can someone clarify?

  10. #10
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Originally posted by cchattoraj
    My understanding is that in an indexed situation, a nested select performs not as well since once the data is in the BP it looses it's indexed access property. Can someone clarify?
    My understanding is that once materialized, a nested table expression is held in memory unless it is so large that it should be written to disk. Retrieval of the data held in memory should be incredibly quick as it involves no disk IO. You would still have the benefit of any indexes used within the nested table expression, so provided you don't expect a massive, materialized results set, I don't see it impacting on performance.

    PS My word is NOT final ;-)

  11. #11
    Join Date
    Mar 2003
    Posts
    343
    Thanks, I was looking at it from my perspective which is a VLDB - you're probably right about reasonably sized databases where such result sets can reside in memory.

Posting Permissions

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