Conditional Logic in SQL

SQL isn’t just about retrieving data - sometimes you need to apply rules or handle missing values directly in your queries. That’s where conditional expressions like CASE WHEN and COALESCE come in. They let you make decisions and keep your reports clean without changing the underlying tables.

CASE WHEN and COALESCE Explained with a a sample hospital database.

patients

patient_id	name	age	diagnosis
1	James Kariuki	45	Diabetes
2	Mary Achieng	30	NULL
3	Peter Otieno	65	Hypertension
4	Sarah Njeri	50	NULL

appointments

appointment_id	patient_id	doctor	visit_date	fee
101	1	Dr. Kim	2026-09-01	2000
102	2	Dr. Ali	2026-09-02	1500
103	3	Dr. Kim	2026-09-03	2500
104	4	Dr. Patel	2026-09-04	NULL

1. CASE WHEN - Conditional Logic

CASE WHEN works like an if-else statement inside SQL. It lets you categorize or transform values based on conditions.

Example: Categorize patients by age group.

SELECT name,
       age,
       CASE 
         WHEN age < 40 THEN 'Young Adult'
         WHEN age BETWEEN 40 AND 60 THEN 'Middle Age'
         ELSE 'Senior'
       END AS age_group
FROM patients;

Output:

name	age	age_group
James Kariuki	45	Middle Age
Mary Achieng	30	Young Adult
Peter Otieno	65	Senior
Sarah Njeri	50	Middle Age

CASE WHEN helps you apply business rules directly in queries.

Example: Flag expensive appointments.

SELECT appointment_id, fee,
       CASE 
         WHEN fee >= 2000 THEN 'High Cost'
         ELSE 'Standard'
       END AS fee_category
FROM appointments;

Output:

appointment_id	fee	fee_category
101	2000	High Cost
102	1500	Standard
103	2500	High Cost
104	NULL	Standard

2. COALESCE - Handling NULLs

COALESCE replaces NULL values with a default. It’s like saying: “If this is missing, use that instead.”

Example: Show diagnosis, or mark as ‘Not Recorded’.

SELECT name,
       COALESCE(diagnosis, 'Not Recorded') AS diagnosis_status
FROM patients;

Output:

name	diagnosis_status
James Kariuki	Diabetes
Mary Achieng	Not Recorded
Peter Otieno	Hypertension
Sarah Njeri	Not Recorded

COALESCE ensures missing data doesn’t break your reports.

Example: Replace missing fees with 0.

SELECT appointment_id,
       COALESCE(fee, 0) AS fee_paid
FROM appointments;

Output:

appointment_id	fee_paid
101	2000
102	1500
103	2500
104	0

General Notes
CASE WHEN → Adds conditional logic (like if-else).

COALESCE → Handles missing values by substituting defaults.

Together, they make SQL queries smarter and more resilient.

Key Takeaway
Conditional logic in SQL lets you apply rules and handle missing data without changing your tables. Whether you’re categorizing patients, flagging expensive appointments, or filling in missing diagnoses, CASE WHEN and COALESCE give you the flexibility to make your queries reflect real-world business needs.