Replace Default Null Values Returned From Left Outer Join

Question

I have a Microsoft SQL Server 2008 query that returns data from three tables using a left outer join. Many times, there is no data in the second and third tables and so I get a null which I think is the default for left outer join. Is there a way to replace the default values in the select statement? I have a workaround in that I can select into a table variable but it feels a little dirty.

SELECT iar.Description, iai.Quantity, iai.Quantity * rpl.RegularPrice as 'Retail',  iar.Compliance FROM InventoryAdjustmentReason iar LEFT OUTER JOIN InventoryAdjustmentItem iai  on (iar.Id = iai.InventoryAdjustmentReasonId) LEFT OUTER JOIN Item i on (i.Id = iai.ItemId) LEFT OUTER JOIN ReportPriceLookup rpl on (rpl.SkuNumber = i.SkuNo) WHERE iar.StoreUse = 'yes' 

I would like the Quantity and RegularPrice to default to zero if possible.

Answer 1

That's as easy as

IsNull(FieldName, 0) 

Or more completely:

SELECT iar.Description,    ISNULL(iai.Quantity,0) as Quantity,    ISNULL(iai.Quantity * rpl.RegularPrice,0) as 'Retail',    iar.Compliance  FROM InventoryAdjustmentReason iar LEFT OUTER JOIN InventoryAdjustmentItem iai  on (iar.Id = iai.InventoryAdjustmentReasonId) LEFT OUTER JOIN Item i on (i.Id = iai.ItemId) LEFT OUTER JOIN ReportPriceLookup rpl on (rpl.SkuNumber = i.SkuNo) WHERE iar.StoreUse = 'yes' 

Answer 2

In case of MySQL or SQLite the correct keyword is IFNULL (not ISNULL).

 SELECT iar.Description,        IFNULL(iai.Quantity,0) as Quantity,        IFNULL(iai.Quantity * rpl.RegularPrice,0) as 'Retail',        iar.Compliance      FROM InventoryAdjustmentReason iar     LEFT OUTER JOIN InventoryAdjustmentItem iai  on (iar.Id = iai.InventoryAdjustmentReasonId)     LEFT OUTER JOIN Item i on (i.Id = iai.ItemId)     LEFT OUTER JOIN ReportPriceLookup rpl on (rpl.SkuNumber = i.SkuNo) WHERE iar.StoreUse = 'yes'