The previous module introduced CASE and COALESCE, the two standard ways to make a decision inside an expression. MySQL adds a set of shorter functions for the decisions that come up constantly: pick one of two values, replace a NULL, turn a sentinel into NULL, take the larger of two columns, sort by a list. They are small, but they make up a large share of real report queries and of interview answers — "avoid division by zero" and "sort status in a custom order" are both one-function answers.

IF: a two-way choice

IF(cond, a, b) returns a when cond is true and b otherwise.

SELECT name, stock,
       IF(stock > 0, 'in stock', 'sold out') AS availability
FROM products
ORDER BY name;

"Otherwise" includes NULL: IF(NULL > 0, 'yes', 'no') returns 'no', because the condition is not true. That matches CASE, which also falls to ELSE when no WHEN is true.

IF is exactly a two-branch CASE:

IF(stock > 0, 'in stock', 'sold out')
CASE WHEN stock > 0 THEN 'in stock' ELSE 'sold out' END

IF is MySQL-only (SQL Server has a similar IIF); CASE is standard. Nesting IFs for three or more outcomes — IF(a, x, IF(b, y, z)) — works but reads badly; once there is a third branch, switch to CASE. Do not confuse the IF() function with the IF … THEN … END IF statement of stored procedures, which is a different thing.

IFNULL and COALESCE: replacing NULL

IFNULL(a, b) returns a unless it is NULL, in which case b. COALESCE does the same for any number of arguments and returns the first non-NULL one.

SELECT name,
       IFNULL(phone, 'no phone')               AS phone,
       COALESCE(mobile, phone, email, 'none')  AS best_contact
FROM contacts
ORDER BY name;

They matter most in arithmetic, where one NULL poisons the whole expression: price - discount is NULL for every product with no discount, and price - IFNULL(discount, 0) is the price. For portability prefer COALESCE; SQL Server's two-argument version is spelled ISNULL(a, b) — which in MySQL is a different, one-argument function that returns 1 or 0.

NULLIF: turning a value into NULL

NULLIF(a, b) returns NULL when a = b, and a otherwise. It sounds backwards until you see its two jobs.

Safe division. Dividing by NULLIF(d, 0) divides by NULL instead of zero, which gives NULL rather than an error:

SELECT campaign,
       ROUND(100 * clicks / NULLIF(views, 0), 1) AS ctr
FROM campaigns
ORDER BY campaign;

MySQL returns NULL for a division by zero anyway, but PostgreSQL and SQL Server raise an error and stop the query, so the NULLIF guard is the habit worth having everywhere. Wrap the whole expression in COALESCE(…, 0) if a zero is wanted instead of NULL.

Cleaning sentinels. Old data often stores "unknown" as an empty string, 0 or 'N/A'. NULLIF(col, '') turns those into real NULLs, so COALESCE, COUNT(col) and AVG(col) treat them as missing:

SELECT COALESCE(NULLIF(TRIM(nickname), ''), name) AS shown_name
FROM users;

GREATEST and LEAST: across columns

GREATEST(a, b, …) returns the largest of its arguments and LEAST the smallest. They work within one row, across columns or expressions, which is what separates them from the aggregates MAX and MIN (next module), which work down a column across rows.

SELECT player,
       GREATEST(round1, round2, round3) AS best_round,
       LEAST(round1, round2, round3)    AS worst_round,
       GREATEST(price - discount, 0)    AS charged      -- never below zero
FROM scores
ORDER BY player;

GREATEST(x, 0) is the standard way to clamp a value at a floor, and LEAST(x, 100) at a ceiling. Combine them for a range: LEAST(GREATEST(score, 0), 100).

The NULL rule differs between databases. In MySQL, if any argument is NULL the result is NULL; PostgreSQL ignores NULL arguments instead. On MySQL, wrap nullable columns: GREATEST(COALESCE(a, 0), COALESCE(b, 0)).

FIELD: position in a list

FIELD(x, v1, v2, v3, …) returns the position of x in the list — 1 for v1, 2 for v2 — and 0 when x is not there. Its main use is sorting in an order that is neither alphabetical nor numeric:

SELECT id, status
FROM tickets
ORDER BY FIELD(status, 'urgent', 'open', 'waiting', 'closed'), id;

Statuses outside the list get 0 and come first. To put unknown values last, sort by FIELD(…) = 0 first — false (0) sorts before true (1) — or list them explicitly. The portable spelling is a CASE that maps each value to a number:

ORDER BY CASE status WHEN 'urgent' THEN 1 WHEN 'open' THEN 2
                     WHEN 'waiting' THEN 3 ELSE 4 END, id

Choosing between them

Use the shorthand when it reads better and the code will only run on MySQL; use CASE and COALESCE when portability or an interviewer's preference for standard SQL matters.

Pitfalls

• IF treats a NULL condition as false — make sure that is what you mean.
• Nesting IF three deep instead of writing a CASE.
• ISNULL(a, b) is SQL Server; MySQL's ISNULL(a) takes one argument and returns 1 or 0.
• GREATEST/LEAST return NULL in MySQL if any argument is NULL.
• FIELD returns 0 for unlisted values, which sorts them first.
• Dividing by a column that can be zero without NULLIF.

Key takeaways

• IF(c, a, b) is a two-branch CASE; use CASE for anything more.
• IFNULL/COALESCE replace NULLs; NULLIF creates them — safe division and sentinel cleanup.
• GREATEST/LEAST work across columns and clamp values.
• FIELD gives a custom sort order; a CASE mapping does the same portably.