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

Question

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?

Answer 1

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.

Answer 2

You can use not exists

select * 
from table(A) a
where not exists (selet 1 from tableB where orderno = a.orderno);

Answer 3

I have experienced the same. try joining the two tables tableA and TableB

select * from TableA a  
inner join TableB b on a.orderno =b.orderno

This should allow you to get the records and then you can delete the same.