Пошук уроків, статей та іншого контенту
Створюватимете умовні значення в результатах запиту за допомогою конструкції CASE.
CASECASE — це умовна конструкція SQL, яка повертає одне значення, якщо умова істинна, і інше — якщо хибна.
Її можна використовувати в:
SELECT, щоб створювати обчислювані значення;
WHERE, щоб будувати складні умови;
ORDER BY, щоб задавати умовний порядок сортування;
UPDATE, щоб змінювати значення залежно від умов.
У цьому уроці зосередимося на створенні умовних значень у результатах запиту.
Загальний синтаксис:
CASE
WHEN умова_1 THEN результат_1
WHEN умова_2 THEN результат_2
ELSE результат_за_замовчуванням
ENDPostgreSQL перевіряє умови зверху вниз і повертає результат першої істинної умови.
Нехай потрібно показати текстовий статус замовлення залежно від його суми:
SELECT
order_id,
total,
CASE
WHEN total >= 1000 THEN 'велике'
WHEN total >= 500 THEN 'середнє'
ELSE 'маленьке'
END AS order_size
FROM orders;Результат може мати такий вигляд:
order_id | total | order_size
----------+-------+------------
1 | 1200 | велике
2 | 750 | середнє
3 | 200 | маленькеAS order_size задає назву обчислюваного стовпця.
Умови перевіряються послідовно. Після знаходження першої істинної умови PostgreSQL не перевіряє наступні.
SELECT
total,
CASE
WHEN total >= 500 THEN 'від 500'
WHEN total >= 1000 THEN 'від 1000'
ELSE 'менше 500'
END AS category
FROM orders;У цьому прикладі замовлення із сумою 1200 отримає категорію 'від 500', оскільки перша умова вже істинна.
Правильний порядок — від більш специфічних або більших значень до загальніших:
SELECT
total,
CASE
WHEN total >= 1000 THEN 'від 1000'
WHEN total >= 500 THEN 'від 500'
ELSE 'менше 500'
END AS category
FROM orders;ELSEELSE визначає значення, яке повертається, якщо жодна умова не виконується.
SELECT
name,
stock,
CASE
WHEN stock = 0 THEN 'немає в наявності'
WHEN stock < 10 THEN 'залишилося мало'
ELSE 'є в наявності'
END AS availability
FROM products;Якщо ELSE не вказати, PostgreSQL поверне NULL, коли жодна умова не буде істинною:
SELECT
name,
stock,
CASE
WHEN stock = 0 THEN 'немає в наявності'
WHEN stock < 10 THEN 'залишилося мало'
END AS availability
FROM products;Для передбачуваного результату зазвичай варто явно додавати ELSE.
CASEІснує два синтаксичні варіанти CASE.
Перший — повна форма, у якій кожен WHEN містить окрему логічну умову:
CASE
WHEN умова_1 THEN результат_1
WHEN умова_2 THEN результат_2
ELSE результат_за_замовчуванням
ENDНаприклад:
SELECT
name,
price,
CASE
WHEN price < 100 THEN 'дешевий'
WHEN price < 500 THEN 'середній'
WHEN price >= 500 THEN 'дорогий'
ELSE 'невідомий'
END AS price_category
FROM products;Цю форму використовують, коли умови містять порівняння, діапазони або складну логіку.
CASEЯкщо потрібно порівняти одне значення з кількома можливими значеннями, можна використати скорочену форму:
CASE значення
WHEN варіант_1 THEN результат_1
WHEN варіант_2 THEN результат_2
ELSE результат_за_замовчуванням
ENDНаприклад:
SELECT
id,
status,
CASE status
WHEN 'new' THEN 'нове'
WHEN 'paid' THEN 'оплачене'
WHEN 'shipped' THEN 'відправлене'
ELSE 'невідомий статус'
END AS status_label
FROM orders;Цей запит еквівалентний такому:
SELECT
id,
status,
CASE
WHEN status = 'new' THEN 'нове'
WHEN status = 'paid' THEN 'оплачене'
WHEN status = 'shipped' THEN 'відправлене'
ELSE 'невідомий статус'
END AS status_label
FROM orders;Скорочена форма зручна для перевірки конкретних значень. Для діапазонів її використати не можна.
CASE з числовими значеннямиРезультати різних гілок CASE можуть бути числами:
SELECT
name,
price,
CASE
WHEN price >= 1000 THEN price * 0.9
WHEN price >= 500 THEN price * 0.95
ELSE price
END AS discounted_price
FROM products;У цьому прикладі:
товари від 1000 отримують знижку 10%;
товари від 500 до 999.99 — знижку 5%;
дешевші товари залишаються без знижки.
Результати всіх гілок повинні мати сумісні типи. Не варто повертати число в одній гілці, а текст — в іншій:
-- Помилка: результати мають несумісні типи
SELECT
CASE
WHEN price > 100 THEN price
ELSE 'без знижки'
END
FROM products;Краще повертати значення одного типу:
SELECT
CASE
WHEN price > 100 THEN price
ELSE 0
END AS discount_amount
FROM products;Або перетворити всі результати на текст, якщо саме це потрібно:
SELECT
CASE
WHEN price > 100 THEN price::text
ELSE 'без знижки'
END AS discount_info
FROM products;WHENУмова може містити оператори AND, OR і NOT:
SELECT
name,
price,
stock,
CASE
WHEN price > 1000 AND stock > 0 THEN 'дорогий товар у наявності'
WHEN price > 1000 AND stock = 0 THEN 'дорогий товар відсутній'
WHEN stock = 0 THEN 'відсутній'
ELSE 'звичайний товар'
END AS product_state
FROM products;Круглі дужки допомагають явно визначити порядок логічних операцій:
SELECT
name,
CASE
WHEN (price < 100 OR stock = 0) THEN 'потрібна увага'
ELSE 'усе гаразд'
END AS review_status
FROM products;NULLПорівняння з NULL за допомогою = не працює очікуваним чином:
-- Неправильно
CASE
WHEN delivered_at = NULL THEN 'ще не доставлено'
ELSE 'доставлено'
ENDДля перевірки NULL використовуйте IS NULL або IS NOT NULL:
SELECT
id,
CASE
WHEN delivered_at IS NULL THEN 'ще не доставлено'
ELSE 'доставлено'
END AS delivery_status
FROM orders;Значення NULL не є звичайним значенням. Воно означає відсутність або невідомість значення, тому для нього існують окремі оператори перевірки.
CASE і значення за замовчуваннямCASE можна використовувати разом із функцією COALESCE, щоб замінити NULL зрозумілим значенням:
SELECT
name,
CASE
WHEN description IS NULL THEN 'Опис відсутній'
ELSE description
END AS product_description
FROM products;У простих випадках для цього також підходить COALESCE:
SELECT
name,
COALESCE(description, 'Опис відсутній') AS product_description
FROM products;CASE потрібен тоді, коли вибір залежить від умов, а не лише від того, чи є значення NULL.
CASE у ORDER BYCASE може визначати умовний порядок сортування. Наприклад, спочатку показати неоплачені замовлення, а потім решту:
SELECT
id,
status,
created_at
FROM orders
ORDER BY
CASE
WHEN status = 'paid' THEN 1
WHEN status = 'new' THEN 2
WHEN status = 'cancelled' THEN 3
ELSE 4
END,
created_at DESC;Число, яке повертає CASE, використовується як ключ сортування:
оплачені замовлення;
нові замовлення;
скасовані;
інші.
Наведений приклад можна виконати в PostgreSQL без додаткових таблиць. Конструкція VALUES створює тимчасовий набір рядків без створення таблиці.
WITH orders(order_id, customer_name, total, status) AS (
VALUES
(1, 'Олена', 1200.00, 'paid'),
(2, 'Микола', 750.00, 'new'),
(3, 'Ірина', 200.00, 'cancelled'),
(4, 'Андрій', 0.00, 'unknown')
)
SELECT
order_id,
customer_name,
total,
CASE status
WHEN 'paid' THEN 'оплачене'
WHEN 'new' THEN 'нове'
WHEN 'cancelled' THEN 'скасоване'
ELSE 'невідоме'
END AS status_label,
CASE
WHEN total >= 1000 THEN 'велике'
WHEN total >= 500 THEN 'середнє'
WHEN total > 0 THEN 'маленьке'
ELSE 'без суми'
END AS order_size,
CASE
WHEN status = 'paid' AND total >= 1000 THEN total * 0.90
WHEN status = 'paid' THEN total * 0.95
ELSE total
END AS final_total
FROM orders
ORDER BY
CASE
WHEN status = 'paid' THEN 1
WHEN status = 'new' THEN 2
ELSE 3
END,
order_id;У цьому запиті:
перший CASE перетворює технічний статус на зрозумілий підпис;
другий визначає розмір замовлення за сумою;
третій обчислює фінальну суму зі знижкою;
CASE в ORDER BY задає пріоритет статусів.
ENDКожна конструкція CASE повинна завершуватися ключовим словом END:
SELECT
CASE
WHEN total > 100 THEN 'велике'
ELSE 'маленьке'
END AS order_size
FROM orders;Загальна умова може перехопити рядки, які мали відповідати конкретнішій умові:
-- Умова total >= 500 спрацює і для значень від 1000
CASE
WHEN total >= 500 THEN 'середнє'
WHEN total >= 1000 THEN 'велике'
ELSE 'маленьке'
ENDРозміщуйте більш специфічні умови першими:
CASE
WHEN total >= 1000 THEN 'велике'
WHEN total >= 500 THEN 'середнє'
ELSE 'маленьке'
ENDNULL через =-- Неправильно
WHEN value = NULLПотрібно:
WHEN value IS NULLУсі результати THEN і ELSE повинні бути сумісними. Не змішуйте числа й текст без явного приведення типів.
ELSEБез ELSE невраховані випадки повернуть NULL. Якщо це не є навмисною поведінкою, додайте значення за замовчуванням.
CASE створює умовні значення в SQL-запитах.
Повна форма використовує довільні логічні умови.
Скорочена форма порівнює одне значення з конкретними варіантами.
Умови перевіряються зверху вниз, а повертається результат першої істинної умови.
ELSE визначає результат для всіх неврахованих випадків.
Для перевірки NULL використовуйте IS NULL і IS NOT NULL.
Результати всіх гілок CASE повинні мати сумісні типи.
CASE можна застосовувати не лише в SELECT, а й у ORDER BY для умовного сортування.