Some values need to change shape based on a condition, right inside the query, without a separate step in application code afterward. These three expressions cover nearly every case that comes up.
Evaluates conditions in order and returns the value tied to the first one that matches, falling back to ELSE if none do.
SELECT
name,
price,
CASE
WHEN price < 10 THEN 'Budget'
WHEN price < 50 THEN 'Mid-range'
ELSE 'Premium'
END AS price_tier
FROM products;Common Mistake
Conditions that overlap can silently return the wrong branch — a common mistake is ordering price ranges so a later, more specific condition never gets reached because an earlier, broader one already matched.
Takes any number of arguments and returns the first one that isn't NULL — most often used to substitute a default when a value is missing.
SELECT
name,
COALESCE(nickname, name) AS display_name
FROM users;
-- Shows the nickname if one exists, otherwise falls back to the real name-- Works with more than two arguments too, tried in order
SELECT COALESCE(phone_mobile, phone_home, phone_work, 'No phone on file')
FROM contacts;The reverse idea — compares two values, and returns NULL if they're equal, or the first value otherwise. Most commonly used to avoid a divide-by-zero error.
SELECT
total_revenue / NULLIF(total_orders, 0) AS avg_order_value
FROM sales_summary;
-- If total_orders is 0, NULLIF returns NULL instead of 0,
-- and dividing by NULL gives NULL instead of a divide-by-zero error| Expression | Purpose |
|---|---|
| CASE | Multi-branch conditional logic — an if/elseif/else for a query |
| COALESCE(a, b, c, ...) | Returns the first non-NULL argument — a default value fallback |
| NULLIF(a, b) | Returns NULL if a equals b, otherwise returns a — often used to prevent divide-by-zero |