Query using addNullQuery and addNotNullQuery

ServiceNow's addNullQuery() and addNotNullQuery() handle null detection correctly where manual comparisons fail. The critical insight: ServiceNow distinguishes between database NULL and empty strings, but this behavior varies by field type. Reference fields store true NULL when empty, while string fields can contain empty strings that aren't NULL. Most developers initially try addQuery('field', '') or addQuery('field', '!=') which miss records where the field contains actual NULL values from the database layer.

When to use this

• Filtering reference fields like assignment_group, assigned_to, caller_id for truly empty assignments
• Finding records where journal fields like work_notes or comments have any content vs completely empty
• Validating required fields in Business Rules where you need to catch both NULL and empty values
• Data cleanup scripts identifying incomplete records across multiple field types

When NOT to use this

• Don't use for choice fields — they store string values, not NULL. Use addQuery('state', '') instead
• Don't use on boolean fields — they're never NULL, only true or false
• Avoid on large tables without proper indexing — NULL queries can't use standard indexes efficiently
• Don't combine with orderBy() on the same field — NULL values sort inconsistently across databases

Key behaviors and gotchas

• Reference fields: addNullQuery() finds truly unassigned records, addQuery('field', '') may miss some
• String fields: Both NULL and empty string exist — addNullQuery() only catches database NULL values
• Journal fields store aggregate content — addNotNullQuery('work_notes') means any historical entries exist
• Date/DateTime fields: Empty dates are stored as NULL, not empty strings
• Performance: NULL queries bypass normal field indexes and scan table data directly
• Import behavior: CSV imports create empty strings, not NULL — existing NULL values get preserved