"IN operator" and Null values--a bug in MS SQL 2000

Just discovered a bug in MS SQL 2000 today. When performing a query with the IN operator, you'll need to make sure that the associated value list does not contain any null values. If it does, the expression will always return false. The fix for this is simple enough: simply add AND column IS NOT NULL to your SELECT sub-query.

For example, the following SQL query:

SELECT intelligence_rating
FROM aliens
WHERE home_world IN (
   SELECT name
   FROM worlds
   WHERE class = 'M'
   )

Would need to be changed to:

SELECT intelligence_rating
FROM aliens
WHERE home_world IN (
   SELECT name
   FROM worlds
   WHERE class = 'M'
      AND name IS NOT NULL
   )

If there were any worlds that did not have a value in the name column. Good thing we can safely assume that all worlds have names. For now... (queue scary music?)