Skip to main content

Null functions — Marketing Cloud SQL reference

Handling NULL in MC SQL — ISNULL, COALESCE, NULLIF, plus the rule for picking COALESCE over ISNULL, the NULLIF division-by-zero idiom, and the difference between ISNULL the function and IS NULL the operator.

Reference·Last updated 2026-05-07·Drafted by Lira · Edited by German Medina

NULL handling is a category of bugs more than a category of functions. MC SQL gives you three tools — ISNULL, COALESCE, NULLIF — and each has a sharp edge. ISNULL is T-SQL's two-argument convenience that quietly coerces types based on the first argument. COALESCE is the standard-SQL multi-argument version that's portable and predictable. NULLIF does the inverse: it converts a specific value back to NULL, which is the trick for safe division and "treat empty string as missing" patterns.

Official syntax

-- ISNULL: T-SQL, two args, returns replacement if value is NULL
SELECT ISNULL(LoyaltyTier, 'standard') AS Tier
FROM master_subscribers;

-- COALESCE: standard SQL, multiple args, returns first non-NULL
SELECT COALESCE(LoyaltyTier, MarketingTier, 'standard') AS Tier
FROM master_subscribers;

-- NULLIF: returns NULL when the two args are equal, else first arg
SELECT NULLIF(EmailAddress, '') AS EmailOrNull
FROM master_subscribers;

-- Division-by-zero protection via NULLIF
SELECT
  TotalSpend / NULLIF(LoyaltyPoints, 0) AS SpendPerPoint
FROM master_subscribers;

-- Combined: treat empty string as NULL, then default
SELECT COALESCE(NULLIF(LoyaltyTier, ''), 'standard') AS Tier
FROM master_subscribers;

The supported set:

| Function | What it does | Returns | |---|---|---| | ISNULL(value, replacement) | Returns replacement if value is NULL, else value | Type of value (1st arg) | | COALESCE(v1, v2, ..., vn) | Returns the first non-NULL value | Type of the highest-precedence arg | | NULLIF(v1, v2) | Returns NULL if v1 = v2, else v1 | Type of v1 | | IS NULL (operator) | Boolean test, true when value is NULL | (Used in WHERE, not a function) | | IS NOT NULL (operator) | Boolean test, true when value is non-NULL | (Used in WHERE, not a function) |

ISNULL (function) and IS NULL (operator) are different things despite the name overlap. ISNULL(x, y) substitutes a value; WHERE x IS NULL filters rows. Both come up in the same query and confusing them produces silent bugs.

Reference:

What survives in production

COALESCE over ISNULL for new code

Three reasons COALESCE is the better default:

  1. Multi-argument: COALESCE(a, b, c, 'default') cascades through fallbacks. ISNULL only takes two.
  2. Standard SQL: COALESCE is portable to every SQL dialect; ISNULL is T-SQL specific. Code that survives a platform migration starts here.
  3. Predictable types: ISNULL returns the type of the first argument, which can silently truncate the second. COALESCE returns the highest-precedence type across all arguments.
-- AT RISK — ISNULL returns the type of the first arg (VARCHAR(5)),
-- so 'standard-tier' (longer than 5 chars) gets truncated to 'stand'
SELECT ISNULL(CAST(LoyaltyTier AS VARCHAR(5)), 'standard-tier') AS Tier
FROM master_subscribers;
-- Returns 'stand', not 'standard-tier'

-- DURABLE — COALESCE evaluates types across all args, returns the wider one
SELECT COALESCE(CAST(LoyaltyTier AS VARCHAR(5)), 'standard-tier') AS Tier
FROM master_subscribers;
-- Returns 'standard-tier' as expected

The truncation case is contrived but the underlying type-coercion difference is real. Default to COALESCE and you avoid the surprise category entirely.

NULLIF for safe division and sentinel-as-missing patterns

Dividing by zero in MC SQL throws an error and kills the Activity. The defensive idiom is NULLIF(divisor, 0) — if the divisor is zero, the division becomes value / NULL, which is NULL, which is benign downstream (with a COALESCE to default it).

-- AT RISK — division by zero kills the Activity for any row where
-- LoyaltyPoints is 0
SELECT TotalSpend / LoyaltyPoints AS SpendPerPoint
FROM master_subscribers;

-- DURABLE — NULLIF turns the 0 into NULL, division becomes NULL,
-- COALESCE defaults to 0 (or whatever sentinel makes sense)
SELECT COALESCE(TotalSpend / NULLIF(LoyaltyPoints, 0), 0) AS SpendPerPoint
FROM master_subscribers;

The other major use of NULLIF: turning empty strings into NULL so they're handled by the rest of your NULL-aware logic.

-- AT RISK — empty strings count as "valid value" in WHERE filters
SELECT SubscriberKey
FROM master_subscribers
WHERE LoyaltyTier IS NOT NULL;
-- Returns rows where LoyaltyTier = '' (empty), which probably isn't what you wanted

-- DURABLE — convert '' to NULL before the IS NOT NULL check
SELECT SubscriberKey
FROM master_subscribers
WHERE NULLIF(LoyaltyTier, '') IS NOT NULL;
-- Now empty strings are correctly treated as missing

ISNULL (function) vs IS NULL (operator) — they're different things

Both come up in the same query and confusing them is a quiet, common bug.

-- ISNULL function — substitutes a value
SELECT ISNULL(LoyaltyTier, 'standard') AS Tier
FROM master_subscribers;

-- IS NULL operator — filters rows
SELECT SubscriberKey
FROM master_subscribers
WHERE LoyaltyTier IS NULL;

-- BUG — common confusion: ISNULL in a WHERE doesn't filter, it substitutes
SELECT SubscriberKey
FROM master_subscribers
WHERE ISNULL(LoyaltyTier, 'gold') = 'gold';
-- This returns subscribers where LoyaltyTier is 'gold' OR NULL
-- (because NULL gets substituted to 'gold'), which is rarely the intent.

When the goal is to filter, use IS NULL / IS NOT NULL. When the goal is to provide a default value in a projection, use COALESCE (or ISNULL if you accept its limitations).

COALESCE evaluates arguments lazily but coerces types eagerly

COALESCE(a, b, c) stops evaluating once it finds a non-NULL — but the type of the result is determined by the highest-precedence type across all arguments, regardless of which one ends up being returned. If one argument forces a type that doesn't fit your data, the surprise is not the value, it's the type.

-- COALESCE picks the result type from all args, not just the one returned
SELECT COALESCE(NULL, '42', 100) AS Result;
-- All args evaluated for type, INT (100) wins, '42' coerced to 42, returns 42

-- If you need a specific output type, cast explicitly
SELECT CAST(COALESCE(NULL, '42', '100') AS INT) AS Result;   -- string args, then cast

When the source columns are mixed types, cast inside the COALESCE call so you're in control of the type, not the coercion rules.

Quick decision

Use COALESCE when:

  • Default. New code. Multi-argument. Type-predictable.

Use ISNULL when:

  • Two-argument default and you've explicitly checked the type-coercion behavior matches what you want. Rare.

Use NULLIF when:

  • Protecting against division by zero (/ NULLIF(divisor, 0)).
  • Treating sentinel values (empty string, 'unknown', -1) as NULL so they're handled by NULL-aware logic.

Use IS NULL / IS NOT NULL (operator) when:

  • Filtering rows in WHERE. Never confuse with the ISNULL function.

Cast inside COALESCE when:

  • Source columns are mixed types and you want explicit control over the result type.

Related

  • Basics — supported T-SQL subset
  • SELECT — COALESCE in projection
  • WHERE — IS NULL / IS NOT NULL operators (vs the ISNULL function)
  • CASE — alternative to COALESCE for more complex conditions
  • Aggregate functions — wrap aggregates in COALESCE for empty-result-set handling
  • Numeric functions — NULLIF(divisor, 0) for safe division
  • Conversion functions — COALESCE(TRY_CAST(...), default) pattern
  • MC SQL gotchas — see #5 for NULL three-valued logic across the language

Catalog progress: With this page, the 6 function references are complete. The remaining catalog work is the SQL Style Guide (the opinionated piece pulling everything together) plus 3 how-to debugging snippets.