Topic 37 of 52 · Intermediate
Functions Reference: Conditional, Comparison & Conversion
CASE, IF, NULL handling, comparison operators and type conversion with their traps.
Concept
Conditional functions choose a value per row. CASE is standard SQL and works everywhere; IF() and IFNULL() are MySQL shortcuts.
NULL means unknown, so NULL = NULL is not true. Use IS NULL, IS NOT NULL or the null-safe operator <=>.
Conversion functions change a value's type. MySQL also converts implicitly ('10' + 5 = 15), which can hide bugs and disable indexes — be explicit with CAST.
Cheat sheet
| Function / operator | What it does | Example → result |
|---|---|---|
| CASE WHEN c THEN v ... ELSE d END | Searched CASE (any conditions) | CASE WHEN 72 >= 60 THEN 'First' ELSE 'Pass' END → 'First' |
| CASE x WHEN v THEN r END | Simple CASE (equality) | CASE 2 WHEN 1 THEN 'A' WHEN 2 THEN 'B' END → 'B' |
| IF(cond, a, b) | Two-way choice | IF(5 > 3, 'yes', 'no') → 'yes' |
| IFNULL(a, b) | b if a is NULL | IFNULL(NULL, 0) → 0 |
| COALESCE(a, b, c) | First non-NULL | COALESCE(NULL, NULL, 'x') → 'x' |
| NULLIF(a, b) | NULL if equal (avoid divide by zero) | 10 / NULLIF(0, 0) → NULL |
| ISNULL(x) | 1 if NULL else 0 | ISNULL(NULL) → 1 |
| a <=> b | Null-safe equals | NULL <=> NULL → 1 |
| BETWEEN a AND b | Inclusive range | 5 BETWEEN 1 AND 5 → 1 |
| IN (...) / NOT IN | Membership (NOT IN with a NULL returns no rows!) | 3 IN (1,2,3) → 1 |
| LIKE / NOT LIKE | Pattern: % any chars, _ one char | 'Hyderabad' LIKE 'Hyd%' → 1 |
| REGEXP / RLIKE | Regex match | 'A123' REGEXP '^[A-Z][0-9]+$' → 1 |
| EXISTS (subquery) | True if subquery returns a row | WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cid = c.id) |
| CAST(x AS SIGNED/UNSIGNED/DECIMAL/CHAR/DATE/DATETIME/JSON) | Explicit conversion | CAST('2026-11-04' AS DATE) → 2026-11-04 |
| CONVERT(x, type) | Same as CAST (MySQL syntax) | CONVERT('12.50', DECIMAL(5,2)) → 12.50 |
| CONVERT(s USING utf8mb4) | Changes character set | CONVERT(name USING utf8mb4) |
| BINARY s | Case-sensitive comparison | BINARY 'a' = 'A' → 0 |
| HEX(x) / UNHEX(s) | To / from hexadecimal | HEX(255) → 'FF' |
| BIN(n) / OCT(n) / CONV(n, from, to) | Number base conversion | CONV('ff', 16, 10) → '255' |
| INET_ATON / INET_NTOA | IPv4 text ↔ number | INET_ATON('10.0.0.1') → 167772161 |
Code (SQL · MySQL 8)
SELECT name,
marks,
CASE
WHEN marks >= 75 THEN 'Distinction'
WHEN marks >= 60 THEN 'First Class'
WHEN marks >= 35 THEN 'Pass'
ELSE 'Fail'
END AS grade,
IF(fee_paid, 'Paid', 'Pending') AS fee_status,
COALESCE(phone, email, 'no contact') AS contact,
ROUND(placed / NULLIF(applied, 0) * 100, 1) AS placement_pct,
CAST(roll_no AS UNSIGNED) AS roll_number
FROM students
WHERE email IS NOT NULL
AND name REGEXP '^[A-Z]';Remember
- Never write = NULL; use IS NULL or <=>.
- NOT IN (subquery) returns nothing if the subquery has a NULL — prefer NOT EXISTS.
- NULLIF(x, 0) is the standard divide-by-zero guard.
- CASE stops at the first true WHEN, so order conditions from most to least specific.
Interview questions
COALESCE vs IFNULL?
IFNULL takes exactly two arguments and is MySQL-specific; COALESCE takes any number and is standard SQL.
Why might NOT IN return zero rows?
If the list/subquery contains NULL, every comparison becomes unknown, so no row qualifies. Use NOT EXISTS or filter the NULLs.
How do you make a string comparison case-sensitive?
Use BINARY col = 'Value', or a case-sensitive collation like utf8mb4_bin.
Lesson test
Take an 8-question test on “Functions Reference: Conditional, Comparison & Conversion” — concepts, output prediction, bug spotting and interview questions.