These flow-control functions sit beside CASE and IF() (CASE, IF() and Pivots). COALESCE() returns its first non-NULL argument and IFNULL(a, b) is the two-argument form. NULLIF(a, b) is NULL when a = b, which turns a zero divisor into a safe NULL. GREATEST() and LEAST() clamp, and INTERVAL(n, b1, b2, ...) counts the sorted bounds n has reached, a quick banding tool.
SELECT p.sku, IFNULL(ROUND(AVG(r.rating), 1), 'none') AS avg,
COALESCE(LEFT(MAX(r.body), 14), p.attributes->>'$.format', 'n/a') AS blurb,
ROUND(p.price / NULLIF(p.stock, 0), 2) AS per_unit,
GREATEST(CAST(p.stock AS SIGNED) - 20, 0) AS surplus, LEAST(p.price, 30) AS capped,
INTERVAL(p.price, 10, 30, 45) AS band, GREATEST(p.price, NULL) AS g_null,
GREATEST(10, '9') AS g_mixed
FROM products AS p LEFT JOIN reviews AS r ON r.product_id = p.id
WHERE p.id IN (3, 6, 8) GROUP BY p.id;+-----------+------+----------------+----------+---------+--------+------+--------+---------+ | sku | avg | blurb | per_unit | surplus | capped | band | g_null | g_mixed | +-----------+------+----------------+----------+---------+--------+------+--------+---------+ | BK-SQL-02 | 3.0 | Good on B-tree | NULL | 0 | 29.00 | 1 | NULL | 9 | | BK-LNX-01 | 4.0 | paperback | 4.67 | 0 | 30.00 | 2 | NULL | 9 | | AC-STK-01 | none | n/a | 0.03 | 130 | 4.99 | 0 | NULL | 9 | +-----------+------+----------------+----------+---------+--------+------+--------+---------+
The out-of-stock ebook gets a NULL unit price, not a warning, and the Linux book, whose only review has no body, falls back to its JSON format. GREATEST() and LEAST() return NULL if any argument is NULL, and a mix of numbers and strings compares as strings, so GREATEST(10, '9') is '9'. IFNULL(AVG(...), 'none') also makes the column text; return the NULL and render "none" in PHP when the value feeds a chart.