NULLIF SQL Compatibility | MySQL, PostgreSQL, SQL Server, Oracle, SQLite

Returns NULL if two expressions are equal, otherwise returns the first expression. Commonly used to suppress zero-division errors and convert sentinel values to NULL.

Returns NULL if the two arguments are equal; otherwise returns the first argument. Useful for avoiding division by zero (NULLIF(col, 0)) and for masking sentinel values. Combined with COALESCE for default substitution.

Compatibility

Details

Database System	Support Status	Since Version	Notes
MySQL	✓ Supported	3.23	NULLIF(expr1, expr2) is fully supported. Standard SQL behavior: returns NULL when expr1 = expr2, returns expr1 otherwise. The comparison is type-sensitive — NULLIF('0', 0) compares after implicit conversion.
PostgreSQL	✓ Supported	6.0	Standard NULLIF(expr1, expr2) supported. Return type is the type of expr1. Useful combined with COALESCE for conditional NULL substitution. Works on any comparable data type including text, dates, and numeric.
SQL Server	✓ Supported	2000	Standard NULLIF(expr1, expr2) supported. Commonly used to guard against division-by-zero in SQL Server, where dividing by zero raises an error (not a NULL). NULLIF is often preferred over CASE WHEN ... THEN NULL for readability.
Oracle	✓ Supported	9i	Standard NULLIF(expr1, expr2) supported since Oracle 9i. In Oracle, dividing by zero raises ORA-01476, so NULLIF is the standard guard. Works on NUMBER, VARCHAR2, DATE, and other comparable types.
SQLite	✓ Supported	3.0	Standard NULLIF(expr1, expr2) supported. SQLite returns NULL for integer division by zero rather than raising an error, but NULLIF is still useful for explicit intent and portability. Works with any SQLite type affinity.

NULLIF(a, b) is exactly equivalent to CASE WHEN a = b THEN NULL ELSE a END, and the SQL standard specifies it that way. Its primary use case is division-by-zero protection: NULLIF(denominator, 0) converts a zero to NULL, making the entire division return NULL instead of raising an error. Oracle is notable: it treats empty string '' as NULL natively, so NULLIF(col, '') on VARCHAR2 is usually a no-op. SQL Server division by zero raises a runtime error (unlike SQLite which returns NULL), making NULLIF particularly important there.

Standard Syntax

Version Support

Per-Database Syntax & Notes

MySQL

NULLIF(expr1, expr2) is fully supported. Standard SQL behavior: returns NULL when expr1 = expr2, returns expr1 otherwise. The comparison is type-sensitive — NULLIF('0', 0) compares after implicit conversion.

-- Division-by-zero guard: SELECT amount / NULLIF(quantity, 0) AS unit_price FROM order_lines; -- Empty string to NULL: SELECT NULLIF(phone, '') AS phone, NULLIF(fax, '') AS fax FROM contacts; -- NULLIF is equivalent to CASE: -- NULLIF(a, b) = CASE WHEN a = b THEN NULL ELSE a END

PostgreSQL

Standard NULLIF(expr1, expr2) supported. Return type is the type of expr1. Useful combined with COALESCE for conditional NULL substitution. Works on any comparable data type including text, dates, and numeric.

-- Division-by-zero guard: SELECT revenue / NULLIF(costs, 0) AS margin FROM financials; -- Combine with COALESCE for default value: SELECT COALESCE(NULLIF(nickname, ''), full_name) AS display_name FROM users; -- Date comparison: SELECT NULLIF(end_date, '9999-12-31'::date) AS actual_end FROM contracts;

SQL Server

Standard NULLIF(expr1, expr2) supported. Commonly used to guard against division-by-zero in SQL Server, where dividing by zero raises an error (not a NULL). NULLIF is often preferred over CASE WHEN ... THEN NULL for readability.

-- Division-by-zero guard (SS raises error on /0): SELECT total_sales / NULLIF(units_sold, 0) AS avg_unit_price FROM products; -- Empty string to NULL before ISNULL: SELECT ISNULL(NULLIF(phone, ''), 'N/A') AS phone_display FROM contacts; -- Equivalent CASE: -- SELECT CASE WHEN a = b THEN NULL ELSE a END

Oracle

Standard NULLIF(expr1, expr2) supported since Oracle 9i. In Oracle, dividing by zero raises ORA-01476, so NULLIF is the standard guard. Works on NUMBER, VARCHAR2, DATE, and other comparable types.

-- Division-by-zero guard: SELECT revenue / NULLIF(expense, 0) AS ratio FROM financials; -- Empty string: note Oracle treats '' as NULL natively, -- so NULLIF(col, '') on VARCHAR2 is typically redundant. SELECT NULLIF(status_code, 'N/A') AS status FROM tickets;

SQLite

Standard NULLIF(expr1, expr2) supported. SQLite returns NULL for integer division by zero rather than raising an error, but NULLIF is still useful for explicit intent and portability. Works with any SQLite type affinity.

-- Division-by-zero guard: SELECT price / NULLIF(quantity, 0) AS unit_cost FROM inventory; -- Convert sentinel value to NULL: SELECT NULLIF(category, 'UNKNOWN') AS category FROM products;