Joining Two Tables, Same ID - Return both IDs even if Null

user_2125263 0
Viewed 79

I have two tables

+-----+----------+
| SKU | Quantity |
+-----+----------+
| ABC | 30       |
| DEF | 40       |
+-----+----------+

+-----+----------+
| SKU | OnOrder  |
+-----+----------+
| ABC | 90       |
| XYZ | 80       |
+-----+----------+

I want to join both tables on SKU when there is a match, but also return SKU from both tables when there isn't a match. So the results would be:

+-----+---------+---------+
| SKU | OnOrder | OnOrder |
+-----+---------+---------+
| ABC | 90      | 30      |
| DEF | 40      |         |
| XYZ |         | 80      |
+-----+---------+---------+

I'm thinking this would be accomplished by using a left join and a right join and by doing a union but I can't seem to get the code right.

Your help is much appreciated!

sql
2 Answers

You can use a full join:

select coalesce(t1.sku, t2.sku), t1.quantity, t2.onorder
from table1 t1 full join
     table2 t2
     on t1.sku = t2.sku;

If your database doesn't support full join, the best workaround is probably:

select t1.sku, t1.quantity, t2.onorder
from table1 t1 left join
     table2 t2
     on t1.sku = t2.sku
union all
select t2.sku, null, t2.onorder
from table2 
where not exists (select 1 from table1 t1 where t1.sku = t2.sku);
user_11440350

You can first select all the SKU values and then join that to both tables:

with skus(sku) as (
   select sku from table1
   union
   select sku from table2
)
select a.sku, b.quantity, c.onorder 
from skus a 
left join table1 b on a.sku = b.sku 
left join table2 c on a.sku = c.sku;
user_73267380
Related