Re: SQL Union Join?

[Michael Ryan <michaelrtr@xxxxxxxxx>]

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!

On Thu, Mar 26, 2009 at 12:14 PM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>wrote:

> Hi,
>
> UNION ALL performs normally better than UNION which is the same as UNION
> DISTINCT.
>
> UNION ALL will return all rows and does not remove duplicates.
> UNION (DISTINCT) removes duplicates. Therefore the result must be sorted
> and
> all rows must be compared which slows down performance.
>
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
>
>
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
> Michael_Schutte@xxxxxxxxxxxx
> Gesendet: Thursday, 26. March 2009 17:00
> An: Midrange Systems Technical Discussion
> Betreff: RE: SQL Union Join?
>
>
> Not to start a big discussion or anything, but why do a union all? Is there
> a better performance? I don't know the answer just wondering.   you already
> know that table b isn't in table a so isn't like there's any duplicate
> records are going to exist.
>
>
> Michael Schutte
> Admin Professional
>
>
>
> We've wrapped up a whole new breakfast adventure in Bob Evans BOBurrito.
> Premiering at 5.99. For more information and to enter the FREE Breakfast
> Every Week for a Year Sweepstakes ($500 value), visit www.BOBurrito.com
>
>
> midrange-l-bounces@xxxxxxxxxxxx wrote on 03/26/2009 11:46:17 AM:
>
> > Hmmmm... initially it sounded to me like he wanted something like FULL
> > EXCEPTION JOIN, which I don't think exists even on 6.1.
> > But then I looked at the examples that worked for him so far and he
> really
> > just wants a LEFT EXCEPTION JOIN with TableB as a primary dial
> > (Michael Schutte's last example).
> >
> > Michael Schutte's 1st example showed how to simulate a FULL EXCEPTION
> JOIN
> > (except I would have used UNION ALL).
> >
> > If he really wanted to look at all columns in the tables, another
> example,
> > leveraging the EXCEPT keyword would be:
> >
> > SELECT * FROM TABLEA
> > EXCEPT
> > SELECT * FROM TABLEB
> > UNION ALL
> > SELECT * FROM TABLEB
> > EXCEPT
> > SELECT * FROM TABLEA
> >
> > In any case, since he really needs a left exception join and is now
> asking
> > about performance, he'll just need to make sure join columns are
> > indexed properly.
> >
> > Elvis
> >
> >
> > Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
> > OS/400 www.centerfieldtechnology.com
> >
> >
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
> > Sent: Thursday, March 26, 2009 10:16 AM
> > To: Midrange Systems Technical Discussion
> > Subject: Re: SQL Union Join?
> >
> > Michael,
> >
> > Basically, for detecting mismatches across two files what you want is
> > called a FULL OUTER JOIN.
> >
> > Unfortunately, the i doesn't support FULL OUTER JOINs until 6.1.
> >
> > However, you can get the same results as a FULL OUTER JOIN by using a
> > UNION of the results from LEFT OUTER and RIGHT EXCEPTION.
>
> http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafysimula
>
> > tefoj.htm
> >  (note IBM example in manual uses flips the table order and uses
> > EXCEPTION join, it's kinda confusing :)
> >
> > select * from tableA left outer join tableB using (SKU,name, Category)
> > UNION ALL select * from tableA right exception join tableB using
> > (SKU,name,
> Category)
> > This would give you all rows from both tables:
> > SKU   NAME        CATEGORY  SKU   NAME        CATEGORY
> > 123   NAME1         1,001      -  -                  -
> > 456   NAME2         1,002   456   NAME2         1,002
> > 123   NAME1         1,002   123   NAME1         1,002
> > 789   NAME3         1,004      -  -                  -
> >    -  -                  -    1   SOMETHING     1,002
> >    -  -                  -  123   NAME1         1,003
> >
> > Since you only want mismatching, add a where:
> > select * from tableA A left outer join tableB B using (SKU,name,
> Category)
> > where b.sku is null
> > UNION ALL
> > select * from tableA A right exception join tableB B using (SKU,name,
> > Category)
> > where a.sku is null
> >
> > which gives you:
> > SKU   NAME        CATEGORY  SKU   NAME        CATEGORY
> > 123   NAME1         1,001      -  -                  -
> > 789   NAME3         1,004      -  -                  -
> >    -  -                  -    1   SOMETHING     1,002
> >    -  -                  -  123   NAME1         1,003
> >
> >
> > But it seems as if you don't want mismatches from both files, instead
> > what you want are only records from tableB that have a mismatched
> > category compared to tableA.
> >
> > select * from tableB B
> > where exists (select 1 from tableA A
> >               where b.sku = a.sku
> >                 and b.name = a.name
> >                 and b.category <> a.category)  and not exists (select
> > 1 from tableA A
> >                  where b.sku = a.sku
> >                    and b.name = a.name
> >                    and b.category = a.category)
> >
> > HTH,
> > Charles
> >
> >
> >
> > On Thu, Mar 26, 2009 at 9:30 AM, Michael Ryan <michaelrtr@xxxxxxxxx>
> wrote:
> > > If I have two tables (TABLEA, TABLEB) that have the same format, and
> > > I
> > want
> > > to retrieve all the rows where a combination of two columns don't
> match,
> > > would that be a union join? If so, how is that specified? Here's
> > > what I
> > > have:
> > >
> > > TABLEA and TABLE have the format of SKU, name and category. A SKU
> > > could exist in multiple categories.
> > >
> > > TABLEA
> > > 123, NAME1, 1001
> > > 456, NAME2, 1002
> > > 123, NAME1, 1002
> > > 789, NAME3, 1004
> > >
> > > TABLEB
> > > 001, SOMETHING, 1002
> > > 123, NAME1, 1003
> > > 456, NAME2, 1002
> > > 123, NAME1, 1002
> > >
> > > I want to retrieve 123, NAME1, 1003 because that's the one that has
> > > a different category. I don't want to retrieve SKU 456, 789, or 001.
> > > Hmmm...maybe I need to match on SKU with a subselect for that SKU
> > > not
> > having
> > > that category?
> > >
> > > Thanks!
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > > mailing
> > list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> > > unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
> > > take a moment to review the archives at
> > > http://archive.midrange.com/midrange-l.
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> > unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
> > a moment to review the archives at
> > http://archive.midrange.com/midrange-l.
> >
> >
> >
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> > unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
> > a moment to review the archives at
> > http://archive.midrange.com/midrange-l.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.