HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Not equal to operator is not returning NULL values in SQL Server

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
operatorserverequalnullsqlreturningvaluesnot

Problem

I have the following query where I'm looking for any rows that are not equal to 1 for the column istrue. However, the results only include records with 0 and omit those with null. While I am aware that using (istrue != 1 or istrue is null) would yield the expected outcome, I am curious about the underlying mechanism causing SQL Server to exclude null values. Could you please provide insights into this behavior? Thanks!

create table #test
(
id int,
val varchar(100),
istrue bit
)

insert into #test values 
(1, 'test-1', 1),
(2, 'test-2', 0),
(3, 'test-3', 1),
(4, 'test-4', 0),
(5, 'test-5', null),
(6, 'test-6', null)

select * from #test
where istrue != 1

drop table if exists #test

Solution

Consider checking documentation:

NULL indicates that the value is unknown. A null value is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a null value and any other value, return unknown because the value of each NULL is unknown.

Context

StackExchange Database Administrators Q#333948, answer score: 19

Revisions (0)

No revisions yet.