[SOLVED] SQL 0 results for 'Not In' and 'In' when row does exist

SQL 0 results for 'Not In' and 'In' when row does exist

I have a table (A) with a list of order numbers. It contains a single row.

Once this order has been processed it should be deleted. However, it is failing to be deleted.

I began investigating, a really simple query is performed for the deletion.

delete from table(A) where orderno not in (select distinct orderno from tableB)

The order number absolutely does not exist in tableB.

I changed the query in SSMS to :

select * from table(A) where orderno not in (select distinct orderno from tableB)

This returned 0 rows. Bare in mind the orderno does exist in tableA. I then changed the query from "not in" to "In". It still returned 0 rows. How can this be possible that a value is not in a list of values but also not show for the opposite?

Things I have tried:

Two additional developers to look over it.
ltrim(rtrim()) on both the select values.
Various char casts and casting the number as an int.

Has anyone experienced this?

Solution

Don't use NOT IN with a subquery. Use NOT EXISTS instead:

delete from tableA 
    where not exists (select 1 from tableB where tableA.orderno = tableB.orderno);

What is the difference? If any orderno in TableB is NULL, then NOT IN returns NULL. This is correct behavior based on how NULL is defined in SQL, but it is counterintuitive. NOT EXISTS does what you want.