Wednesday, June 30, 2010

Caught flat-footed (again) by a NULL

It's common knowledge there are some curious (and occasionally head-scratching) properties of NULL in MSSQL. For instance, if a statement aggregates a nullable column, NULL values are excluded from the calculation. Given a table...
14432 Ash StreetApt. 12BrownsvilleTX
23417 12th AveNULLKalamazooMI
346961 117th ST NE NULLSt. PaulM
...the query "SELECT Count(ApartmentNumber) FROM Address" will yield a count of 1, rather than 3.

Also, comparing two NULL variables to each other without an ISNULL operator will result in false (even though they contain identical values).

DECLARE @Var1 int
DECLARE @Var2 int
SELECT @Var1 = Column1 FROM Table WHERE (no match condition)
SELECT @Var2 = Column1 FROM OtherTable WHERE (no match condition)
IF @Var1 = @Var2
SELECT 'True' --ain't never gonna happen
SELECT 'False'
And, oh yes, if
SELECT @Var = Col1 FROM Table WHERE (no matchcondition)
doesn't match a record (which, I suppose by definition it won't...), you might expect @Var to be assigned a NULL value. In fact, however, @Var holds whatever value it held before the SELECT statement, just as if the statement had never occurred (insert music from the Twilight Zone).

Anyway, aside from these better-known NULL behaviors, I stumbled across another not-so-intuitive quirk yesterday.

Assuming Table2's ColumnOfInterest field is nullable, the statement
SELECT * FROM Table1 WHERE SomeColumn IN
(SELECT ColumnOfInterest FROM Table2)
will return zero results of there are any NULL values in the subselect result, even if there are matching values. The lesson here: always scope the subselect query in situations like this to exclude that persnickety NULL.


  1. The trick here is NULL is not actually a value, it's a state that corresponds to "no value set". That's why a) you can't agregate a NULL because it's not a value (can't count something that doesn't exist), and b) you can't compare NULL to NULL - there's no value to compare.

    Your select's with no constraint don't cause @Var to be set because no actual recordset is returned. In order to set @Var to NULL, a row would have to be returned from the server containing NULLs. To that end, no assignment takes place in the former case; hence the variable is never set.

    The rule of thumb is NULL and anything is NULL. No matter what that is. I will grant you, I don't get the behaviour in the last one. :)

    Enjoyed your queueing article.

  2. Geoff - thanks very much for your thoughts. I agree entirely about the reason why the behavior exists. I guess my main observation was that once in a while the counter-intuitiveness of it all can sneak up on you, no matter how long you've been working with these values! Cheers :)