← SQL roadmap

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 / operatorWhat it doesExample → result
CASE WHEN c THEN v ... ELSE d ENDSearched CASE (any conditions)CASE WHEN 72 >= 60 THEN 'First' ELSE 'Pass' END → 'First'
CASE x WHEN v THEN r ENDSimple CASE (equality)CASE 2 WHEN 1 THEN 'A' WHEN 2 THEN 'B' END → 'B'
IF(cond, a, b)Two-way choiceIF(5 > 3, 'yes', 'no') → 'yes'
IFNULL(a, b)b if a is NULLIFNULL(NULL, 0) → 0
COALESCE(a, b, c)First non-NULLCOALESCE(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 0ISNULL(NULL) → 1
a <=> bNull-safe equalsNULL <=> NULL → 1
BETWEEN a AND bInclusive range5 BETWEEN 1 AND 5 → 1
IN (...) / NOT INMembership (NOT IN with a NULL returns no rows!)3 IN (1,2,3) → 1
LIKE / NOT LIKEPattern: % any chars, _ one char'Hyderabad' LIKE 'Hyd%' → 1
REGEXP / RLIKERegex match'A123' REGEXP '^[A-Z][0-9]+$' → 1
EXISTS (subquery)True if subquery returns a rowWHERE EXISTS (SELECT 1 FROM orders o WHERE o.cid = c.id)
CAST(x AS SIGNED/UNSIGNED/DECIMAL/CHAR/DATE/DATETIME/JSON)Explicit conversionCAST('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 setCONVERT(name USING utf8mb4)
BINARY sCase-sensitive comparisonBINARY 'a' = 'A' → 0
HEX(x) / UNHEX(s)To / from hexadecimalHEX(255) → 'FF'
BIN(n) / OCT(n) / CONV(n, from, to)Number base conversionCONV('ff', 16, 10) → '255'
INET_ATON / INET_NTOAIPv4 text ↔ numberINET_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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Functions Reference: Conditional, Comparison & Conversion” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.