RE: SQL Union Join?

I vote for the EXCEPTION JOIN, for performance and ease of maintenance
reasons.

First example is not doing a join but rather using an IN clause, which is
definitely not a good performer. That it even approaches performance of a
join is an indicator to me that tables you're using must be pretty small, or
else query optimizer is rewriting it internally as a join. You ought to see
that in the Visual Explain diagram.

I am not sure how exactly query optimizer would implement the third option,
but it can't be any better/simpler than the EXCEPTION JOIN, and is not as
easy to understand hence making it slightly more troublesome to maintain
long term.

Just my 2 cents.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: SQL Union Join?

Here's some results from Visual Explain for three techniques (that I think
are equivalent). Anyone, feel free to chime in and let me know if I'm wrong
(what!) thinking they're equivalent, and why they do what they do. I added
the distinct and am just returning SKU. Thanks!

SELECT Distinct A.SKU FROM TABLEA a WHERE A.Category not in (select
b.category from TABLEB b where a.sku = b.sku)
Total Estimated Runtime (MS) - 69.44

SELECT DISTINCT a.SKU FROM TABLEA a EXCEPTION JOIN TABLEB b ON a.SKU = b.SKU
And a.Category = b.category
Total Estimated Runtime (MS) - 56.51

Select b.SKU, b.Category from TABLEB b join TABLEA a using(SKU_number)
Except Select a.SKU, a.Category from TABLEA a join TABLEB b
using(SKU_number)
Total Estimated Runtime (MS) - 90.98

Thanks!