CODEWITHSUNDEEP
sqlsql-serversql-server-2008null
YamHon.CHAN
YamHon.CHAN

Reputation: 886

Confused in SQL query involving "Null" return no row selected

I'm confused by the result of the two SQL queries. Give the table content and two queries:

Col1 Status
---- ------ 
abcd "null"
abcd PENDING
defg "null"
defg PENDING

a) SELECT Col1, STATUS FROM TABLE_A WHERE KEY IN ('abcd','defg');

b) SELECT Col1, STATUS FROM TABLE_A WHERE KEY IN ('abcd','defg') and NOT (STATUS = 'PENDING')

result of (a) shows all 4 rows, and result of (b) shows nothing!

Column 'Status' can have values other than 'PENDING', and for reporting reason, I cannot use 'STATUS IS NOT NULL' as filter. I CANNOT understand why query (b) return nothing. Thanks in advance for any help

Platform: MSSQL 2008

Upvotes: 0

Views: 329

Answers (1)

Martin Smith
Martin Smith

Reputation: 453028

SQL uses three valued logic (True, False, Unknown). The WHERE clause needs to evaluate to True for a row to be returned.

STATUS = 'PENDING' returns UNKNOWN when Status IS NULL.

If you negate Unknown you still get Unknown.

See SQL and the Snare of Three-Valued Logic

You would need to rewrite the query as 

SELECT Col1, STATUS 
FROM TABLE_A 
WHERE KEY IN ('abcd','defg') and (STATUS IS NULL OR  STATUS  <> 'PENDING')

(Though it is possible to use the deprecated ANSI_NULLS session option to get the behaviour you want)

Upvotes: 2

Related Questions