Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Unanswered: Exception Join vs Not Exists

    Hi everyone, i would like to ask what is the difference between Exception Join vs Not Exists. What are the advantages and disadvantages. Thanks in advance

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What do you mean by "Exception Join"?

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by juliusquitain View Post
    Hi everyone, i would like to ask what is the difference between Exception Join vs Not Exists. What are the advantages and disadvantages.
    Actually, there are syntactically three different ways to write "give me all rows satisfying condition A, except for those satisfying condition B":
    Code:
    SELECT ... FROM tbl_1
    WHERE  condition_A
      AND NOT EXISTS (correlated subquery on tbl_2
                                WHERE condition_B)
    Code:
    SELECT ... FROM tbl_1
    WHERE  condition_A
    EXCEPT
    SELECT ... FROM tbl_2
    WHERE  condition_B
    Code:
    SELECT ... FROM tbl_1 LEFT OUTER JOIN tbl_2 ON condition_B
    WHERE condition_A AND tbl_2.pk IS NULL
    Sorry for the pseudo-code; a "real" example would help here, of course, to see how you may rewrite from one form into the other (or in which cases this would be impossible).
    Which of the three is "best"?
    Well, of course, "it depends"!
    First of all, not all three formulations make sense for a particular real-world question; and more precisely, one of the three is often the "most natural" and hence the most readable formulation.
    So go for that one! Readability of SQL means maintainability, which means: lower maintenance cost in the longer run.

    What about runtime performance? In the ideal world, the DB2 optimizer will turn your three formulations into exactly the same "byte code". Of course, there will be some situations (hopefully less than 5% of the cases) where the most readable SQL turns into the least performant implementation. In those cases, it may be useful (or even mandatory) to rewrite the query into one of the other formulations. Use EXPLAIN to find out! And always keep in mind that this should be the exception, not the rule!
    The DB2 optimizer is often better than you expect
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Tags for this Thread

Posting Permissions

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