CASE Expressions and Conditional Logic

Make SQL choose a value per row, categorize data and count subsets in one pass

Big idea: CASE is SQL's if / else. It looks at each row, checks conditions in order, and returns the value for the first condition that is true. Because it is an expression, it produces a value you can drop into a SELECT list, an ORDER BY, a WHERE, or even inside an aggregate like SUM() to count exactly the rows you care about.

By the end of this topic you will be able to:

write both the simple and the searched forms of CASE,
categorize rows into labels or grade bands,
use the conditional aggregation pattern (SUM(CASE ... )) to count subsets and build pivots,
guard against divide-by-zero with NULLIF,
replace NULL with a fallback using IFNULL / COALESCE.

0. The example table

Every query below runs against one small table of exam results. Some scores are NULL because the student was absent, and one student has attempts = 0 - both facts will matter later.

CREATE TABLE students (
  student_id  INT PRIMARY KEY,
  full_name   VARCHAR(50),
  subject     VARCHAR(20),
  score       INT,          -- 0..100, NULL if the student was absent
  attempts    INT
);

INSERT INTO students VALUES
  (1, 'Aida Nurlan',   'Math',    92,   1),
  (2, 'Bekzat Orozov', 'Math',    58,   2),
  (3, 'Chris Doyle',   'Math',    NULL, 0),
  (4, 'Dina Sultan',   'History', 74,   1),
  (5, 'Erlan Kadyr',   'History', 45,   3),
  (6, 'Farida Iman',   'Science', 88,   1),
  (7, 'Gulnara Sadyk', 'Science', 63,   2),
  (8, 'Hasan Umar',    'Science', NULL, 0);

Keep this table in mind: 8 students, 3 subjects, two absentees (scores NULL).

1. Simple CASE: compare one value against a list

The simple form takes an expression once, then compares it to a series of WHEN values using plain equality. Use it when you are mapping one column to a fixed set of labels.

SELECT full_name, subject,
  CASE subject
    WHEN 'Math'    THEN 'STEM'
    WHEN 'Science' THEN 'STEM'
    WHEN 'History' THEN 'Humanities'
    ELSE 'Other'
  END AS faculty
FROM students;

Result (first rows):

full_name	subject	faculty
Aida Nurlan	Math	STEM
Dina Sultan	History	Humanities
Farida Iman	Science	STEM

The expression after CASE (here subject) is evaluated once and matched against each WHEN value with =. The ELSE is optional; if you omit it and nothing matches, the result is NULL.

CASE Expressions and Conditional Logic