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

SQL NULLIF Compatibility Across Databases
Database System	Support Status	Since Version	Notes
MySQL	Native	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	Native	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	Native	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	Native	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	Native	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.

Details

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

-- Avoid division by zero: SELECT total_revenue / NULLIF(total_orders, 0) AS avg_order_value FROM sales_summary; -- Convert empty string to NULL: SELECT NULLIF(email, '') AS email FROM users;

Version Support

MySQL: Native since 3.23 PostgreSQL: Native since 6.0 SQL Server: Native since 2000 Oracle: Native since 9i SQLite: Native since 3.0

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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;