T-SQL not equal to NULL

This is a well known problem addressed elsewhere, but I want to log it here anyway. When joining two tables and checking for records where a value in one does not equal a value in the other, T-SQL fails to find records where the reference table has recorded a NULL value. Presumably this is because a NULL value is unknown, so there is no way of knowing whether the two values are equal or not.

SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.FK = t2.PK
WHERE t1.Name <> t2.Name

If any name values in Table2 are NULL then the SELECT will not return these records. Instead, we must explicitly handle the NULL values.

SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.FK = t2.PK
WHERE t1.Name <> t2.Name
OR t2.Name IS NULL

See also the blog by Michael Freidgeim.