Joins on nullable columns, ANSI_NULLS 1

[foxdev]

I had a situation that drove another developer nuts for about an hour, and had me going for quite awhile, too. Its probably something most are aware of, but I thought I'd mention it just in case someone else runs into this situation.

Let's say we have two tables, Bigger and Smaller. We'd like to get a list of all IDs that are in Bigger but not in Smaller. ID is of type INT. We issue this command:

[tt]select * from Bigger where ID not in (select ID from Smaller)[/tt]

We get zero rows returned. We know this is wrong. There are definitely rows in Bigger that are not in Smaller (183 rows, actually).

One clue: Smaller.ID allows Nulls, and there are several (but not many) rows that have ID as Null.

Changing the query to either of the below returned the desired result:

A
[tt]select * from Bigger where ID not in (select ID from Smaller where ID is not NULL)[/tt]

B
[tt]set ansi_nulls off
select * from Bigger where ID not in (select ID from Smaller)[/tt]

In other words, either add SET ANSI_NULLS OFF or include WHERE ID IS NOT NULL.

Oh, sure, I know you're asking: why the heck have the ID column accept nulls? It appears to be a key, right? Well, yes, but this data is coming to us from an external customer (a rather large one at that), and their data is not the cleanest. The actual tables referred to in this example are in a staging database used for cleansing the data before going to its final resting place.


Robert Bradley

http://www.foxdev.com/

 

[harryrich]

I have the same situation on an imported file. Since this table gets queried via MS Access as well, I've been solving the problme mapping the null to a reserved value in the DTS import specifcation.