Attribute Query "Is not equal to" doesn't recognize Null

[Jon-PaulMcCool]

When doing an attribute query and using the Is Not Equal To operator, the result does not include Null values. For example, I have a series of parcels with a given identifier, and when trying to select those without the identifier, I was getting no result since all those without the identifier were Null. I know very well that I can just select the Null options, but if a user wanted all values other than X and needed that to include Null, and they did not know that the operator did not work with Nulls, that could be an issue. Just seems that Nulls should be included in the operator's ability to discriminate.

[KoryKramer]

Hi @Jon-PaulMcCool and @CynthiaGeskes 

What version of ArcGIS Pro are you using where you are seeing this behavior?

I just tried on a dev version and when I query Fire_Name is not equal to ZWINGE, I get all records except ZWINGE selected, including blanks and Nulls.

What you're seeing would be considered a bug rather than an idea, but we'll need to know your Pro version and it may be necessary to get the data and steps to reproduce in order to investigate.

[Jon-PaulMcCool]

ArcGIS Pro 3.0 Patch 2 (3.0.2). It is my employer's computer and I don't
have the ability to update the software independently, so I'm not sure if
this would be an issue in the latest version.

JP

[KoryKramer]

Can you share the query you're using, both a screenshot of the query in the builder like I showed above as well as the resulting SQL?; e.g.

[RandyCasey]

@Jon-PaulMcCool I feel this, and I may be wrong here, but I believe this is a SQL issue, and maybe not an Esri issue. When writing a SQL query, when I use the IN/NOT IN or LIKE/NOT LIKE operators, if I want to include NULL values I also have to include OR IS NULL in my expression. This is something I have had to learn the hard way after a few failed SQL queries; that NULL values always have to be handled separately.

[JoshuaBixby]

@Jon-PaulMcCool, @RandyCasey is absolutely correct.  The use of NULL introduces 3-value logic into SQL comparison operators.  Since NULL cannot be equal or unequal to any other value, including NULL itself, one cannot select NULL records based on equality comparisons. 

[KoryKramer]

Thanks for the input @RandyCasey and @JoshuaBixby !

I may have misunderstood the original description, but my understanding was that @Jon-PaulMcCool has a query such as FIRE_NAME <> 'ZWINGE' and null values are not being selected.

I do not see that behavior so we'll need some clarifying details around the problem being reported.

Thanks, everyone.

[JoshuaBixby]

@KoryKramer, with standard SQL the following expression does not select NULL:

FIRE_NAME <> 'ZWINGE'

I just fired up Pro 3.0.2 and tested Select By Attributes on a feature class in a file geodatabase, and results were as expected, which is not to select NULL.

[KoryKramer]

You're correct @JoshuaBixby ! I think I got tripped up initially by user error - I would have had that column highlighted, so when I took a quick look, it appeared that Null values were selected. They were not. Thank you for the reality check!

And as you and Randy have both pointed out, this is all working as expected since = and <> don't work with NULL values. 

@Jon-PaulMcCool see https://pro.arcgis.com/en/pro-app/latest/help/mapping/navigation/sql-reference-for-elements-used-in-... 

I'm not sure where that leaves us with this idea - I can double-check with our development team, but we'll likely need to Close it as a known limit since I don't think we should modify standard SQL behavior when is comes to querying data.

[KoryKramer]

Closing this idea - as pointed out in the discussion, Equal to and Not equal to do not recognize Null values. 

Also as pointed out in the comments, if such a query is needed it will need to explicitly include a clause to check for null values, such as fieldA != 'some value' or fieldA is null.

[MarcelSt-Germain]

@KoryKramer Not straitfoward leaving place to problem since source may have no null value when making the query but be there after.  Do me a lot of problem.