MySQL IF: function, statement, and IFNULL

Quick answer

In a query, IF() is a function that takes three arguments:

SELECT IF(age >= 40, 'senior', 'junior') FROM users;

In a stored procedure or function, IF is a statement:

IF age >= 40 THEN
  SET label = 'senior';
ELSE
  SET label = 'junior';
END IF;

These are different things and they are not interchangeable. Using the statement form in a plain query is the most common mistake here.

The IF() function

IF(condition, value_if_true, value_if_false)

SELECT name, IF(age >= 40, 'senior', 'junior') AS bracket FROM users;

name            bracket
Ada Lovelace    junior
Alan Turing     senior
Grace Hopper    senior

All three arguments are required. There is no two-argument form, and unlike CASE it cannot chain.

Truthiness rules

MySQL treats the condition as a number, and this catches people out:

SELECT IF(NULL, 'true-branch', 'false-branch') AS null_cond,
       IF(0, 'a', 'b') AS zero,
       IF(1, 'a', 'b') AS one,
       IF('abc', 'a', 'b') AS str;

null_cond       zero  one  str
false-branch    b     a    b

Two things worth pinning down:

IF(NULL, ...) takes the false branch. A NULL condition is not true, so it falls through. That is reasonable, but it means IF(x, 'yes', 'no') returns 'no' both when x is false and when x is unknown, silently merging two different states. If you care about the difference, test for it explicitly.

A non-numeric string is false. IF('abc', ...) returns the false branch, because 'abc' converts to 0. So IF(status, ...) on a text column is almost never what you want.

The IF statement

Inside a stored procedure, function, or trigger, IF is control flow:

DELIMITER //

CREATE PROCEDURE categorize(IN user_age INT, OUT label VARCHAR(20))
BEGIN
  IF user_age >= 65 THEN
    SET label = 'retired';
  ELSEIF user_age >= 40 THEN
    SET label = 'senior';
  ELSE
    SET label = 'junior';
  END IF;
END //

DELIMITER ;

Note ELSEIF is one word. ELSE IF as two words parses as a nested IF inside an ELSE, which then needs its own END IF. It usually still works, and it reads as a bug.

Try the statement form in a plain query and you get:

IF 1=1 THEN SELECT 'x'; END IF;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'IF 1=1 THEN SELECT 'x'' at line 1

A 1064 near an IF almost always means you wanted the function, not the statement.

IFNULL

Two arguments. Returns the first if it is not NULL, otherwise the second:

SELECT name, IFNULL(status, 'unknown') AS status FROM users;

name            status
Ada Lovelace    active
Alan Turing     unknown
Grace Hopper    active

IFNULL only tests for NULL. It does not treat empty strings or zeros as missing, which is usually right and occasionally surprising:

SELECT IFNULL('', 'fallback');   -- returns '', not 'fallback'

If empty strings should also fall back, say so:

SELECT IF(status IS NULL OR status = '', 'unknown', status) FROM users;

Or use NULLIF to convert empties to NULL first:

SELECT IFNULL(NULLIF(status, ''), 'unknown') FROM users;

COALESCE

IFNULL with any number of arguments. Returns the first non-NULL:

SELECT name, COALESCE(status, email, 'none') AS contact FROM users;

name            contact
Ada Lovelace    active
Alan Turing     alan@example.com
Grace Hopper    active

Alan's status is NULL, so it fell through to his email.

Prefer COALESCE over IFNULL. It is standard SQL and works in PostgreSQL, SQLite, and SQL Server, whereas IFNULL is MySQL-specific. There is no performance difference. IFNULL is shorter, and that is its only advantage.

NULLIF

The inverse. Returns NULL if the two arguments are equal, otherwise the first:

SELECT NULLIF(status, 'active') FROM users;

Its main use is guarding division:

SELECT total / NULLIF(count, 0) AS average FROM stats;

Without it, count = 0 gives a division-by-zero. With it, the divisor becomes NULL and the result is NULL, which is usually the honest answer for "average of nothing".

IF() vs CASE

IF() handles one condition. CASE handles many, and is standard SQL:

SELECT name,
  CASE
    WHEN age >= 65 THEN 'retired'
    WHEN age >= 40 THEN 'senior'
    ELSE 'junior'
  END AS bracket
FROM users;

The equivalent with nested IF() is legal and horrible:

SELECT IF(age >= 65, 'retired', IF(age >= 40, 'senior', 'junior')) FROM users;

Two branches is the point where CASE wins. It also ports to other databases, which nested IF() does not.

CASE has a shorthand form for equality against one expression:

SELECT CASE status
         WHEN 'active'   THEN 'Active user'
         WHEN 'inactive' THEN 'Dormant'
         ELSE 'Unknown'
       END
FROM users;

Careful with that form and NULL: CASE status WHEN NULL THEN ... never matches, because it compares with =, and NULL = NULL is unknown. Use the long form with WHEN status IS NULL THEN.

Without an ELSE, CASE returns NULL when nothing matches. That is a common source of unexpected NULLs.

Using IF() to count and aggregate

This is where IF() earns its keep, and it is genuinely useful:

SELECT
  COUNT(*)                          AS total,
  SUM(IF(status = 'active', 1, 0))  AS active,
  SUM(status = 'active')            AS active_shorthand
FROM users;

Both SUM columns produce the same number, because a comparison in MySQL evaluates to 1 or 0. The shorthand is idiomatic MySQL but does not port; the IF() form is clearer to a reader.

For conditional aggregates, CASE inside the aggregate is the portable pattern:

SELECT
  SUM(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_revenue,
  AVG(CASE WHEN status = 'active' THEN amount END)        AS active_avg
FROM orders;

Note the missing ELSE in the AVG. That is deliberate, and it changes the answer. Given two active orders of 100 and 200, and one inactive order:

SELECT AVG(CASE WHEN status='active' THEN amount END)        AS avg_no_else,
       AVG(CASE WHEN status='active' THEN amount ELSE 0 END) AS avg_with_else
FROM orders;

avg_no_else   avg_with_else
150.0000      100.0000

AVG ignores NULLs, so omitting ELSE averages only the active rows and gives 150. Adding ELSE 0 feeds a zero into the average for every inactive row and drags it to 100. The first is the average order value of active orders. The second is not the average of anything anybody asked for.

IF() in WHERE, and why not to

You can:

SELECT * FROM users WHERE IF(age > 40, status = 'active', status IS NOT NULL);

You should not. Wrapping a column in a function stops MySQL using an index on it, so this forces a full scan. Write the boolean logic directly:

SELECT * FROM users
WHERE (age > 40 AND status = 'active')
   OR (age <= 40 AND status IS NOT NULL);

Longer, and it can use an index.

Common problems

ERROR 1064 near IF. You used the statement form outside a stored program. Use IF() the function, or CASE.

IF() returns the false branch for rows I expected to match. The condition is NULL. IF(NULL, ...) is false. Check for NULLs in the columns you are testing.

IFNULL is not replacing my empty strings. It only tests NULL. Use NULLIF(col, '') first, or test explicitly.

CASE returns NULL unexpectedly. No branch matched and there is no ELSE.

My query got slow after adding IF() to WHERE. It killed the index. Rewrite as plain boolean conditions.

Quick reference