🔀

MariaDB Control Flow Functions — IF, CASE, COALESCE

MariaDB

Complete reference for MariaDB control flow functions covering IF, CASE, COALESCE, IFNULL, NULLIF, NVL, NVL2, and DECODE_ORACLE. Every function includes syntax, NULL-handling examples, and comparisons with MySQL and PostgreSQL conditional expressions. Updated for MariaDB 11.x.

12 functions

Jump to function

IF IFNULL NULLIF COALESCE CASE IIF NVL NVL2 DECODE GREATEST LEAST ISNULL

What are MariaDB Control Flow Functions — IF, CASE, COALESCE?

MariaDB control flow functions handle conditional branching and NULL substitution in SQL queries. COALESCE() returns the first non-NULL argument, IF(condition, true_val, false_val) is a shorthand ternary, and IFNULL() is the two-argument NULL coalescing shortcut. MariaDB adds Oracle compatibility functions NVL() and NVL2() in SQL_MODE=ORACLE, and DECODE_ORACLE() provides Oracle-style DECODE(). These functions differ from PostgreSQL's COALESCE and CASE in minor syntax details but are conceptually equivalent.

IF

→ ANY (type of true_value or false_value)

Returns true_value if condition is true (non-zero and non-NULL), otherwise returns false_value.

Signature

IF(condition, true_value, false_value)

Parameters

Parameter	Type	Description
condition	BOOLEAN / numeric	Expression evaluated as true (non-zero, non-NULL) or false
true_value	ANY	Value returned when condition is true
false_value	ANY	Value returned when condition is false or NULL

Examples

Basic true branch

sql

SELECT IF(1 > 0, 'yes', 'no');

→-- yes

Column-based condition

sql

SELECT IF(score >= 60, 'Pass', 'Fail') FROM exams;

→-- Pass or Fail

NULL condition is treated as false

sql

SELECT IF(NULL, 'true', 'false');

→-- false

Zero is false

sql

SELECT IF(0, 'true', 'false');

→-- false

Inline calculation

sql

SELECT IF(amount > 0, amount * 0.9, 0) AS discounted FROM orders;

→-- discounted amount or 0

⚠Anti-Pattern— Deep nesting of IF()

Nesting multiple IF() calls to handle several conditions becomes unreadable quickly.

✓ Instead: Use CASE WHEN ... THEN ... ELSE ... END for three or more branches — it is far more readable and maintainable.

⇄ vs PostgreSQL: PostgreSQL has no IF() function; use CASE WHEN condition THEN true_value ELSE false_value END.

See also:CASE IFNULL NULLIF IIF COALESCE

IFNULL

→ ANY (type of expr or fallback)

Returns expr if it is not NULL; otherwise returns fallback. Equivalent to COALESCE(expr, fallback) with exactly two arguments.

Signature

IFNULL(expr, fallback)

Parameters

Parameter	Type	Description
expr	ANY	Expression to test for NULL
fallback	ANY	Value returned when expr is NULL

Examples

NULL replaced by fallback

sql

SELECT IFNULL(NULL, 'default');

→-- default

Non-NULL returned as-is

sql

SELECT IFNULL('value', 'default');

→-- value

Replace NULL column

sql

SELECT IFNULL(middle_name, '') FROM customers;

→-- middle name or empty string

0 is not NULL

sql

SELECT IFNULL(0, 99);

→-- 0

Column fallback

sql

SELECT IFNULL(price, list_price) FROM products;

→-- price or list_price

⚠Anti-Pattern— Using IFNULL on aggregates without considering GROUP BY

SELECT IFNULL(SUM(amount), 0) looks safe, but SUM() over an empty set already returns NULL — make sure you actually need the IFNULL wrapper, and be aware it does not change row counts.

✓ Instead: Use COALESCE(SUM(amount), 0) which is more portable and equally clear.

⇄ vs PostgreSQL: PostgreSQL has no IFNULL(); use COALESCE(expr, fallback).

NULLIF

→ ANY (type of expr1, or NULL)

Returns NULL if expr1 equals expr2; otherwise returns expr1. Useful for preventing division-by-zero and converting sentinel values to NULL.

Signature

NULLIF(expr1, expr2)

Parameters

Parameter	Type	Description
expr1	ANY	Primary expression; returned when the two arguments differ
expr2	ANY	Comparison value; when equal to expr1 the result is NULL

Examples

Equal values return NULL

sql

SELECT NULLIF(5, 5);

→-- NULL

Unequal values return expr1

sql

SELECT NULLIF(5, 3);

→-- 5

Division-by-zero guard

sql

SELECT 100 / NULLIF(divisor, 0) FROM metrics;

→-- NULL instead of divide-by-zero error

Sentinel to NULL

sql

SELECT NULLIF(status, 'N/A') FROM records;

→-- NULL when status is 'N/A'

NULL = NULL comparison still yields NULL

sql

SELECT NULLIF(NULL, NULL);

→-- NULL

⚠Anti-Pattern— Expecting NULLIF to act like IFNULL

NULLIF produces NULL when values match; IFNULL replaces NULL with a fallback. Mixing them up inverts your logic.

✓ Instead: Use NULLIF to convert a value TO NULL, and IFNULL/COALESCE to convert FROM NULL.

⇄ vs PostgreSQL: PostgreSQL NULLIF() is identical.

COALESCE

→ ANY (type of first non-NULL argument)

Returns the first non-NULL argument from the list. Evaluates arguments left-to-right and short-circuits on the first non-NULL value.

Signature

COALESCE(value1, value2, ...)

Parameters

Parameter	Type	Description
value1, value2, ...	ANY	Values tested in order; the first non-NULL is returned

Examples

Returns first non-NULL

sql

SELECT COALESCE(NULL, NULL, 'fallback');

→-- fallback

Column fallback

sql

SELECT COALESCE(middle_name, 'N/A') FROM users;

→-- middle name or N/A

Multiple fallbacks

sql

SELECT COALESCE(price, list_price, 0.00) FROM products;

→-- first non-null price

All NULL returns NULL

sql

SELECT COALESCE(NULL, NULL, NULL);

→-- NULL

0 is not NULL

sql

SELECT COALESCE(0, 'fallback');

→-- 0

⚠Anti-Pattern— Confusing COALESCE with IFNULL

IFNULL(a, b) only accepts two arguments; COALESCE accepts any number. Nesting IFNULL(IFNULL(a, b), c) is harder to read than COALESCE(a, b, c).

✓ Instead: Use COALESCE when you have more than two fallback values.

⇄ vs PostgreSQL: PostgreSQL COALESCE() is identical.

CASE

→ ANY (type of matched THEN value or ELSE value)

Conditional expression with two forms: simple CASE compares an expression against fixed values; searched CASE evaluates arbitrary boolean conditions. Returns the value of the first matching THEN clause, or the ELSE value (NULL if ELSE is omitted).

Signatures

CASE expr WHEN val1 THEN result1 [WHEN val2 THEN result2 ...] [ELSE default] END

CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2 ...] [ELSE default] END

Parameters

Parameter	Type	Description
expr	ANY	Expression compared against WHEN values (simple form only)
condition	BOOLEAN	Boolean expression evaluated for each WHEN clause (searched form)
result	ANY	Value returned for a matching WHEN branch
default	ANY	Value returned when no WHEN matches; NULL if ELSE is omitted

Examples

Simple CASE form

sql

SELECT CASE status WHEN 'A' THEN 'Active' WHEN 'I' THEN 'Inactive' ELSE 'Unknown' END FROM accounts;

→-- Active, Inactive, or Unknown

Searched CASE form

sql

SELECT CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END AS grade FROM results;

→-- letter grade

NULL handling — equivalent to COALESCE

sql

SELECT CASE WHEN amount IS NULL THEN 0 ELSE amount END FROM orders;

→-- 0 or amount

Pivot-style aggregate

sql

SELECT SUM(CASE WHEN type = 'credit' THEN amount ELSE 0 END) AS total_credits FROM ledger;

→-- conditional aggregate

ELSE omitted; no match returns NULL

sql

SELECT CASE WHEN 1=1 THEN 'always' END;

→-- always

⚠Anti-Pattern— Omitting ELSE and ignoring the implicit NULL

When no WHEN clause matches and ELSE is absent, CASE returns NULL silently. This can cause unexpected NULLs to propagate through calculations.

✓ Instead: Always include an ELSE clause, even if just ELSE NULL, to make the intent explicit and prevent surprises.

⇄ vs PostgreSQL: PostgreSQL CASE WHEN is identical; PostgreSQL relies on CASE more heavily because it lacks IF().

IIF

MariaDB 10.3.2+→ ANY (type of true_value or false_value)

Returns true_value if condition is true (non-zero and non-NULL), otherwise returns false_value. IIF is an alias for IF() introduced in MariaDB 10.3.2 for compatibility with Microsoft SQL Server and Access.

Signature

IIF(condition, true_value, false_value)

Parameters

Parameter	Type	Description
condition	BOOLEAN / numeric	Expression evaluated as true (non-zero, non-NULL) or false
true_value	ANY	Value returned when condition is true
false_value	ANY	Value returned when condition is false or NULL

Examples

Identical to IF(1>0,'yes','no')

sql

SELECT IIF(1 > 0, 'yes', 'no');

→-- yes

Column-based branch

sql

SELECT IIF(stock > 0, 'In Stock', 'Out of Stock') FROM products;

→-- In Stock or Out of Stock

NULL condition treated as false

sql

SELECT IIF(NULL, 'true', 'false');

→-- false

Arithmetic in branches

sql

SELECT IIF(salary > 50000, salary * 0.1, salary * 0.05) AS bonus FROM employees;

→-- calculated bonus

Equality check

sql

SELECT IIF(a = b, 'equal', 'not equal') FROM comparisons;

→-- equal or not equal

⚠Anti-Pattern— Using IIF expecting it to work in MySQL

IIF() does not exist in MySQL; using it makes your SQL incompatible with plain MySQL deployments.

✓ Instead: Use IF() for MySQL-compatible code, or CASE WHEN for maximum portability.

⇄ vs MySQL: IIF() does not exist in MySQL; use IF() instead.

⇄ vs PostgreSQL: PostgreSQL has no IIF(); use CASE WHEN condition THEN true_value ELSE false_value END.

NVL

MariaDB 10.3+→ ANY (type of expr or fallback)

Returns expr if it is not NULL; otherwise returns fallback. Equivalent to IFNULL() and provided as an Oracle compatibility alias in MariaDB 10.3+.

Signature

NVL(expr, fallback)

Parameters

Parameter	Type	Description
expr	ANY	Expression to test for NULL
fallback	ANY	Value returned when expr is NULL

Examples

NULL replaced by fallback

sql

SELECT NVL(NULL, 'default');

→-- default

Non-NULL returned as-is

sql

SELECT NVL('value', 'default');

→-- value

Oracle-style NULL guard

sql

SELECT NVL(commission, 0) FROM employees;

→-- commission or 0

Column fallback chain

sql

SELECT NVL(phone, email) FROM contacts;

→-- phone or email

0 is not NULL

sql

SELECT NVL(0, 99);

→-- 0

⚠Anti-Pattern— Using NVL in code that must also run on MySQL

NVL() does not exist in MySQL. Code using it will fail on a standard MySQL server.

✓ Instead: Use IFNULL(expr, fallback) which works on both MariaDB and MySQL.

◆MariaDB Note

Available when the SQL mode includes Oracle compatibility (sql_mode=ORACLE) or as a standalone function in MariaDB 10.3+.

⇄ vs MySQL: NVL() does not exist in MySQL.

⇄ vs PostgreSQL: PostgreSQL has no NVL(); use COALESCE(expr, fallback).

NVL2

MariaDB 10.3+→ ANY (type of not_null_value or null_value)

Returns not_null_value if expr is not NULL; returns null_value if expr is NULL. An Oracle compatibility function added in MariaDB 10.3. Extends NVL by allowing a different return value for both the NULL and non-NULL cases.

Signature

NVL2(expr, not_null_value, null_value)

Parameters

Parameter	Type	Description
expr	ANY	Expression tested for NULL
not_null_value	ANY	Value returned when expr is NOT NULL
null_value	ANY	Value returned when expr IS NULL

Examples

Non-NULL takes second argument

sql

SELECT NVL2('hello', 'not null', 'is null');

→-- not null

NULL takes third argument

sql

SELECT NVL2(NULL, 'not null', 'is null');

→-- is null

Conditional calculation

sql

SELECT NVL2(bonus, salary + bonus, salary) AS total FROM employees;

→-- total with or without bonus

Presence check

sql

SELECT NVL2(email, 'Subscribed', 'Not subscribed') FROM customers;

→-- subscription status

0 is not NULL

sql

SELECT NVL2(0, 'has value', 'is null');

→-- has value

⚠Anti-Pattern— Expecting NVL2 argument order to match IF()

IF(condition, true_val, false_val) and NVL2(expr, not_null_val, null_val) share the same argument order (true branch second, false branch third), but NVL2's first argument is an expression tested for NULL, not a boolean. Passing a boolean condition to NVL2 evaluates whether that expression is NULL or not — usually not what you want.

✓ Instead: Use IF(expr IS NULL, null_val, not_null_val) or CASE WHEN to make the NULL check explicit.

◆MariaDB Note

Oracle compatibility function available in MariaDB 10.3+.

⇄ vs MySQL: NVL2() does not exist in MySQL.

⇄ vs PostgreSQL: PostgreSQL has no NVL2(); use CASE WHEN expr IS NOT NULL THEN a ELSE b END.

DECODE

MariaDB 10.3+ (Oracle mode)→ ANY (type of matched return value or default)

Compares expr against a series of search values and returns the corresponding result for the first match. An optional final argument serves as the default when no match is found. Available in MariaDB 10.3+ under Oracle SQL mode. NULL-safe: NULL = NULL is treated as a match.

Signature

DECODE(expr, search1, result1 [, search2, result2 ...] [, default])

Parameters

Parameter	Type	Description
expr	ANY	Expression to compare against each search value
search1, search2, ...	ANY	Values compared against expr in sequence
result1, result2, ...	ANY	Value returned when the corresponding search value matches expr
default	ANY	Optional value returned when no search value matches; NULL if omitted

Examples

Oracle-style simple CASE equivalent

sql

SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM accounts;

→-- Active, Inactive, or Unknown

Numeric match

sql

SELECT DECODE(1, 1, 'one', 2, 'two', 'other');

→-- one

NULL-safe: NULL matches NULL

sql

SELECT DECODE(NULL, NULL, 'was null', 'not null');

→-- was null

Grade-to-GPA mapping

sql

SELECT DECODE(grade, 'A', 4.0, 'B', 3.0, 'C', 2.0, 0.0) AS gpa FROM transcript;

→-- GPA value

Missing default returns NULL

sql

SELECT DECODE(x, 1, 'one');

→-- NULL when x != 1 (no default provided)

⚠Anti-Pattern— Confusing MariaDB DECODE with MySQL DECODE

MySQL has a DECODE(ciphertext, key) function that decrypts DES-encrypted data — completely different from Oracle/MariaDB DECODE. Using Oracle-style DECODE in MySQL will produce wrong results silently.

✓ Instead: In MySQL, use CASE WHEN. In MariaDB with Oracle mode, DECODE(expr, ...) works as the Oracle-compatible conditional.

◆MariaDB Note

Requires sql_mode=ORACLE or the Oracle compatibility plugin in MariaDB 10.3+.

⇄ vs MySQL: MySQL DECODE(ciphertext, key) is a DES decryption function — completely different. The Oracle-compatible conditional DECODE does not exist in MySQL.

⇄ vs PostgreSQL: PostgreSQL has no DECODE() conditional; use CASE WHEN. (PostgreSQL does have decode() for binary encoding, unrelated.)

GREATEST

→ ANY (type of the largest argument)

Returns the largest value from the list of arguments. Comparison follows the same rules as ORDER BY. If any argument is NULL, returns NULL.

Signature

GREATEST(value1, value2, ...)

Parameters

Parameter	Type	Description
value1, value2, ...	ANY (all comparable)	Two or more values to compare; the largest is returned

Examples

Largest integer

sql

SELECT GREATEST(3, 1, 4, 1, 5, 9);

→-- 9

String comparison (alphabetical)

sql

SELECT GREATEST('banana', 'apple', 'cherry');

→-- cherry

Latest of two date columns

sql

SELECT GREATEST(start_date, last_modified) FROM records;

→-- the more recent date

Any NULL argument produces NULL

sql

SELECT GREATEST(1, NULL, 3);

→-- NULL

Floor price enforcement

sql

SELECT GREATEST(price, min_price) AS effective_price FROM products;

→-- higher of the two prices

⚠Anti-Pattern— Using GREATEST as a MAX() replacement across rows

GREATEST operates across columns within a single row, not across multiple rows. MAX() aggregates across rows.

✓ Instead: Use MAX(column) to find the largest value across rows; use GREATEST(col1, col2) to find the largest value across columns in the same row.

⇄ vs PostgreSQL: PostgreSQL GREATEST() is identical, including NULL propagation behavior.

LEAST

→ ANY (type of the smallest argument)

Returns the smallest value from the list of arguments. Comparison follows the same rules as ORDER BY. If any argument is NULL, returns NULL.

Signature

LEAST(value1, value2, ...)

Parameters

Parameter	Type	Description
value1, value2, ...	ANY (all comparable)	Two or more values to compare; the smallest is returned

Examples

Smallest integer

sql

SELECT LEAST(3, 1, 4, 1, 5, 9);

→-- 1

String comparison (alphabetical)

sql

SELECT LEAST('banana', 'apple', 'cherry');

→-- apple

Earliest termination date

sql

SELECT LEAST(expiry_date, cancel_date) FROM subscriptions;

→-- earlier of the two dates

Any NULL argument produces NULL

sql

SELECT LEAST(1, NULL, 3);

→-- NULL

Price cap enforcement

sql

SELECT LEAST(price, cap_price) AS capped FROM products;

→-- price or cap, whichever is lower

⚠Anti-Pattern— Confusing LEAST with MIN() aggregate

LEAST compares values across columns in the same row; MIN() aggregates across rows. They are not interchangeable.

✓ Instead: Use MIN(column) to find the smallest value across rows; use LEAST(col1, col2) to find the smaller of two columns in the same row.

⇄ vs PostgreSQL: PostgreSQL LEAST() is identical, including NULL propagation behavior.

ISNULL

→ INT (1 or 0)

Returns 1 if expr is NULL, 0 otherwise. A convenience function equivalent to the predicate expr IS NULL.

Signature

ISNULL(expr)

Parameters

Parameter	Type	Description
expr	ANY	Expression to test for NULL

Examples

NULL returns 1

sql

SELECT ISNULL(NULL);

→-- 1

Non-NULL returns 0

sql

SELECT ISNULL('value');

→-- 0

0 is not NULL

sql

SELECT ISNULL(0);

→-- 0

Filter for NULL rows

sql

SELECT * FROM orders WHERE ISNULL(shipped_at);

→-- unshipped orders

NULL presence as a boolean column

sql

SELECT ISNULL(middle_name) AS has_no_middle FROM customers;

→-- 1 or 0 flag

⚠Anti-Pattern— Comparing ISNULL result with = instead of using it as a boolean

Writing WHERE ISNULL(col) = 1 is redundant. The function already returns 1 or 0.

✓ Instead: Write WHERE ISNULL(col) or, more readably, WHERE col IS NULL.

⇄ vs MySQL: MySQL also has ISNULL() with identical behavior.

⇄ vs PostgreSQL: PostgreSQL has no ISNULL() function; use the IS NULL predicate.

Related MariaDB Categories

String Functions →Aggregate Functions →JSON Functions →