T-SQL CASE 子句:关于使用外连接指定 WHEN NULL 的问题 [英] T-SQL CASE Clause: Issue on specifying WHEN NULL with an Outer JOIN

问题描述

以下查询正确显示所有当前/潜在客户的记录，无论他/她是否下订单.但是下面的 SELECT 语句的 YesNO 别名总是返回 1，即使在某些情况下 o.OrderID 为空(即，当透视客户还没有下订单).为什么?我认为下面的 CASE 语句是正确的(如 this 响应中所示).我正在使用 SQL Server 2012.注意:请记住，虽然 OrderID 是一个 PK，但如果不满足连接条件，它与 OUTER JOIN 中的订单表的所有其他列将始终为空.

Following query correctly displays all current/prospective customers' record regardless of whether an order was placed by him/her or not. But the YesNO alias of the SELECT statement below is always returning 1 even when in some cases o.OrderID is null (i.e., when a perspective customer has not placed an order yet). Why? I think the CASE statement below is correct (as shown in this response as well). I'm using SQL Server 2012. NOTE: Please keep in mind that although the OrderID is a PK it will always be null along with all other columns of orders table in an OUTER JOIN if the join condition is not met.

SELECT c.customerID, o.OrderID, CASE When o.OrderID is NULL Then 0 Else 1 End as YesNO
FROM Customers c
LEFT JOIN Orders o
 ON c.customerID = o.customerID

推荐答案

尝试使用 ISNULL

Try using ISNULL

我很确定原因是因为当您将 IS NULL 与 NULL 进行比较时，结果为 NULL，因此为 False，因此它对您的 ELSE 1 进行评估

I am pretty sure that the reason is because when you compare IS NULL to NULL the result is NULL, thus False, hence why it is evaluating to your ELSE 1

SELECT c.customerID, o.OrderID, CASE When ISNULL(o.OrderID, 0) = 0 Then 0 Else 
1 End as YesNO
FROM Customers c
LEFT JOIN Orders o
 ON c.customerID = o.customerID

这篇关于T-SQL CASE 子句:关于使用外连接指定 WHEN NULL 的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！