Re: SQL Union Join?

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.