Skip to main content

CASE — Marketing Cloud SQL reference

Conditional logic in MC SQL — Simple vs Searched CASE, the missing-ELSE NULL trap, type compatibility across branches, and the rule for when to stage into a lookup DE instead.

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

CASE is how you put conditional logic into a SQL expression. In MC SQL it works like T-SQL: a CASE returns a single value per row based on which WHEN matches first. There are two forms — Simple CASE (compares against a single column) and Searched CASE (each WHEN is its own boolean expression). The bugs are mostly about the ELSE you forgot to write.

Official syntax

-- Simple CASE — equality check against a single column
SELECT
  SubscriberKey,
  CASE LoyaltyTier
    WHEN 'gold'    THEN 'priority'
    WHEN 'silver'  THEN 'priority'
    WHEN 'bronze'  THEN 'standard'
    ELSE 'unsegmented'
  END AS Segment
FROM master_subscribers;

-- Searched CASE — each WHEN is a full boolean expression
SELECT
  SubscriberKey,
  CASE
    WHEN LoyaltyTier IN ('gold','silver') AND LastPurchase >= DATEADD(day, -30, GETDATE())
      THEN 'priority-active'
    WHEN LoyaltyTier IN ('gold','silver')
      THEN 'priority-dormant'
    WHEN LoyaltyTier = 'bronze'
      THEN 'standard'
    ELSE 'unsegmented'
  END AS Segment
FROM master_subscribers;

-- CASE inside an aggregate — counting matches per condition
SELECT
  EmailDomain,
  COUNT(*) AS TotalSubs,
  COUNT(CASE WHEN LoyaltyTier = 'gold'   THEN 1 END) AS GoldCount,
  COUNT(CASE WHEN LoyaltyTier = 'silver' THEN 1 END) AS SilverCount,
  SUM(CASE WHEN Status = 'Active'        THEN 1 ELSE 0 END) AS ActiveCount
FROM master_subscribers
GROUP BY EmailDomain;

CASE evaluates its WHEN clauses in order and returns the value from the first match. Order matters when conditions overlap. The expression has to return a single, type-compatible value across all branches.

Reference:

What survives in production

Always include ELSE — missing ELSE returns NULL

Without an ELSE, any row that doesn't match any WHEN returns NULL. The next WHERE, JOIN, or INSERT INTO then has to deal with that NULL — and three-valued logic doesn't behave the way most readers assume (see WHERE and gotchas — #5).

-- AT RISK — no ELSE, NULL leaks downstream
SELECT
  SubscriberKey,
  CASE LoyaltyTier
    WHEN 'gold'   THEN 'priority'
    WHEN 'silver' THEN 'priority'
  END AS Segment           -- bronze, NULL → returns NULL
FROM master_subscribers;

-- DURABLE — explicit ELSE catches every other case
SELECT
  SubscriberKey,
  CASE LoyaltyTier
    WHEN 'gold'   THEN 'priority'
    WHEN 'silver' THEN 'priority'
    ELSE 'standard'
  END AS Segment
FROM master_subscribers;

The discipline: every CASE ends with ELSE, even if the ELSE is a literal 'unknown' or NULL you wrote intentionally. Writing ELSE NULL is fine — it tells the next dev you thought about the unmatched case.

All branches must return the same type

If one WHEN returns a string and another returns an integer, the engine implicitly casts — sometimes correctly, sometimes silently corrupting. Lock the type explicitly.

-- AT RISK — branches return different types, implicit coercion
SELECT
  SubscriberKey,
  CASE
    WHEN LoyaltyTier = 'gold'  THEN 1            -- INT
    WHEN LoyaltyTier = 'silver' THEN 'tier-2'    -- VARCHAR
    ELSE 0
  END AS TierScore
FROM master_subscribers;

-- DURABLE — all branches the same type, explicit cast where needed
SELECT
  SubscriberKey,
  CASE
    WHEN LoyaltyTier = 'gold'   THEN 'tier-1'
    WHEN LoyaltyTier = 'silver' THEN 'tier-2'
    ELSE 'tier-0'
  END AS TierScore
FROM master_subscribers;

Order matters — most-specific WHEN first

CASE returns the first match. If a more general condition comes before a more specific one, the specific one never fires.

-- BUG — the first WHEN catches everyone with LoyaltyTier='gold',
-- so the second WHEN (gold + recent purchase) is unreachable
CASE
  WHEN LoyaltyTier = 'gold' THEN 'gold'
  WHEN LoyaltyTier = 'gold' AND LastPurchase >= DATEADD(day, -30, GETDATE()) THEN 'gold-active'
  ELSE 'standard'
END

-- CORRECT — most-specific first
CASE
  WHEN LoyaltyTier = 'gold' AND LastPurchase >= DATEADD(day, -30, GETDATE()) THEN 'gold-active'
  WHEN LoyaltyTier = 'gold' THEN 'gold'
  ELSE 'standard'
END

When CASE gets nested 3+ levels deep, stage into a lookup DE instead

Nested CASE reads like a labyrinth in code review and changes are scary. If the logic encodes business rules that change quarterly (segment definitions, discount tiers, eligibility flags), put the rules in a small Data Extension and JOIN against it.

-- AT RISK — deeply nested CASE, business rules buried in SQL
CASE
  WHEN LoyaltyTier = 'gold' THEN
    CASE WHEN LastPurchase >= DATEADD(day, -30, GETDATE()) THEN 'gold-active'
         WHEN LastPurchase >= DATEADD(day, -90, GETDATE()) THEN 'gold-warm'
         ELSE 'gold-cold' END
  WHEN LoyaltyTier = 'silver' THEN
    CASE WHEN LastPurchase >= DATEADD(day, -60, GETDATE()) THEN 'silver-active'
         ELSE 'silver-cold' END
  ELSE 'standard'
END

-- DURABLE — rules live in de_lookup_segment_rules, JOIN to apply.
-- Marketing edits the DE; the SQL doesn't change.
SELECT
  s.SubscriberKey,
  r.SegmentName
FROM master_subscribers s
LEFT JOIN de_lookup_segment_rules r
  ON s.LoyaltyTier = r.LoyaltyTier
  AND DATEDIFF(day, s.LastPurchase, GETDATE()) BETWEEN r.MinDays AND r.MaxDays;

The lookup DE is two columns wider, but the SQL stops being the source of truth for business logic. Marketing can read de_lookup_segment_rules directly and update it without filing a ticket.

Quick decision

Use Simple CASE (CASE col WHEN val THEN ...) when:

  • You're comparing one column against a small set of literal values.
  • Equality is the only check needed.

Use Searched CASE (CASE WHEN expr THEN ...) when:

  • Conditions involve multiple columns, ranges, or IN / LIKE.
  • Each branch needs its own full boolean expression.

Always include ELSE when:

  • The result feeds into a WHERE filter, JOIN condition, or destination DE column. NULL leakage downstream is the bug.

Stage into a lookup DE instead of CASE when:

  • The logic encodes business rules that change frequently.
  • The nesting goes 3+ levels deep.
  • Marketing needs to edit the rules without touching SQL.

Use CASE inside aggregates when:

  • Counting / summing rows that match a condition. Prefer SUM(CASE WHEN x THEN 1 ELSE 0 END) to guarantee numeric output.

Related

  • Basics — supported T-SQL subset
  • SELECT — CASE in projection
  • WHERE — using CASE results in filters (and the NULL trap)
  • JOIN — when to lift logic out of CASE into a lookup DE
  • MC SQL gotchas — see #5 for NULL three-valued logic implications

More reference pages incoming: INSERT INTO · String / Date / Numeric / Conversion / Aggregate / Null Functions · Style Guide.

Plus how-to snippets for common production debugging — email sends, value length, contact reach, etc.