CODEWITHSUNDEEP
sqlsql-serversql-server-2008
DooDoo
DooDoo

Reputation: 13479

behavior of <> to filter null values

Hi experts: I have a table Called tblAlarm and it has some records like this:

enter image description here

I have another table for determine what user see what message:

enter image description here

Now I want to write a query to show Messages that user has not seen if message didinot expired.(for example it's year between BeginYear and EndYear and so on ...). I write this query:

SELECT  *
FROM
tblAlarms LEFT OUTER JOIN tblUsersAlarms tua ON tblAlarms.Id=tua.MessageID
WHERE @CurrentYear BETWEEN  tblAlarms.BeginYear AND tblAlarms.EndYear 
  AND @CurrentMonth BETWEEN  tblAlarms.BeginMonth AND tblAlarms.EndMonth 
  AND @CurrentDay BETWEEN tblAlarms.BeginDay AND tblAlarms.EndDay
  AND @CurrentHour * 60 + @CurrentMinute BETWEEN tblAlarms.BeginHour*60 + tblAlarms.BeginMinute AND tblAlarms.EndHour*60 + tblAlarms.EndMinute
  --AND (tua.UserID <> 128  AND tua.UserID IS NULL)

and it returns :

enter image description here

but if I unComment last line it does not return any record.How I can determine what messages that users has not been seen?

thanks

Upvotes: 1

Views: 642

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 754820

The result of comparing a NULL with a value is UNKNOWN. A filter condition only selects rows where the conditions evaluate to TRUE, so rows where the condition evaluates to FALSE or UNKNOWN are not selected.

The IS NULL and IS NOT NULL tests are different and always produce a TRUE or FALSE answer. You also cannot write column = NULL or column != NULL (at least, the SQL standard does not allow that; some SQL dialects may).

Your condition is:

AND (tua.UserID <> 128 AND tua.UserID IS NULL)

  • If the value of tua.UserID is NULL, then the second term evaluates to TRUE, but the first will then evaluate to UNKNOWN, and UNKNOWN and TRUE is UNKNOWN, which is not TRUE, so the row is not selected.

  • If the value of tua.UserID is not NULL, then the second term evaluates to FALSE, so the overall condition evaluates to FALSE, and so the row is not selected.

Hence, when you add the condition, nothing is selected, as you observed.

More typically, your condition would be either of these two:

AND (tua.UserID <> 128 AND tua.UserID IS NOT NULL)

AND (tua.UserID <> 128 OR tua.UserID IS NULL)

The double condition in the first alternative is in fact redundant. If tua.UserID is NULL, the first term will evaluate to UNKNOWN, so the overall condition would not be TRUE and the row would not be selected. The second alternative is quite useful.

Upvotes: 6

Hubert Sch&#246;lnast
Hubert Sch&#246;lnast

Reputation: 8527

"NULL" means: "I don't know the value". It does not mean: "There can't be any value". So, if you execute a comparison with a NULL-Value as one of its arguments, the result could be TRUE or FALSE, depending on the real (but unknown) value. But UNKNOWN is not a valid result of a comparison, so in this case the output is FALSE.

When you put a comparision-operator like =, !=, <, <=, >, >=, <> between two values (fields or literals), and one of this values is NULL then the result of that compare-operation is always FALSE.

There are two special comparison-operators for NULL-Values, that you already used: IS NULL and IS NOT NULL. Both aren't binary operators, but unary operators. That means, they don't have two arguments, but only one. They test if their argument is a NULL-Value or not.

So, if tua.UserID is NULL, tua.UserID <> 128 must be FALSE. The other part tua.UserID IS NULL is TRUE, but FALSE AND TRUE is FALSE, and this makes the complete WHERE-Condition FALSE for all rows.

Upvotes: 0

Related Questions