T-SQL CASE Clause: Issue on specifying WHEN NULL with an Outer JOIN

Question

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

Are you sure there are cases where the o.OrderId is null? Or is that supposed to be c.OrderId? — DigiFriend
– DigiFriend, Commented May 2, 2017 at 17:05
@DigiFriend Yes.That's why I'm including o.OrderID in SELECT so I could verify. — nam
– nam, Commented May 2, 2017 at 17:08
are u execute it under northwind database? your query is correct. if yes then it shows 0 properly — Fahmi
– Fahmi, Commented May 2, 2017 at 17:09
For 'FISSA' customerid orderid is null and it shows 0 on that row — Fahmi
– Fahmi, Commented May 2, 2017 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
– SqlZim, Commented May 2, 2017 at 18:17

Jacob H · Accepted Answer · 2017-05-02 17:05:19Z

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

answered May 2, 2017 at 17:05

Jacob H

2,5341 gold badge14 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nam Over a year ago

Thanks for explaining a possible reason for the issue, as well.

SqlZim Over a year ago

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 ....

Collectives™ on Stack Overflow

T-SQL CASE Clause: Issue on specifying WHEN NULL with an Outer JOIN

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related