RE: SQL Union Join?

[Michael_Schutte@xxxxxxxxxxxx]

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.