Replace Default Null Values Returned From Left Outer Join

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'

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'

MySQL

COALESCE(field, 'default')

For example:

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

Also, you can use multiple columns to check their NULL by COALESCE function. For example:

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

For Oracle you can use:

NVL(columnName,deafultValue) :- NVL is used to convert a null value to a default value in the query output. eg. If you want to replace null values with 'NA' then use something like this.

SELECT NVL(columnName,'NA') FROM tableName