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?

Take the time to post DDL and DML statements to go with your queries so we can reproduce the issue and explain exactly what's happening here. — Thom A, Oct 12 '20 at 08:58
Also, What *are* yo expecting here? For all the queries to be the same? — Thom A, Oct 12 '20 at 09:00
With the limited information provided, all 3 queries return the same result for me. — Dale K, Oct 12 '20 at 09:09

score 0 · Accepted Answer · answered Oct 12 '20 at 09:10

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))

Of course. I didn't even consider null values at all :) – Kenneth Lauritsen Oct 12 '20 at 09:39 — Kenneth Lauritsen, Oct 12 '20 at 09:39

Query not returning expected data

1 Answers1