...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
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)