NULL values in GIS data can trip you up if you don’t know how to deal with them.  A NULL value is not the same as a blank (empty) value, and actually takes up 1 bit more of space than an empty value.  Allowing a field to be NULL introduces an additional state that you would not have if the field was created to not allow NULLs.  And yes, you can have NULLs in numeric fields too!

Why have a NULL value in your data?  Usually this is used to mean “not set” or “uninitialized”.  A good example would be an employee database with a termination date field that is set to NULL when the employee is new.  Another example would be an elevation field.  You could use a NULL value to represent “not known”, since zero is a valid elevation.  More than likely you have fields that allow NULLs because someone created them not paying attention to the defaults.

So how do you specify a field to allow NULLs or not?  Open ArcMap (I’m using version 10.2.2) and bring in some data from a geodatabase.  Then open the data’s attribute table and select “Add Field…” from the Table Options icon.

Note by default in the Field Properties that “Allow NULL Values” is set to “Yes”.  If you do not want NULLs, set it to “No”.  If you set it to “No” and do not specify a “Default Value”, then Text fields will default to a blank (empty) value and numeric fields will default to zero.  Also, keep in mind that you can add a field with “Allow NULL Values” set to “No” only if your feature class or table is not populated yet with records.

So let’s take a look at some test data I created with some fields that allow NULLs.

As you can probably guess, the field TFIELD is a text field and NFIELD is a numeric field.  Note some records have NULL values while others have data.

Using the Select By Attributes tool, I can select NULL values in the TFIELD by constructing the following SQL expression.

The records with NULL in the TFIELD are selected.

If I wanted to select everything but NULL, I would have used this SQL expression.

Numeric fields work the same way when selecting NULLs.

Now let’s take a few records and calc the TFIELD value to blank.  First I select a few records.

Then right click on the TFIELD name in the attribute table and select “Field Calculator…”.  Then in Field Calculator I specify TFIELD = “” (that is two double quotes with no space between them).

Note the values change from NULL to blank.

Now let’s select NULL values in TFIELD again with the Select By Attributes tool using “TFIELD IS NULL”.  Look what happens.

Note the blank records were not selected.  This shows that blanks are not the same as NULLs.  You have to be careful of this.  If you wanted all blanks and NULLs, try this expression in the Select By Attributes tool.

Note in this tool I had to use two single quotes with no spaces between for the SQL expression.  This selects all NULLs and blanks.

What if I wanted to calc a NULL value to a field?  First I will select some records, like my blank values in the TFIELD, then using the Field Calculator I specify TFIELD = NULL.

I can do the same for the numeric field.  First I will select all records with the value of 55.

Then using the Field Calculator I specify NFIELD = NULL.  All values of 55 are changed to NULL.

So there you have it.  You are now an expert with dealing with NULLs and hopefully when you setup your data, you will know when to use them and NOT NULL!