Query not returning expected data

Question

I'm looking for an explanation for why 1 of the following 3 queries aren't returning what I am expecting.

-- Query 1
SELECT ANNo, ANCpr
FROM Anmodning
WHERE LEFT(ANCpr,6) + '-' + RIGHT(ANCpr,4) NOT IN (SELECT PSCpr FROM Person)

-- Query 2
SELECT ANNo, ANCpr
FROM Anmodning a
LEFT JOIN Person p ON p.PSCpr = LEFT(a.ANCpr,6) + '-' + RIGHT(a.ANCpr,4)
WHERE p.PSNo IS NULL

-- Query 3
SELECT ANNo, ANCpr
FROM Anmodning
WHERE ANNo NOT IN
(
  SELECT ANNo
  FROM Anmodning
  WHERE LEFT(ANCpr,6) + '-' + RIGHT(ANCpr,4) IN (SELECT PSCpr FROM Person)
)

Assume the following:
Anmodning with ANNo=1, ANCpr=1111112222
And the Person table doesn't have a row with PSCpr=111111-2222
Queries are executed in Management Studio against a SQL Server 2017.

Queries 2 and 3 returns the Anmodning row as expected but query 1 does not. Why is that?

Answer 1

I suspect the issue with the first query is a null-safety problem. If there are null values in Person(PSCpr), then the not in condition filters out all Anmodning rows, regardless of other values in Person.

Consider this simple example:

select 1 where 1 not in (select 2 union all select null)

Returns no rows, while:

select 1 where 1 not in (select 2 union all select 3)

Returns 1 as you would expect.

This problem does not happen when you use left join, as in the second query.

You could also phrase this with not exists, which is null-safe, which I would recommend here:

SELECT ANNo, ANCpr
FROM Anmodning a
WHERE NOT EXITS (SELECT 1 FROM Person p WHERE p.PSCpr = LEFT(a.ANCpr,6) + '-' + RIGHT(a.ANCpr,4))