Re: SQL Union Join?

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.