NULLS Matching?

Question

NULLS Matching?

SteveOC

Ten Centuries

Points: 1257
More actions
December 6, 2020 at 10:39 am

#3817483
Hello.
I am guessing that is simple - or I am just missing something but I am trying to understand why the same piece of code that I run against 2 tables in 2 DBs in the same cluster works as expected in one and not the in the other.
My data contains NULLS in both cases - and I have a JOIN (to the same table) which I understand Defaults to Inner Join (which is what I want) and one of the clauses for the JOIN is an '=' on the column containing NULLs.
Since I understand that NULLs cannot be matched - even to other NULLs - the code works as I expect on one table and excludes the millions of rows where the column has a NULL value. However on the other table the code returns the NULL values unless I add a "> 0" or "IS NOT NULL" in a WHERE Clause.
I do not get this - I checked the collations of the DBs, tables, Indexes, and even tried setting the DBLEVELs to the same in Test (one is 2012 and the other is 2014) but it made no difference. ANSI NULLs settings seem to be the same, the column is NVARCHAR in both and I don't know what else to look for.
I am confused in that I thought that it was a maxim of SQL Server that NULLS could not be matched, even to other NULLS so please advise me where I am going wrong with my understanding.
The original code snippet came from the internet, and I am sure that somebody will tell me it is not the most performant method - although I have tried a number and it works best for me so far, but grateful for any and all input.
It looks very simple - alternatecode should not be duplicated within an account but NULLs are allowed and most are NULL, and by including in the join it filters out the entries with NULLs in one of the 2 DBs and just returns the list of duplicates I am after (8K from 125M records).
As I said, I have a workaround to return the correct result set from the other DB - add a Where alternatecode > 0 or IS NOT NULL I just want to understand why the behaviour is different?
TIA.
```
select s.accountid,s.alternatecode
from [rsitem] s
join (
    select accountid, alternatecode, count(*) as qty
    from [rsitem]
    group by accountid, alternatecode
    having count(*) > 1
) t on s.accountid = t.accountid and s.alternatecode = t.alternatecode
```
- This topic was modified 3 years, 11 months ago by SteveOC.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16157 More actions · Answer 1

from what I can see the output of that query will filter out the cases where either accountid or alternatecode is null - why are you stating it is not ignoring them?

if you do the following do you get records with null values

select s.accountid,s.alternatecode
from [rsitem] s
join (
    select accountid, alternatecode, count(*) as qty
    from [rsitem]
    group by accountid, alternatecode
    having count(*) > 1
) t on s.accountid = t.accountid and s.alternatecode = t.alternatecode
where s.accountid is null
or s.alternatecode is null

the "inner" query can give nulls on its own but that would be correct on that particular query as there is no join or filtering.

SteveOC Ten Centuries Points: 1257 More actions · Answer 2

Thanks for the reply Frederico - which gave me a kick in the right direction....

Adding those predicates returned no results - looks like I have been confusing myself and went back to basics and did a manual search of my email archives (something that I try to avoid since migrating to O365......) to find this which I used about 3-4 years ago for something else......and finally found it.

select convert(varbinary(max),alternatecode) as HexAltCode

I had also tried using some vendor code at some point (which doesn't work)

Which revealed that whilst one DB does indeed have NULLS as expected - the other appears to have inserted spaces for the entire length of the field - I have changed the names for illustration.

Whilst I a have front-end apps for the former, I don't have the same for the latter.

I thought it might be something like this but could not find the code snippet and it just did not make sense.

Thanks again.

Steve O.