Null values are excluded when using the definition query to filter specific values in ArcGIS Pro

Description

In ArcGIS Pro, applying a definition query to exclude specific values from a field removes records with null values. This issue results in the loss of data intended for analysis or visualization. For example, excluding records with the value 'Public' from the TYPE field, which contains the values 'Public', 'Private', and null, also removes records with null values from the attribute table, as shown in the image below.

Cause

This is expected behavior when filtering records with null values, which indicate the absence of data and cannot be defined by any specific value. Because null values are unknown, they cannot be selected using equality comparisons. Therefore, when creating a definition query to exclude certain values, records with null values are also filtered out, as they do not meet the query criteria.

Solution or Workaround

To address null values, use the IS NULL or IS NOT NULL operator in the definition query. Refer to ArcGIS Pro: Common expressions: Searching for NULL values for more information. In this example, to return records with null values in the query results, modify the query expression to include null values using the IS NULL operator.

1. Open the ArcGIS Pro project.
2. In the Contents pane, right-click the feature layer and select Properties.
3. In the Layer Properties dialog box, click the Definition Query tab.

Note:
Alternatively, on the ribbon, click the Data tab. In the Definition Query group, click Build Definition Query .

4. Under Definition Queries, in the existing query's cell, click Edit.

Note:
The query builder opens in Clause mode by default. To open it in SQL mode, toggle the SQL option .

• For the Clause mode:
  1. In the query builder, click Add Clause.
  2. Click the And drop-down menu and select Or.
  3. Click the Select a field drop-down menu and select the required field. In this example, TYPE is selected.
  4. Click the is equal to drop-down menu and select is null.

• For the SQL mode:

Add the following expression to the existing query and replace <field> with the required field.

Or <field> IS NULL

5. In the query builder, click Apply.
6. Click OK to close the Layer Properties dialog box.

The table shows the records with null values included when excluding records with 'Public' from the TYPE field in the definition query.