CASE and conditional logic

When an electronic school diary shows a score, it also shows a letter grade next to it: 94 is an “A”, 81 a “B”. An online shop writes “out of stock” next to a product, and the head teacher wants to see the number of students per grade and city in one table. All of these are conditions inside a query, and in SQL we write them with the CASE expression. CASE is like if in other programming languages, but it is an expression that returns a value, not a statement — so it can be used anywhere a column can.

Searched CASE: CASE WHEN ... THEN ... END

The DBMS checks the WHEN conditions from top to bottom and returns the THEN value of the first true one; it doesn't look at the rest. If no condition is true, the ELSE value is returned, or NULL if there is no ELSE. The expression always ends with END and is usually given a name with AS.

SELECT s.first_name, e.score,
       CASE
         WHEN e.score >= 90 THEN 'A'
         WHEN e.score >= 80 THEN 'B'
         WHEN e.score >= 70 THEN 'C'
         ELSE 'D'
       END AS letter
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
WHERE e.course_id = 3
ORDER BY e.score DESC;

first_name | score | letter
Fidan | 94 | A
Murad | 81 | B
Rəşad | 77 | C
Elvin | 68 | D

Simple CASE and sorting with CASE

When you compare one column with several specific values, there is a short form: CASE column WHEN value THEN ... END. It is shorthand for checks with =. CASE can also go in ORDER BY: below we show the customers with their currency and move the customers from Azerbaijan to the top of the list.

SELECT name, country,
       CASE country
         WHEN 'Azerbaijan'     THEN 'AZN'
         WHEN 'Türkiye'        THEN 'TRY'
         WHEN 'Russia'         THEN 'RUB'
         WHEN 'United Kingdom' THEN 'GBP'
       END AS currency
FROM customers
ORDER BY CASE WHEN country = 'Azerbaijan' THEN 0 ELSE 1 END, name;

name | country | currency
Anar Mustafayev | Azerbaijan | AZN
Lalə Əhmədova | Azerbaijan | AZN
Zəhra Hüseynli | Azerbaijan | AZN
Emre Yılmaz | Türkiye | TRY
John Carter | United Kingdom | GBP
Mehmet Kaya | Türkiye | TRY
Olga Ivanova | Russia | RUB

COALESCE and NULLIF

COALESCE(a, b, c) returns the first value in the list that is not NULL — a short way of writing CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END. NULLIF(a, b) does the opposite: it returns NULL when a = b, and a otherwise. Its main job is to defuse division by zero: in x / NULLIF(y, 0), when y is zero the division is by NULL and the result is NULL.

SELECT p.name, p.stock,
       COALESCE(SUM(o.quantity), 0) AS sold,
       ROUND(1.0 * COALESCE(SUM(o.quantity), 0) / NULLIF(p.stock, 0), 3) AS sold_per_stock
FROM products AS p
LEFT JOIN orders AS o ON o.product_id = p.id
WHERE p.category = 'Electronics'
GROUP BY p.id, p.name, p.stock
ORDER BY p.id;

name | stock | sold | sold_per_stock
Laptop | 8 | 1 | 0.125
Smartphone | 15 | 2 | 0.133
Headphones | 40 | 3 | 0.075
Monitor | 0 | 0 | NULL

Conditional aggregation and pivoting

Put CASE inside an aggregate and it becomes a very powerful tool. SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts the rows that meet the condition. Write several such columns, and values from the rows turn into columns — like a pivot table in Excel. Below we see the number of students per grade for every city.

SELECT city,
       SUM(CASE WHEN grade = 8  THEN 1 ELSE 0 END) AS g8,
       SUM(CASE WHEN grade = 9  THEN 1 ELSE 0 END) AS g9,
       SUM(CASE WHEN grade = 10 THEN 1 ELSE 0 END) AS g10,
       SUM(CASE WHEN grade = 11 THEN 1 ELSE 0 END) AS g11,
       COUNT(*) AS total
FROM students
GROUP BY city
ORDER BY total DESC, city;

city | g8 | g9 | g10 | g11 | total
Bakı | 1 | 2 | 1 | 0 | 4
Gəncə | 0 | 0 | 1 | 1 | 2
Sumqayıt | 1 | 0 | 0 | 1 | 2
Lənkəran | 1 | 0 | 0 | 0 | 1
Naxçıvan | 0 | 0 | 0 | 1 | 1
Quba | 0 | 0 | 1 | 0 | 1
Şəki | 0 | 1 | 0 | 0 | 1

The same trick gives shares: the average of 0s and 1s is the share of rows that meet the condition. COUNT(CASE WHEN condition THEN 1 END) also works, because without ELSE the value is NULL, and COUNT doesn't count NULLs.

SELECT c.title,
       COUNT(*) AS students,
       COUNT(CASE WHEN e.score >= 90 THEN 1 END) AS excellent,
       ROUND(100.0 * AVG(CASE WHEN e.score >= 90 THEN 1 ELSE 0 END), 1) AS excellent_pct
FROM enrollments AS e
JOIN courses AS c ON c.id = e.course_id
GROUP BY c.id, c.title
ORDER BY excellent_pct DESC, c.title;

title | students | excellent | excellent_pct
Python Basics | 3 | 2 | 66.7
Algebra | 4 | 2 | 50
English B1 | 2 | 1 | 50
World History | 2 | 1 | 50
Geometry | 3 | 1 | 33.3
Mechanics | 4 | 1 | 25
Organic Chemistry | 2 | 0 | 0

SELECT name, stock
       -- add the status column with CASE
FROM products
ORDER BY stock, name;

name | stock | status
Monitor | 0 | out of stock
Laptop | 8 | low
Smartphone | 15 | low
Chess set | 18 | low
Desk lamp | 25 | ok
Headphones | 40 | ok
Backpack | 60 | ok
Water bottle | 120 | ok
Pen set | 300 | ok
Notebook | 500 | ok

SELECT c.country
       -- electronics and other
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN products  AS p ON p.id = o.product_id
GROUP BY c.country
ORDER BY c.country;

country | electronics | other
Azerbaijan | 5 | 33
Russia | 0 | 1
Türkiye | 1 | 5
United Kingdom | 0 | 2