How To Join Two Tables And Replace Null In Second Table With Zero

When I try to join table A with B, I get only two rows - I do not get data for '55A' in ProdID column as it is missing in table B. How do I ensure that I get three rows with zero

Solution 1:

First, you should follow a simple rule: never use commas in the join clause. Explicit join syntax is much more powerful. And, it has been around for a long, long time.

The join you want is a left join. It keeps all rows in the first table, regardless of whether or not there are matches in the second table. The columns in the second table are given NULL values when there is no match. To convert the NULL to a 0, you can use the ANSI standard function coalesce():

select A.orderNo, coalesce(B.Price, 0) as Price
from A left join
     B
     on A.ProdID = B.ProdID;

Solution 2:

just use LEFT/RIGHT JOIN

after that you can use the COALESCE function like this COALESCE(B.Price,0) Price

Solution 3:

You can join these tables(tableA,tableB):

SELECT A.orderNo, B.Price
FROM tableA A
LEFTJOIN tableB B
ON A.ProdID = B.ProdID
WHERE1