SQL NULLIF

What is SQL NULLIF?

Returns NULL if two expressions are equal, otherwise returns the first expression.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL NULLIF: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, IBM Db2

SQL NULLIF Full Explanation

NULLIF is a scalar conditional expression defined in the SQL standard that compares two expressions. If expression1 equals expression2 after type coercion, the function returns NULL; otherwise it returns expression1 unchanged. NULLIF is often used to prevent errors such as divide-by-zero or to convert specific sentinel values (for example 0 or -1) into NULLs so they can be handled uniformly by aggregate functions or COALESCE.Because NULLIF only evaluates its two arguments once, it is more efficient and concise than equivalent CASE statements like CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END. The data type of the result is the same as expression1 (after implicit casting if needed). Both arguments are evaluated before comparison, so avoid side-effects in their evaluation. NULLIF follows normal NULL comparison semantics: if either argument is NULL, the comparison returns UNKNOWN, and the function therefore returns expression1.

SQL NULLIF Syntax

NULLIF ( expression1 , expression2 );

SQL NULLIF Parameters

expression1 (any comparable expression) - The value that will be returned if it is not equal to expression2.
expression2 (any comparable expression) - The value to compare against expression1 to determine whether NULL should be returned.

Example Queries Using SQL NULLIF

-- Return NULL because 1 equals 1
SELECT NULLIF(1, 1) AS result;

-- Return 5 because 5 is not equal to 1
SELECT NULLIF(5, 1) AS result;

-- Avoid divide-by-zero by converting 0 to NULL
SELECT total_amount / NULLIF(quantity, 0) AS avg_price
FROM order_items;

-- Replace blank strings with NULL for cleaner reporting
SELECT NULLIF(TRIM(comment), '') AS cleaned_comment
FROM user_feedback;

Expected Output Using SQL NULLIF

First query returns NULL
Second query returns 5
Third query calculates avg_price; rows with quantity = 0 return NULL instead of causing an error
Fourth query converts empty strings to NULL, yielding NULL in cleaned_comment where comment was blank

Use Cases with SQL NULLIF

Guard against divide-by-zero in arithmetic expressions
Transform sentinel values (0, -1, empty string) into NULL for consistent handling
Shorter alternative to CASE WHEN ... THEN NULL ELSE ... END constructs
Data cleansing during ETL to standardize missing or invalid values

Common Mistakes with SQL NULLIF

Expecting NULLIF to return expression2 when expressions differ; it always returns expression1 or NULL
Forgetting that if either argument is already NULL, NULLIF returns expression1, not NULL
Using non-comparable data types, leading to implicit cast errors
Assuming performance benefits when expression2 is expensive to compute; both arguments are always evaluated

First Introduced In

SQL:1999 standard

Frequently Asked Questions

What does NULLIF return when the expressions differ?

It returns the value of the first expression unchanged.

How is NULLIF different from COALESCE?

NULLIF turns certain values into NULL, while COALESCE turns NULL into the first non-NULL value in its argument list.

Can NULLIF prevent divide-by-zero errors?

Yes. Wrapping the divisor with NULLIF(divisor, 0) converts zero to NULL, causing the result of the division to become NULL instead of raising an error.

Does NULLIF support string comparisons?

Absolutely. You can compare any data types that are comparable in your SQL dialect, including VARCHAR, DATE, and numeric types.