title: SQL Server: NOT IN in where clause not working with NVARCHAR
body: <p>I have below table:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>If I run below query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I get nothing, no result. Why "NOT IN" is not working?</p>
  
  <p>I need to obtain the two below rows:</p>
  
  <pre><code>(554,4,'DDDD','AAAA',0)
  (554,5,'EEEE','AAAA',0)
  </code></pre>