MySQL Control Flow Functions
Control flow functions let you embed conditional logic directly inside SQL queries. Instead of fetching raw data and branching in application code, you can transform and categorize values at the database level — reducing round trips and simplifying your application layer.
IF — Simple Conditional
IF(condition, true_value, false_value) is the simplest branching function. It evaluates the condition and returns one of two values.
SELECT IF(1 > 0, 'yes', 'no'); -- 'yes' SELECT IF(NULL = 1, 'yes', 'no'); -- 'no' (NULL comparisons are falsy) -- Label order status SELECT order_id, total, IF(total >= 100, 'Free Shipping', 'Standard Shipping') AS shipping_type FROM orders; -- Count active vs inactive users SELECT SUM(IF(is_active = 1, 1, 0)) AS active_count, SUM(IF(is_active = 0, 1, 0)) AS inactive_count FROM users;
IF() works well for simple two-way choices. For three or more branches, use CASE for readability.IFNULL — NULL Replacement
IFNULL(expr, replacement) returns the replacement value when expr is NULL, otherwise returns expr. It is shorthand for IF(expr IS NULL, replacement, expr).
SELECT IFNULL(NULL, 'default'); -- 'default'
SELECT IFNULL('value', 'default'); -- 'value'
SELECT IFNULL(0, 'default'); -- 0 (0 is not NULL)
-- Display "N/A" when phone is missing
SELECT
name,
IFNULL(phone, 'N/A') AS phone
FROM customers;
-- Default to 0 when no orders exist (with LEFT JOIN)
SELECT
c.name,
IFNULL(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;NULLIF — Return NULL on Match
NULLIF(expr1, expr2) returns NULL when both expressions are equal, otherwise returns expr1. Its primary use is preventing division-by-zero errors.
SELECT NULLIF(5, 5); -- NULL SELECT NULLIF(5, 3); -- 5 SELECT NULLIF(0, 0); -- NULL -- Prevent division by zero SELECT total_revenue / NULLIF(total_orders, 0) AS avg_order_value FROM monthly_summary; -- When total_orders = 0, NULLIF returns NULL, making the division return NULL -- instead of throwing an error -- Treat empty strings as NULL SELECT NULLIF(TRIM(notes), '') AS cleaned_notes FROM orders;
COALESCE — First Non-NULL Value
COALESCE(val1, val2, ..., valN) returns the first non-NULL value from its argument list. It accepts any number of arguments and is the SQL standard equivalent of a null-coalescing chain.
SELECT COALESCE(NULL, NULL, 'third'); -- 'third'
SELECT COALESCE(NULL, 'second', 'third'); -- 'second'
SELECT COALESCE('first', 'second'); -- 'first'
SELECT COALESCE(NULL, NULL, NULL); -- NULL (all NULL)
-- Use mobile number if available, then home, then work
SELECT
name,
COALESCE(mobile_phone, home_phone, work_phone, 'No phone') AS best_contact
FROM customers;
-- Fill in missing price with category average
SELECT
product_name,
COALESCE(price, category_avg_price, 9.99) AS effective_price
FROM products p
JOIN category_stats cs ON p.category_id = cs.category_id;COALESCE() over nested IFNULL() when you have more than two fallback values — it is cleaner and more portable across SQL databases.CASE WHEN — Searched Form
The searched CASE expression evaluates each WHEN condition independently and returns the result of the first matching branch. The optional ELSE clause handles unmatched rows; without it, unmatched rows return NULL.
SELECT
order_id,
total,
CASE
WHEN total >= 1000 THEN 'Platinum'
WHEN total >= 500 THEN 'Gold'
WHEN total >= 100 THEN 'Silver'
ELSE 'Bronze'
END AS tier
FROM orders;
-- Bucket ages into groups
SELECT
name,
age,
CASE
WHEN age < 18 THEN 'Under 18'
WHEN age BETWEEN 18 AND 24 THEN '18-24'
WHEN age BETWEEN 25 AND 34 THEN '25-34'
WHEN age BETWEEN 35 AND 44 THEN '35-44'
ELSE '45+'
END AS age_group
FROM customers;CASE expression — Simple Form
The simple CASE form compares a single expression against a list of values. It is more concise when you are comparing one column to multiple literal values.
SELECT
order_id,
status,
CASE status
WHEN 'pending' THEN 'Awaiting Payment'
WHEN 'paid' THEN 'Processing'
WHEN 'shipped' THEN 'On the Way'
WHEN 'delivered' THEN 'Completed'
WHEN 'cancelled' THEN 'Cancelled'
ELSE 'Unknown'
END AS status_label
FROM orders;CASE in ORDER BY
You can use CASE inside ORDER BY to apply custom sort logic that is not based on the raw column value.
-- Sort by custom status priority (not alphabetical)
SELECT order_id, status, created_at
FROM orders
ORDER BY
CASE status
WHEN 'urgent' THEN 1
WHEN 'pending' THEN 2
WHEN 'processing'THEN 3
WHEN 'shipped' THEN 4
ELSE 5
END,
created_at ASC;
-- Sort NULL values to the end (default: NULL sorts first in ASC)
SELECT name, priority
FROM tasks
ORDER BY CASE WHEN priority IS NULL THEN 1 ELSE 0 END, priority ASC;CASE in Aggregates (Conditional Aggregation)
Combining CASE with aggregate functions like SUM() and COUNT() is one of the most powerful patterns in SQL. It lets you compute multiple group summaries in a single query — effectively pivoting row data into columns.
-- Count orders by status in one query (pivot)
SELECT
DATE_FORMAT(created_at, '%Y-%m') AS month,
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
ROUND(
SUM(CASE WHEN status = 'delivered' THEN total ELSE 0 END), 2
) AS delivered_revenue
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;
-- Count males vs females
SELECT
SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male,
SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female,
SUM(CASE WHEN gender NOT IN ('M','F') OR gender IS NULL THEN 1 ELSE 0 END) AS other
FROM users;Nesting CASE Expressions
CASE expressions can be nested inside each other, though deep nesting becomes hard to read. Consider using derived tables or CTEs for complex logic.
SELECT
product_id,
stock_qty,
price,
CASE
WHEN stock_qty = 0 THEN 'Out of Stock'
WHEN stock_qty < 10 THEN
CASE
WHEN price > 500 THEN 'Low Stock - High Value'
ELSE 'Low Stock'
END
ELSE 'In Stock'
END AS availability_label
FROM products;Practical Data Transformation Examples
Translate numeric rating to stars:
SELECT
product_id,
avg_rating,
CASE
WHEN avg_rating >= 4.5 THEN '5 Stars'
WHEN avg_rating >= 3.5 THEN '4 Stars'
WHEN avg_rating >= 2.5 THEN '3 Stars'
WHEN avg_rating >= 1.5 THEN '2 Stars'
ELSE '1 Star'
END AS star_label
FROM product_ratings;Apply tiered pricing:
SELECT
u.user_id,
u.name,
o.total,
ROUND(
o.total * (1 - CASE
WHEN u.loyalty_tier = 'gold' THEN 0.15
WHEN u.loyalty_tier = 'silver' THEN 0.10
WHEN u.loyalty_tier = 'bronze' THEN 0.05
ELSE 0
END),
2
) AS discounted_total
FROM orders o
JOIN users u ON o.user_id = u.id;Safe average (skip rows with zero denominators):
SELECT category_id, AVG(CASE WHEN views > 0 THEN clicks / views ELSE NULL END) AS avg_ctr FROM articles GROUP BY category_id;
Quick Reference
Function / Syntax | Purpose | Notes |
|---|---|---|
IF(cond, t, f) | Two-way branch | Returns t if cond is true, else f |
IFNULL(expr, val) | Replace NULL | Returns val only when expr is NULL |
NULLIF(a, b) | Produce NULL on match | Returns NULL when a = b |
COALESCE(a, b, ...) | First non-NULL | Any number of arguments |
CASE WHEN ... END | Multi-branch (searched) | Each WHEN has its own condition |
CASE expr WHEN ... END | Multi-branch (simple) | Compares expr to literal values |
CASE in ORDER BY | Custom sort order | Assign numeric priority per value |
SUM(CASE ...) | Conditional aggregation | Pivot rows into columns |