Why my NOT IN statment not working ?

Question

Hi I have following statement ran and no result come out

select * from producttosite
where
SKU
NOT IN
(select SKU from product0209)

the SKU in producttosite table should not in product0209 table, I checked manually.

the SKU field is nvarchar(255)

What I missing ? please help

Thanks

Answer 1

Hi @li dcik ,

Welcome to the Microsoft TSQL Q&A Forum!

Your statement is correct.

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)? So that we’ll get a right direction and make some test.

Regards
Echo

Answer 2

Hi @li dcik ,

Yes, I agree with @EchoLiu-MSFT . Your statement looks correct. You can try to use NOT EXISTS instead of NOT IN and see if it's give you the expected results. Thanks!

SELECT * FROM producttosite AS pts  
WHERE NOT EXISTS   
(SELECT * FROM product0209 AS p WHERE p.SKU = pts.SKU)  

Answer 3

What I missing ? please help

Pretty easy, the sub query returns at least one NULL value, and compares on NULL returns an undefined result = empty record set

A simple test:

-- This works
select *
from sys.objects
where not object_id in 
     (select 3 as id)

-- Returns empty result
select *
from sys.objects
where not object_id in 
     (select 3 as id
      union all
      select null)

So change your query to

select * from producttosite
 where
 SKU
 NOT IN
 (select SKU from product0209 where not SKU  IS NULL)

Answer 4

Use alias names to exclude column name scope mismatch due to e.g. typos.

SELECT O.*
FROM   producttosite O
WHERE  O.SKU NOT IN (   SELECT I.SKU
                        FROM   product0209 I );

Cause this can happen:

DECLARE @O TABLE (
    SKU INT
);

DECLARE @I TABLE (
    SKU2 INT
);

INSERT INTO @I ( SKU2 )
VALUES ( 0 ) ,
       ( 1 );

INSERT INTO @O ( SKU )
VALUES ( 1 ) ,
       ( 2 );

SELECT *
FROM   @O
WHERE  SKU NOT IN (   SELECT SKU
                      FROM   @I );