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
