SQL Null Handling

SQL NULLIF Function: Convert Values to NULL (All Databases)

SQL NULLIF returns NULL if two values are equal. Prevents division by zero and converts sentinel values to NULL.

Mar 12, 20263 min read

Overview

NULLIF returns NULL if two expressions are equal, otherwise returns the first expression. Its most common use is preventing division by zero.

All Databases

-- NULLIF(expr1, expr2):
SELECT NULLIF(10, 10);   -- NULL (they're equal)
SELECT NULLIF(10, 20);   -- 10 (they're different)

-- Prevent division by zero:
SELECT
  total_revenue,
  total_orders,
  total_revenue / NULLIF(total_orders, 0) AS avg_order_value
FROM monthly_stats;
-- If total_orders = 0, NULLIF returns NULL, division returns NULL instead of error

-- Convert empty strings to NULL:
SELECT NULLIF(TRIM(phone), '') AS phone FROM users;

-- Convert sentinel values to NULL:
SELECT NULLIF(age, -1) AS age FROM users;  -- treats -1 as NULL
SELECT NULLIF(status, 'N/A') AS status FROM records;

-- Combine with COALESCE:
SELECT
  revenue / NULLIF(costs, 0) AS roi,
  COALESCE(revenue / NULLIF(costs, 0), 0) AS roi_safe
FROM financials;

Skip the Syntax Lookup

Instead of memorizing NULLIF syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Try AI2SQL Free

No credit card required

Frequently Asked Questions

What does NULLIF do in SQL?

NULLIF(a, b) returns NULL if a equals b, otherwise returns a. It's the opposite of COALESCE — it creates NULLs instead of replacing them.

How do I prevent division by zero in SQL?

Wrap the divisor in NULLIF: SELECT x / NULLIF(y, 0). If y is 0, NULLIF returns NULL, and dividing by NULL gives NULL instead of an error.

Can AI2SQL handle division by zero?

Yes. AI2SQL automatically adds NULLIF protection when generating division queries to prevent runtime errors.