How to join two tables and set the default value to zero if it is not

Question

I have two tables with values ​​related to various elements 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 

If there is no element in one table, it is equal to zero by default and in any case adds a column. Is this possible in SQL Server?

Answer 1

You can perform 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 

A full outer join allows you to retrieve 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