sql server - Why SQL comparison with <> doesn't return the row with NULL value -


this question has answer here:

assume table x so:

     |   b ----------------    2       pqr    3       xyz  *null*    abc 

when execute query like:

select *  x  <> 2 

i expect result set this:

     |   b ----------------    3       xyz  *null*    abc 

but surprise, result set :

     |   b ----------------    3      xyz 

why row null value not appear in result set?

can explain behavior ?

the ansi-92 sql standard states if 1 of operands null, result of comparison "unknown" - not true or false.

for @ how nulls work in sql, see 4 simple rules handling sql nulls

sql fiddle

ms sql server 2008 schema setup:

create table x     ([a] int, [b] varchar(3)) ;  insert x     ([a], [b]) values     (2, 'pqr'),     (3, 'xyz'),     (null, 'abc') ; 

query 1:

select *  x  null or <> 2 

results:

|      |   b | |--------|-----| |      3 | xyz | | (null) | abc | 

Comments

Popular posts from this blog

php - regexp cyrillic filename not matches -

c# - OpenXML hanging while writing elements -

sql - Select Query has unexpected multiple records (MS Access) -