Quote:
Originally Posted by juliusquitain
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
dBforums
>
Exception Join vs Not Exists
09-13-11, 06:58
Linear Mode
