How to join two tables and make default value zero if one doesn't have it

Question

I have two tables with values pertaining to different items like this:

Table1:

ItemID  |  val1  |  val2  |  val3  |
ABC        5        1        2.5
DEF        5        5        3.8
GHI        2        1        4.9
MNO        8        2        1.1
PQR        1        8        2.4

Table 2:

ItemID  |  val4  |  val5  
ABC        hi       4       
DEF        dude     9
GHI        word3    0
JKL        balls    1
MNO        day      5

I would like to join the tables so that they are like this:

ItemID  |  val1  |  val2  |  val3  |  val4  |  val5
ABC        5        1        2.5      hi       4
DEF        5        5        3.8      dude     9
GHI        2        1        4.9      word3    0
JKL        0        0        0        balls    1
MNO        8        2        1.1      day      5
PQR        1        8        2.4      0        0

Where if one table doesn't have the item, it just defaults to zero and adds the column anyway. Is this possible in SQL Server?

Answer 1

You can do a full outer join, using COALESCE:

SELECT COALESCE(TABLE1.ITEMID, TABLE2.ITEMID), COALESCE(VAL1, 0), COALESCE(VAL2, 0), 
    COALESCE(VAL3, 0), COALESCE(VAL4, 0), COALESCE(VAL5, 0)
FROM TABLE1 FULL OUTER JOIN TABLE2 
    ON TABLE1.ITEMID = TABLE2.ITEMID

The full outer join allows you to get data from both tables, even if there is no data in the first table.

Answer 2

SELECT ISNULL(t1.val1,0), ISNULL(t1.val2,0), ISNULL(t1.val3,0), ISNULL(t2.val4,0), ISNULL(t2.val5,0)  
FROM table1 t1  
FULL OUTER JOIN tale2 t2 ON t1.itemid= t2.itemid

Answer 3

select a.itemid, isnull(a.val1, 0), isnull(b.val5, 0)
from a 
left join b on a.itemid = b.itemid