MySQL 替换从左外连接返回的默认空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1664266/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Replace Default Null Values Returned From Left Outer Join
提问by Brett Bim
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.
我有一个 Microsoft SQL Server 2008 查询，它使用左外连接从三个表中返回数据。很多时候，第二个和第三个表中没有数据，所以我得到一个空值，我认为这是左外连接的默认值。有没有办法替换 select 语句中的默认值？我有一个解决方法，我可以选择一个表变量，但感觉有点脏。
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.
如果可能，我希望 Quantity 和 RegularPrice 默认为零。
回答by Michael Haren
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'
回答by Zendem
In case of MySQLor SQLitethe correct keyword is IFNULL(not ISNULL).
如果MySQL或SQLite正确的关键字是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'
回答by Amir Fo
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 NULLby COALESCEfunction.
For example:
此外，您可以使用多个列NULL通过COALESCE函数检查它们。例如：
mysql> SELECT COALESCE(NULL, 1, NULL);
-> 1
mysql> SELECT COALESCE(0, 1, NULL);
-> 0
mysql> SELECT COALESCE(NULL, NULL, NULL);
-> NULL
