patternsqlModerate
Not equal to operator is not returning NULL values in SQL Server
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 #testSolution
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.
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.