CASE и условная логика

Электронный дневник рядом с баллом показывает и буквенную оценку: 94 — «A», 81 — «B». Интернет-магазин пишет возле товара «нет в наличии», а директор хочет видеть число учеников по классам и городам в одной таблице. Всё это — условия внутри запроса, и в SQL их записывают выражением CASE. CASE похож на if в других языках программирования, но это не команда, а выражение, возвращающее значение, поэтому его можно писать везде, где можно написать столбец.

Поисковый CASE: CASE WHEN ... THEN ... END

СУБД проверяет условия WHEN сверху вниз и возвращает значение THEN первого истинного из них; остальные уже не смотрит. Если ни одно условие не истинно, возвращается значение ELSE, а если ELSE нет — NULL. Выражение всегда заканчивается словом END, и обычно ему дают имя через 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

Простой CASE и сортировка с CASE

Когда один столбец сравнивают с несколькими конкретными значениями, есть краткая форма: CASE столбец WHEN значение THEN ... END. Это сокращение для проверок через =. CASE можно писать и в ORDER BY: ниже мы выводим покупателей с их валютой и поднимаем покупателей из Азербайджана в начало списка.

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 и NULLIF

COALESCE(a, b, c) возвращает первое значение списка, не равное NULL, — это краткая запись CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END. NULLIF(a, b) делает обратное: возвращает NULL, если a = b, и a в остальных случаях. Его главная задача — обезвредить деление на ноль: в выражении x / NULLIF(y, 0) при нулевом y деление идёт на NULL, и результат равен 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

Условная агрегация и сводные таблицы

Если написать CASE внутри агрегата, он становится очень мощным инструментом. SUM(CASE WHEN условие THEN 1 ELSE 0 END) считает строки, удовлетворяющие условию. Несколько таких столбцов превращают значения из строк в столбцы — как сводная таблица в Excel. Ниже видно число учеников по классам для каждого города.

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

Тем же приёмом считают доли: среднее из нулей и единиц — это доля строк, удовлетворяющих условию. Работает и COUNT(CASE WHEN условие THEN 1 END): без ELSE значение равно NULL, а COUNT не считает NULL.

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