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

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

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