Re: SQL Union Join? -- MIDRANGE-L

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.