CODEWITHSUNDEEP
sql-serversql-server-2000
P.Brian.Mackey
P.Brian.Mackey

Reputation: 44285

What is the difference between != and is not? And Nulls in general

I am having a bit of difficulty understanding how t-sql treats null values.

As a C# guy, I tend to want to do

IF(@myVar != null)...

But, this doesn't ever appear to run my code. So I do

IF(@myVar is not null)

Whats the difference?

Second, the way addition works is unclear. Let's say I have

declare @someCount int, @someFinalResult int

--Select that returns null
SELECT @someCount = columnName from tableName where someColumn = someValue

Then if I do

SET @someFinalResult = @someCount + 1--I seem to get NULL if I had null + something

But, if I first 

declare @someCount int, @someFinalResult int
--FIRST SET DEFAULT TO 0
SET @someCount = 0

--Select that returns null
SELECT @someCount = columnName from tableName where someColumn = someValue

Now, @someCount defaults to 0, it's not actually set equal to NULL even if the result is null. Why?

Upvotes: 1

Views: 97

Answers (2)

kristof
kristof

Reputation: 53834

When you deal with NULL in SQL Server you basically work with 3-value logic with all the implications.

So in your example

IF(@myVar != null) vs IF(@myVar is not null)

It basically boils down to the question what is the difference between: @myVar = null vs @myVar is null

@myVar = null will always evaluate to null as what you are asking is:

is the value in @myVar equal to UNKNOWN

As you do not know what the UNKNOWN is this question cannot by answered yes, or no so it evaluates to UNKNOWN

e.g. 
    "is 1 = UNKNOWN" - I do not know
    "is 'a' = UNKNOWN" - I do not know 
    "is UNKNOWN = UNKNOWN" - I do not know

The last one may be a bit tricky but just imagine that you have 2 boxes with apples and you do not know neither how many apples are in box1 one nor in box2 so asking: 

is count(box1) = count(box2)
is the same as 
is UNKNOWN = UNKNOWN"

so the answer is I do not know

the second one @myVar is null is different as it is like asking

is the value in @myVar UNKNOWN

so the difference is that you specifically ask "is it true that the value stored in the variable is UNKNOWN?", so

    "is 1 UNKNOWN" - NO
    "is 'a' UNKNOWN" - NO
    "is UNKNOWN UNKNOWN" - YES

Upvotes: 3

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143119

Generally, it's like this: NULL is unknown, so !=NULL is also unknown, because you don't know if it's equal or not. And you know even less whether two unknowns are equal. The same goes for more or less any operation with unknowns, when you add something to unknown the result is hardly any more known to you.

Upvotes: 1

Related Questions