Default Null Values from the Left Outer Join should be replaced.

Problem

I have a Microsoft SQL Server 2008 query that uses a left outer join to return data from three tables. 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.

Asked by Brett Bim

Solution #1

It’s as simple as that.

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'

Answered by Michael Haren

Solution #2

The right keyword in MySQL and SQLite 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'

Answered by Zendem

Solution #3

COALESCE(field, 'default')

For example:

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

You can also use the COALESCE function to verify multiple columns for NULL. Consider the following scenario:

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

Answered by Amir Fo

Solution #4

You can use the following for Oracle:

NVL(columnName,deafultValue): In the query output, NVL is used to transform a null value to a default value. eg. Use something like this if you wish to replace null values with ‘NA’.

FROM tableName SELECT NVL(columnName,’NA’)

Answered by Partha Prateem Patra

Post is based on https://stackoverflow.com/questions/1664266/replace-default-null-values-returned-from-left-outer-join