Practical guide

How SQL NULL Works in Filters and Comparisons

NULL represents an unknown or absent value. Ordinary equality comparisons with NULL do not behave like comparisons with regular values.

What to do

  1. Find missing values with WHERE phone IS NULL. Use IS NOT NULL to find rows that contain a value.
  2. Do not write phone = NULL; the result is unknown rather than true. Use the IS NULL predicate instead.
  3. Use COALESCE to display a fallback in a query, for example COALESCE(phone, 'Not provided'), if that representation is appropriate.
  4. Check how NULL affects aggregates: COUNT(column) skips NULL values, while COUNT(*) counts rows.

Checks and limitations

  • An empty string and a NULL are distinct in many systems; imported datasets may use either for missing information.
  • Replacing NULL with a made-up value can affect analysis, so preserve the difference unless the task calls for a default.