RE: SQL Union Join? -- MIDRANGE-L

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.