SQL Server: NOT IN in where clause not working with NVARCHAR

Question

I have below table:

DECLARE @P AS TABLE
(
    ID int,
    PID  int,
    PNAME NVARCHAR(30),
    PARENT_PNAME NVARCHAR(30),
    Error int
)

INSERT INTO @P VALUES
(554,1,'AAAA',NULL,0),
(554,2,'BBBB',NULL,0),
(554,3,'CCCC',NULL,0),
(554,4,'DDDD','AAAA',0),
(554,5,'EEEE','AAAA',0),
(554,6,'FFFF',NULL,0),
(554,7,'GGGG',NULL,0),
(554,8,'HHHH',NULL,0),
(554,9,'IIII',NULL,0),
(554,10,'JJJJ',NULL,0),
(554,11,'KKKK',NULL,0);

If I run below query:

select ID, PID, PNAME, PARENT_PNAME,  Error
from   @P                  
where  PNAME not in (select PARENT_PNAME 
                     from   @P
                     where  PNAME is not null)
       AND PARENT_PNAME IS NOT NULL

I get nothing, no result. Why "NOT IN" is not working?

I need to obtain the two below rows:

(554,4,'DDDD','AAAA',0)
(554,5,'EEEE','AAAA',0)

On a side note: with your example data, the condition `where PNAME is not null` has no effect because all PNAME are not null. — Peter B, Jan 31 '17 at 11:37
Possible duplicate of [NOT IN clause and NULL values](http://stackoverflow.com/questions/129077/not-in-clause-and-null-values) — Peter B, Jan 31 '17 at 11:39
Chanukya's answer is correct (and he was the first to write the correct solution, so +1 for that), He doesn't explain why the `NOT IN` condition returns no results. The reason is that some values of `PARENT_PNAME` are null, so they can't be used in a `NOT IN` operator. that will always return false. — Zohar Peled, Jan 31 '17 at 11:41
I'm not quite sure what the criteria you are wanting to apply is - there are different criteria for returning the tow rows you mention. — Cato, Jan 31 '17 at 12:02

score 4 · Answer 1 · answered Jan 31 '17 at 11:32

DECLARE @P AS TABLE
(
    ID int,
    PID  int,
    PNAME NVARCHAR(30),
    PARENT_PNAME NVARCHAR(30),
    Error int
)

INSERT INTO @P VALUES
(554,1,'AAAA',NULL,0),
(554,2,'BBBB',NULL,0),
(554,3,'CCCC',NULL,0),
(554,4,'DDDD','AAAA',0),
(554,5,'EEEE','AAAA',0),
(554,6,'FFFF',NULL,0),
(554,7,'GGGG',NULL,0),
(554,8,'HHHH',NULL,0),
(554,9,'IIII',NULL,0),
(554,10,'JJJJ',NULL,0),
(554,11,'KKKK',NULL,0);

select * from @P where PARENT_PNAME is not null

output

ID  PID PNAME   PARENT_PNAME    Error
554 4   DDDD    AAAA    0
554 5   EEEE    AAAA    0

score 1 · Answer 2 · edited May 23 '17 at 12:16

You should also add and PARENT_PNAME is not null in your nested query.

SELECT ID, PID, PNAME, PARENT_PNAME, Error
FROM @P
WHERE PNAME NOT IN (
        SELECT PARENT_PNAME
        FROM @P
        WHERE PNAME IS NOT NULL
            AND PARENT_PNAME IS NOT NULL
        )
    AND PARENT_PNAME IS NOT NULL

That is the reason why you are not getting any results back.

For more detailed information have a look here.

score 1 · Answer 3 · answered Jan 31 '17 at 11:34

1

You don't need a NOT IN clause. Just try:

SELECT * FROM  @P where PARENT_PNAME is NOT NULL

answered Jan 31 '17 at 11:34

apomene

14,282
9
46
72

TheGameiswar · Answer 4 · 2017-01-31T11:44:30.283

0

select ID, PID, PNAME, PARENT_PNAME,  Error
from   @P                  
where  PNAME<>PARENT_PNAME
       AND PARENT_PNAME IS NOT NULL

Don't use NotIN for comparisons when you have null values,use NOT Exists

edited Jan 31 '17 at 11:44

answered Jan 31 '17 at 11:37

TheGameiswar

27,855
8
56
94

score 0 · Answer 5 · answered Dec 07 '18 at 09:40

There are number of methods to get the required result.

--method 1    
select ID, PID, PNAME, PARENT_PNAME,  Error
from   @P P1                 
where EXISTS (select PARENT_PNAME 
                 from   @P P2
                 where  p2.PNAME = p1.PNAME 
                        and P1.PARENT_PNAME is not null)

--query 2
select P1.* 
from @P  P1
where P1.PARENT_PNAME is not null

SQL Server: NOT IN in where clause not working with NVARCHAR

5 Answers5