Replace null defaults returned on the left

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 zero, which, in my opinion, is the default value for an external outer join. Is there a way to replace the default values ​​in a select statement? I have a workaround in that I can select 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 default number and RegularPrice to be zero if possible.

Answer 1

It is as simple as

 IsNull(FieldName, 0) 

Or more fully:

 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 the case of MySQL or SQLite 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' 

Answer 3

MySQL

 COALESCE(field, 'default') 

For example:

  SELECT t.id, COALESCE(d.field, 'default') FROM table t LEFT JOIN detail d ON t.id = d.item 

In addition, you can use multiple columns to check for their NULL by the COALESCE function. For example:

 mysql> SELECT COALESCE(NULL, 1, NULL); -> 1 mysql> SELECT COALESCE(7, 1, NULL); -> 7 mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL