CASE Expressions — Conditional Logic in SQL

Lesson 0007 — Searched vs Simple CASE, NULL Handling, Bonus & Node Classification — ~18 min read

SQL's if/else is CASE. Top to bottom, first WHEN that is TRUE wins. No match + no ELSE → NULL. It is an expression (returns a value), not a statement.

What is a CASE Expression?

CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE fallback_value
END
CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE fallback_value
END

Example: Adult check with a variable.

SET @temp_age = 19;
SELECT
  CASE
    WHEN @temp_age >= 18 THEN 'Adult'
    ELSE 'Not an Adult'
  END AS result;
-- pgsql: no @variables; use a CTE or literal
SELECT
  CASE
    WHEN 19 >= 18 THEN 'Adult'
    ELSE 'Not an Adult'
  END AS result;
result
Adult

Searched CASE vs Simple CASE

TypeWhat it matchesNeeded forExample
Searched CASEBoolean conditions (>, <, BETWEEN, IS NULL)Thresholds / ranges / validationsWHEN score < 40 THEN 'Fail'
Simple CASEEquality (= against fixed values)Direct mappingCASE status WHEN 'P' THEN 'Pending' WHEN 'D' THEN 'Delivered' END

Scenario 1: Price-Based Product Classification

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL, price INT NULL
);
INSERT INTO products (product_id, name, price) VALUES
  (1,  'Laptop Sleeve',        18),
  (2,  'Wireless Mouse',       32),
  (3,  'Keyboard',             55),
  (4,  'Monitor',              150),
  (5,  'USB Cable',            8),
  (6,  'Webcam',               60),
  (7,  'HDMI Cable',           20),
  (8,  'Mechanical Keyboard',  120),
  (9,  'Sticker Pack',         0),
  (10, 'Gift Card',            19),
  (11, 'Mystery Box',          NULL),
  (12, 'Refurb Monitor',       61);
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL, price INT NULL
);
-- same INSERT as SQL
product_idnameprice
1Laptop Sleeve18
2Wireless Mouse32
3Keyboard55
4Monitor150
5USB Cable8
6Webcam60
7HDMI Cable20
8Mechanical Keyboard120
9Sticker Pack0
10Gift Card19
11Mystery BoxNULL
12Refurb Monitor61

Rule: NULL → Missing Price; ≤20 → Budget; 21–100 → Mid Range; >100 → Premium. Order matters — check IS NULL first.

SELECT product_id, name, price,
  CASE
    WHEN price IS NULL THEN 'Missing Price'
    WHEN price <= 20 THEN 'Budget'
    WHEN price > 20 AND price <= 100 THEN 'Mid Range'
    WHEN price > 100 THEN 'Premium'
  END AS price_category
FROM products;
SELECT product_id, name, price,
  CASE
    WHEN price IS NULL THEN 'Missing Price'
    WHEN price <= 20 THEN 'Budget'
    WHEN price > 20 AND price <= 100 THEN 'Mid Range'
    WHEN price > 100 THEN 'Premium'
  END AS price_category
FROM products;
product_idnamepriceprice_category
1Laptop Sleeve18Budget
2Wireless Mouse32Mid Range
3Keyboard55Mid Range
4Monitor150Premium
5USB Cable8Budget
6Webcam60Mid Range
7HDMI Cable20Budget
8Mechanical Keyboard120Premium
9Sticker Pack0Budget
10Gift Card19Budget
11Mystery BoxNULLMissing Price
12Refurb Monitor61Mid Range

If no WHEN matches and no ELSE, result is NULL. Every CASE must end with END.

Scenario 2: Boolean Evaluation — PASS / FAIL

CREATE TABLE rules (
  rule_id INT PRIMARY KEY, value INT
);
INSERT INTO rules (rule_id, value) VALUES
  (1, 75), (2, 40), (3, 60), (4, NULL),
  (5, 95), (6, 59), (7, 0), (8, 61);
CREATE TABLE rules (
  rule_id INT PRIMARY KEY, value INT
);
-- same INSERT

Rule: NULL → Missing; ≥40 → PASS; else FAIL.

SELECT rule_id, value,
  CASE
    WHEN value IS NULL THEN 'Missing'
    WHEN value >= 40 THEN 'PASS'
    ELSE 'FAIL'
  END AS verdict
FROM rules;
SELECT rule_id, value,
  CASE
    WHEN value IS NULL THEN 'Missing'
    WHEN value >= 40 THEN 'PASS'
    ELSE 'FAIL'
  END AS verdict
FROM rules;
rule_idvalueverdict
175PASS
240PASS
360PASS
4NULLMissing
595PASS
659PASS
70FAIL
861PASS

NULL Comparisons — UNKNOWN

Comparisons can return TRUE, FALSE, or UNKNOWN (NULL).

SELECT
  NULL = NULL     AS eq_null_null,   -- NULL (unknown)
  NULL <=> NULL   AS nullsafe_eq,    -- 1 (true) MySQL only
  NULL = 5        AS eq_null_5,      -- NULL
  NULL <=> 5      AS nullsafe_eq_5,  -- 0 (false)
  (NULL >= 60)    AS ge_null_60;     -- NULL
SELECT
  NULL = NULL                       AS eq_null_null,   -- NULL
  NULL IS NOT DISTINCT FROM NULL    AS nullsafe_eq,    -- true
  NULL = 5                          AS eq_null_5,      -- NULL
  NULL IS NOT DISTINCT FROM 5       AS nullsafe_eq_5,  -- false
  (NULL >= 60)                      AS ge_null_60;     -- NULL

Normalize Usernames — Missing vs Empty

CREATE TABLE users (
  user_id  INT PRIMARY KEY,
  username VARCHAR(60) NULL,
  INDEX idx_users_username (username)
);
INSERT INTO users (user_id, username) VALUES
  (1, 'Pro_User'), (2, 'guest123'), (3, 'PRO_member'), (4, 'ProXUser'),
  (5, 'pro_user99'), (6, ' Pro_User '), (7, ''), (8, NULL);
CREATE TABLE users (
  user_id  INT PRIMARY KEY,
  username VARCHAR(60) NULL
);
CREATE INDEX idx_users_username ON users(username);
-- same INSERT
SELECT user_id, username,
  CASE
    WHEN username <=> NULL THEN 'Not Filled'
    WHEN username = '' THEN 'Empty'
    ELSE username
  END AS username_display
FROM users;
SELECT user_id, username,
  CASE
    WHEN username IS NULL THEN 'Not Filled'
    WHEN username = '' THEN 'Empty'
    ELSE username
  END AS username_display
FROM users;
user_idusernameusername_display
1Pro_UserPro_User
2guest123guest123
3PRO_memberPRO_member
4ProXUserProXUser
5pro_user99pro_user99
6 Pro_User Pro_User
7''Empty
8NULLNot Filled

Scenario 3: Bonus Calculation with COALESCE

CREATE TABLE employees (
  emp_id            INT PRIMARY KEY, salary INT NOT NULL, performance_score INT NULL
);
INSERT INTO employees (emp_id, salary, performance_score) VALUES
  (101, 50000, 90), (102, 45000, NULL), (103, 60000, 74),
  (104, 70000, 75), (105, 80000, 89),    (106, 0,     92),
  (107, 52000, 100),(108, 50000, 0),     (109, 45000, 85),
  (110, 40000, NULL);
CREATE TABLE employees (
  emp_id            INT PRIMARY KEY, salary INT NOT NULL, performance_score INT NULL
);
-- same INSERT

Rule: NULL → 0; ≥90 → 20%; ≥75 → 10%; else 5%.

SELECT emp_id, salary, performance_score,
  CASE
    WHEN COALESCE(performance_score, -1) = -1 THEN 0
    WHEN performance_score >= 90 THEN salary * 0.20
    WHEN performance_score >= 75 THEN salary * 0.10
    ELSE salary * 0.05
  END AS bonus_amount
FROM employees
ORDER BY emp_id;
SELECT emp_id, salary, performance_score,
  CASE
    WHEN COALESCE(performance_score, -1) = -1 THEN 0
    WHEN performance_score >= 90 THEN salary * 0.20
    WHEN performance_score >= 75 THEN salary * 0.10
    ELSE salary * 0.05
  END AS bonus_amount
FROM employees
ORDER BY emp_id;
emp_idsalaryperformance_scorebonus_amount
101500009010000
10245000NULL0
10360000743000
10470000757000
10580000898000
1060920
1075200010010400
1085000002500
10945000854500
11040000NULL0

COALESCE(score, -1) = -1 detects NULL safely; salary 0 → bonus 0 even at 92%.

Scenario 4: Node Classification — ROOT / INTERNAL / LEAF

CREATE TABLE nodes (
  node_id   INT PRIMARY KEY, parent_id INT NULL,
  INDEX idx_nodes_parent_id (parent_id)
);
INSERT INTO nodes (node_id, parent_id) VALUES
  (1,  NULL), (2,  1), (3,  2), (4,  1),
  (5,  4),    (6,  4), (7,  6), (8,  NULL),
  (9,  8),    (10, 8), (11, 10), (12, 10);
CREATE TABLE nodes (
  node_id   INT PRIMARY KEY, parent_id INT NULL
);
CREATE INDEX idx_nodes_parent_id ON nodes(parent_id);
-- same INSERT
node_idparent_id
1NULL
21
32
41
54
64
76
8NULL
98
108
1110
1210

Rule: parent_id IS NULL → ROOT; else if has no children → LEAF; else INTERNAL. Check via NOT EXISTS.

SELECT node_id, parent_id,
  CASE
    WHEN parent_id IS NULL THEN 'ROOT'
    WHEN NOT EXISTS (SELECT 1 FROM nodes AS n2 WHERE n1.node_id = n2.parent_id) THEN 'LEAF'
    ELSE 'INTERNAL'
  END AS node_type
FROM nodes AS n1;
SELECT node_id, parent_id,
  CASE
    WHEN parent_id IS NULL THEN 'ROOT'
    WHEN NOT EXISTS (SELECT 1 FROM nodes AS n2 WHERE n1.node_id = n2.parent_id) THEN 'LEAF'
    ELSE 'INTERNAL'
  END AS node_type
FROM nodes AS n1;
node_idparent_idnode_type
1NULLROOT
21INTERNAL
32LEAF
41INTERNAL
54LEAF
64INTERNAL
76LEAF
8NULLROOT
98LEAF
108INTERNAL
1110LEAF
1210LEAF

Scenario 5: Conditional Aggregation

CASE inside aggregates splits metrics by condition — no extra GROUP BY needed.

CREATE TABLE sales (
  item     VARCHAR(30) NOT NULL, quantity INT NULL
);
INSERT INTO sales (item, quantity) VALUES
  ('apple',  10), ('orange', 7),  ('apple',  5),
  ('banana', 3),  ('mango',  2),  ('apple',  NULL),
  ('orange', NULL);
CREATE TABLE sales (
  item     VARCHAR(30) NOT NULL, quantity INT NULL
);
-- same INSERT
SELECT
  SUM(CASE WHEN item = 'apple'  THEN COALESCE(quantity, 0) ELSE 0 END) AS apples_sold,
  SUM(CASE WHEN item = 'orange' THEN COALESCE(quantity, 0) ELSE 0 END) AS oranges_sold
FROM sales;
SELECT
  SUM(CASE WHEN item = 'apple'  THEN COALESCE(quantity, 0) ELSE 0 END) AS apples_sold,
  SUM(CASE WHEN item = 'orange' THEN COALESCE(quantity, 0) ELSE 0 END) AS oranges_sold
FROM sales;
apples_soldoranges_sold
157

Apple: 10+5+0=15; Orange: 7+0=7 — COALESCE turns NULL quantities into 0 before summing.

Why check price IS NULL before price <= 20 in CASE?

Notes

CASE basics :-

CASE WHEN cond THEN val ... ELSE fallback END — first TRUE wins; no match + no ELSE → NULL

Searched CASE: WHEN score <40 …; Simple CASE: CASE status WHEN 'P' THEN …

Expression not statement — returns a value per row

Patterns :-

Price: IS NULL → Missing; <=20 Budget; 21–100 Mid; >100 Premium

Rules: IS NULL → Missing; >=40 PASS else FAIL

NULL = NULL → UNKNOWN; use IS NULL; MySQL <=> / PG IS NOT DISTINCT FROM for null-safe

Username: <=> NULL → Not Filled; ='' → Empty

Bonus: COALESCE(score,-1)=-1 → 0; ≥90 20%; ≥75 10%; else 5%

Nodes: parent_id IS NULL → ROOT; NOT EXISTS child → LEAF else INTERNAL

Conditional agg: SUM(CASE WHEN item='apple' THEN COALESCE(qty,0) ELSE 0 END)

Primary sources: MySQL CASE · Postgres CASE · MySQL NULL-safe. Ask me anything that's unclear.

← Prev: Single-Row Functions Next: String Functions →

Questions? Ask your agent — you can follow up on any concept, quiz answer, or how this applies to a specific query. Review the SQL glossary or syntax quick reference for a compressed overview.