Friday, September 15, 2017

SQL Server Not operator with nullable values


Null values are always tricky and in last couple of days I spent quite some time wrapping my head around one such case. Here’s knowledge bite from that effort which might help you to devise better queries in your projects.

QUERY 1
declare @num1 int = 5
if (@num1 = 1)
       select 1
else
       select 0

Output of above query is 0 as expected.


QUERY 2
declare @num1 int = 5
if NOT (@num1 = 1)
       select 1
else
       select 0

Output of above query is 1 as expected.

QUERY 3
declare @num1 int
if (@num1 = 1)
       select 1
else
       select 0

Output of above query is 0 as expected.

QUERY 4
declare @num1 int
if (@num1 = 1 OR 4 < 5)
       select 1
else
       select 0

Output of above query is 1 as expected.

QUERY 5
declare @num1 int
if NOT (@num1 = 1 OR 4 < 5)
       select 1
else
       select 0

Output of above query is 0 as expected.

QUERY 6
declare @num1 int
if (@num1 = 1 OR 4 > 5)
       select 1
else
       select 0

Output of above query is 0 as expected.

QUERY 7
declare @num1 int
if NOT (@num1 = 1 OR 4 > 5)
       select 1
else
       select 0

Output of above query is 0 UNEXPECTED (at-least I was very surprised).

Conclusion:
In SQL Server comparison operations involving null values result in unknown which is treated as falsy. Such falsy (unknown) values when passed to Boolean operator result in false. This problem gets aggravated for some of us because we work on javascript a lot and there falsy is false.

Handle null values carefully and remember below table:
true || true                                        => true
true || unknown                              => true
false || unknown                             => false

NOT (true || unknown)                  => false
NOT (false || unknown)                => false

Note: all queries above executed on SQL Server 2014

Happy and Safe Coding ;) 

Thanks,

Ravi Gupta

No comments:

Post a Comment