Replace Default Null Values Returned From Left Outer Join
MysqlSqlSql ServerTsqlSql Server-2008Mysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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'
Solution 2 - Mysql
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'
Solution 3 - Mysql
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
Solution 4 - Mysql
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