CASE & NULL Functions

CASE works like if/else in SQL. Simple CASE matches one expression; searched CASE uses full conditions.

CASE — conditional logic in queries

CASE works like if/else in SQL. Simple CASE matches one expression; searched CASE uses full conditions.

Real-life example: CASE is the grading rubric — 90+ is A, 80–89 is B, everything else is C.

Letter grades from scores

SELECT
  student_id,
  score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'Needs improvement'
  END AS letter_grade
FROM enrollments;

NULL Functions — COALESCE, IFNULL, NVL

COALESCE(a, b, c) returns the first non-NULL value. MySQL uses IFNULL(x, y). SQL Server uses ISNULL(x, y). Oracle uses NVL(x, y).

Real-life example: COALESCE is 'use city if filled, otherwise use Unknown' on a mailing label.

Replace NULL city with a default

SELECT
  full_name,
  COALESCE(city, 'Unknown') AS display_city
FROM students;

-- MySQL
SELECT full_name, IFNULL(city, 'Unknown') AS display_city FROM students;