Yesterday I was asked to check 2 tables for differences. First I tried it using the IN operaror:

        SELECT * FROM Table1 where not(Table1.ID IN (SELECT Table2.ID
                                                     FROM Table2))

I knew there were supposed to be several differences between the tables, weirdly enough using this query I didn't find any.

Afterwards I tried it using a LEFT JOIN:

        SELECT * FROM Table1 LEFT JOIN Table2
        ON Table1.ID = Table2.ID

I made a view of this query, and when I checked for cases where Table2.ID was NULL I suddenly found my differences.

Personally I'm baffled by this. Does anyone here have an explanation for this?

Logically, at least that was what I thought, my 1st query should have sufficed, but it didn't yield anything.

Please help. This may sound like a newbie question, but I really would like to know what I did wrong.

Do you have NULL as a value for the ID column in Table2?

The IN clause is shorthand for an equality test of a value with a list of values. If NULL is a value in the list, the equality test will result in unknown. Thus, it is not possible to know for sure that any given value isn't in the list because of that unknown result, so no rows are returned by NOT...IN.

Welcome to three valued logic...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com