SQL NULL FUNCTIONS

What are SQL NULL functions?

SQL NULL functions evaluate, detect, or replace NULL values so queries return meaningful, non-NULL results.

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 NULL FUNCTIONS: COALESCE and NULLIF: PostgreSQL, MySQL, SQL Server, Oracle, SQLite, MariaDB, DB2, Snowflake, BigQuery ISNULL: SQL Server, Sybase, Azure SQL IFNULL: MySQL, MariaDB, SQLite, BigQuery NVL: Oracle, BigQuery

SQL NULL FUNCTIONS Full Explanation

NULL represents an unknown or missing value. Arithmetic or string operations that involve NULL propagate NULL and can ruin aggregates or comparisons. NULL functions let you interrogate or substitute these values so downstream logic behaves predictably.Standard SQL defines two portable NULL functions:• COALESCE – returns the first non-NULL argument in the list.• NULLIF – returns NULL if two expressions are equal, otherwise returns the first expression.Vendors add synonyms that accomplish similar tasks:• ISNULL (SQL Server, Sybase) – two-argument COALESCE.• IFNULL (MySQL, SQLite) – two-argument COALESCE.• NVL (Oracle, BigQuery) – two-argument COALESCE.Because COALESCE is ANSI-SQL compliant, it is the safest choice across databases. All functions evaluate arguments in order, stop at the first non-NULL candidate, and preserve data type precedence rules. NULLIF is often paired with COALESCE for defensive programming (e.g., COALESCE(NULLIF(col,''), 'N/A')). Watch out for performance impacts when large subqueries appear inside COALESCE arguments—each expression is still evaluated unless explicitly short-circuited by the optimizer.

SQL NULL FUNCTIONS Syntax

-- Standard SQL
COALESCE(expr1 , expr2 [, ...]);
NULLIF(expr1 , expr2);

-- Vendor specific equivalents
ISNULL (expr , replacement);
IFNULL(expr , replacement);
NVL   (expr , replacement);

SQL NULL FUNCTIONS Parameters

expr1..N Any SQL expression - returned in order of first non-NULL
expr1 - Any SQL expression
expr2 - Any SQL expression to compare to expr1
expr - Any SQL expression to test for NULL

Example Queries Using SQL NULL FUNCTIONS

-- 1. Replace nullable discount with 0
SELECT order_id,
       COALESCE(discount_pct, 0) AS discount_pct
FROM   sales;

-- 2. Avoid divide-by-zero by converting 0 to NULL and back
SELECT COALESCE(sales_amount / NULLIF(quantity,0), 0) AS avg_price
FROM   sales;

-- 3. Vendor-specific: SQL Server ISNULL
SELECT ISNULL(phone,'Unknown') AS phone
FROM   customers;

-- 4. Vendor-specific: Oracle NVL
SELECT NVL(notes,'No notes') AS notes
FROM   tickets;

Expected Output Using SQL NULL FUNCTIONS

Null discounts show as 0 instead of NULL.
When quantity is 0 the division returns 0 instead of error or NULL.
Rows with NULL phone numbers display "Unknown".
Ticket rows without notes display "No notes".

Use Cases with SQL NULL FUNCTIONS

Defaulting NULL metrics to 0 for reporting dashboards.
Replacing missing text with a placeholder to avoid blank UI fields.
Protecting mathematical operations from divide-by-zero errors.
Converting magic numbers (0, -1) to NULL for cleaner aggregates.
Building flexible search filters (e.g., WHERE COALESCE(param, column) = column).

Common Mistakes with SQL NULL FUNCTIONS

Using ISNULL/IFNULL/NVL in portable code instead of COALESCE.
Forgetting that COALESCE evaluates every argument unless optimizer short-circuits, which can slow queries containing subselects.
Expecting COALESCE to convert empty strings to NULL (only Oracle treats '' as NULL).
Mixing incompatible data types; resulting type follows precedence of all arguments and may yield unexpected casting errors.

First Introduced In

SQL-92 (COALESCE and NULLIF)

Frequently Asked Questions

What is the difference between COALESCE and NULLIF?

COALESCE picks the first non-NULL value from its argument list. NULLIF compares two expressions and returns NULL when they are equal, otherwise the first expression. Use NULLIF to convert sentinel values into NULL, then COALESCE to substitute a default.

Why does COALESCE return an integer when I pass strings?

The resulting data type follows the database's type-precedence rules. If any argument is numeric and others are strings that can cast to numbers, the output may become numeric. Cast explicitly to keep the desired type.

Can I nest NULL functions?

Yes. A common pattern is COALESCE(NULLIF(col, ''), 'Unknown'). First NULLIF turns empty strings into NULL, then COALESCE supplies the placeholder.

Are NULL functions portable across databases?

COALESCE and NULLIF are portable. ISNULL, IFNULL, and NVL are not. For cross-vendor scripts rely on COALESCE/NULLIF or include dialect-specific branches.