IF (x > 0) THEN ... END IF; inside a plain SELECT | IF(x > 0, a, b) — the three-argument function — anywhere an expression is valid | IF ... THEN ... END IF is a statement, only legal inside stored-program bodies (triggers, procedures, functions). IF() the function takes exactly 3 args and returns a value; it works in ordinary SQL |
CASE ... END CASE in a SELECT list or WHERE clause | CASE ... END (no CASE after END) as an expression | The bare CASE value WHEN ... END / CASE WHEN ... END operator is valid anywhere an expression is valid. CASE ... END CASE is the compound statement form, legal only inside stored-program bodies |
IFNULL(a, b) when there are more than two fallback candidates | COALESCE(a, b, c, ...) | IFNULL() is fixed at exactly 2 arguments; COALESCE() accepts any number and returns the first non-NULL. With exactly two arguments they're equivalent |
IFNULL(x, ...) to test whether x is NULL | ISNULL(x) for a boolean test, IFNULL(x, y) for a substitution | They solve different problems: ISNULL(expr) is single-argument and returns 1/0; IFNULL(expr1, expr2) is two-argument and returns a value (expr1 or expr2) |
x / y where y can be 0, relying on a NULL-safe division | x / NULLIF(y, 0) | NULLIF(a, b) returns NULL when a = b, otherwise a — it's shorthand for CASE WHEN a=b THEN NULL ELSE a END. Guarding the divisor with NULLIF(y, 0) turns a division error into a NULL result instead of failing the statement |
COALESCE(int_col, 'literal') / mixing incompatible types across branches, expecting the DB to "figure it out" | Cast explicit branches to a common type, or rely on documented type-aggregation rules | COALESCE(), IF(), and CASE all aggregate a single result type across their value arguments. A hex literal passed to COALESCE() is treated as a string, not a number, even though the same literal inserted directly into an INT column is treated as a number — a documented gotcha in the COALESCE reference page |
DECODE(expr, search1, result1, search2, result2, default) and expecting it to work outside Oracle mode | DECODE_ORACLE(expr, search1, result1, ...) for the value-matching form in any SQL mode | Plain DECODE(crypt_str, pass_str) is the decryption function (reverse of ENCODE()) in default mode — only two arguments. The Oracle-style value-matching form is available under that name only when sql_mode=ORACLE; DECODE_ORACLE is the synonym that works in all modes |
DECODE(expr, NULL, 'x', 'y') expecting NULL to never match | Rely on NULL-equals-NULL matching, or guard explicitly if that's unwanted | In the Oracle-mode matching form (and in DECODE_ORACLE), NULL values in the search list are treated as equivalent to a NULL expr — the opposite of standard SQL = semantics, where NULL = NULL is NULL/unknown |
