Re: SQL Union Join?

Ah good catch.

You are correct. Once I added the where clause an EXCEPTION JOIN would
have been a better choice.

Also correct that adding the where clause was redundant.

I was concentrating to much on the path from FULL OUTER to what the OP
wanted ;-)

Charles


On Thu, Mar 26, 2009 at 1:15 PM, Adam Glauser <adamglauser@xxxxxxxxx> wrote:

Charles Wilt wrote:

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

Good examples.  I want to make sure I understand this correctly. In your
second example, is there a reason you use a 'left outer join' with
'where b.sku is null' instead of 'left exception join'?  I think they
should both end up with the same set.

Secondly, I think that 'where a.sku is null' is redundant in the second
part of the second example.
--
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.