...the query "SELECT Count(ApartmentNumber) FROM Address" will yield a count of 1, rather than 3.
AddressID StreetAddress ApartmentNumber City ST 1 4432 Ash Street Apt. 12 Brownsville TX 2 3417 12th Ave NULL Kalamazoo MI 3 46961 117th ST NE NULL St. Paul M
Also, comparing two NULL variables to each other without an ISNULL operator will result in false (even though they contain identical values).
DECLARE @Var1 intAnd, oh yes, if
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
ELSE
SELECT 'False'
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 INwill 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.
(SELECT ColumnOfInterest FROM Table2)
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.
ReplyDeleteYour 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.
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 :)
ReplyDelete