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?
I suspect the issue with the first query is a null-safety problem. If there are null values in
Person(PSCpr)
, then thenot in
condition filters out allAnmodning
rows, regardless of other values inPerson
.Consider this simple example:
Returns no rows, while:
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 isnull
-safe, which I would recommend here: