1

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
Community
  • 1
  • 1
nam
  • 21,967
  • 37
  • 158
  • 332
  • 1
    Are you sure there are cases where the `o.OrderId` is null? Or is that supposed to be `c.OrderId`? – DigiFriend May 02 '17 at 17:05
  • @DigiFriend Yes.That's why I'm including `o.OrderID` in `SELECT` so I could verify. – nam May 02 '17 at 17:08
  • are u execute it under northwind database? your query is correct. if yes then it shows 0 properly – Fahmi May 02 '17 at 17:09
  • For 'FISSA' customerid orderid is null and it shows 0 on that row – Fahmi May 02 '17 at 17:09
  • If you are getting `1`, then `o.OrderID` is not `null`. Are you considering some other value to be `null` that isn't an actual `null` value? – SqlZim May 02 '17 at 18:17

1 Answers1

0

Try using ISNULL

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
Jacob H
  • 2,455
  • 1
  • 12
  • 29
  • Thanks for explaining a possible reason for the issue, as well. – nam May 02 '17 at 17:15
  • When you check if a value `is null` it returns true or false, not `null`. When you try to check if a value is null by using `= null` *then* you would get `null`. Also, `null` does not mean false, but it does not mean true either and since it is not `true` then it would not return `0` per the `when ...`. – SqlZim May 02 '17 at 18:15