sql - How to join two tables and replace null in second table with zero

Question

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 for price for missing ProdID

Query used:
select A.orderNo, B.Price
from A,B
where A.ProdID=B.ProdIDl

Answer 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;