Выражение CASE – условный оператор языка SQL
Данный оператор позволяет осуществить проверку условий и возвратить в зависимости от выполнения того или иного условия тот или иной результат.
Оператор CASE имеет 2 формы:
В качестве значений здесь могут выступать и выражения.
Все предложения WHEN должны иметь одинаковую синтаксическую форму, то есть нельзя смешивать первую и вторую формы. При использовании второй синтаксической формы условие WHEN удовлетворяется, как только значение проверяемого выражения станет равным значению выражения, указанного в предложении WHEN. При использовании первой синтаксической формы условие WHEN удовлетворяется, как только предикат принимает значение TRUE. При удовлетворении условия оператор CASE возвращает значение, указанное в соответствующем предложении THEN. Если ни одно из условий WHEN не выполнилось, то будет использовано значение, указанное в предложении ELSE. При отсутствии ELSE, будет возвращено NULL - значение. Если удовлетворены несколько условий, то будет возвращено значение предложения THEN первого из них, так как остальные просто не будут проверяться.
Разберем на примере первую форму CASE:
SELECT ID,Name,Salary,
CASE
WHEN Salary>=3000 THEN 'ЗП >= 3000'
WHEN Salary>=2000 THEN '2000 <= ЗП < 3000'
ELSE 'ЗП < 2000'
END SalaryTypeWithELSE,
CASE
WHEN Salary>=3000 THEN 'ЗП >= 3000'
WHEN Salary>=2000 THEN '2000 <= ЗП < 3000'
END SalaryTypeWithoutELSE
FROM Employees
WHEN - условия проверяются последовательно, сверху-вниз. При достижении первого удовлетворяющего условия дальнейшая проверка прерывается и возвращается значение, указанное после слова THEN, относящегося к данному блоку WHEN.
Если ни одно из WHEN-условий не выполняется, то возвращается значение, указанное после слова ELSE (что в данном случае означает «ИНАЧЕ ВЕРНИ …»).
Если ELSE-блок не указан и не выполняется ни одно WHEN-условие, то возвращается NULL.
И в первой, и во второй форме ELSE - блок идет в самом конце конструкции CASE, т.е. после всех WHEN - условий.
Разберем на примере вторую форму CASE:
Допустим, на новый год решили премировать всех сотрудников и попросили вычислить сумму бонусов по следующей схеме:
- Сотрудникам ИТ-отдела выдать по 15% от ЗП;
- Сотрудникам Бухгалтерии по 10% от ЗП;
- Всем остальным по 5% от ЗП.
Используем для данной задачи запрос с выражением CASE:
SELECT ID,Name,Salary,DepartmentID,
-- для наглядности выведем процент в виде строки
CASE DepartmentID -- проверяемое значение
WHEN 2 THEN '10%' -- 10% от ЗП выдать Бухгалтерам
WHEN 3 THEN '15%' -- 15% от ЗП выдать ИТ-шникам
ELSE '5%' -- всем остальным по 5%
END NewYearBonusPercent,
-- построим выражение с использованием CASE, чтобы увидеть сумму бонуса
Salary/100*
CASE DepartmentID
WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам
WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам
ELSE 5 -- всем остальным по 5%
END BonusAmount
FROM Employees
Здесь делается последовательная проверка значения DepartmentID с WHEN - значениями. При достижении первого равенства DepartmentID с WHEN - значением, проверка прерывается и возвращается значение, указанное после слова THEN, относящегося к данному блоку WHEN.
Соответственно, значение блока ELSE возвращается в случае, если DepartmentID не совпал ни с одним WHEN - значением.
Если блок ELSE отсутствует, то в случае несовпадения DepartmentID ни с одним WHEN - значением будет возвращено NULL.
Вторую форму CASE несложно представить при помощи первой формы:
SELECT ID,Name,Salary,DepartmentID,
CASE
WHEN DepartmentID=2 THEN '10%' -- 10% от ЗП выдать Бухгалтерам
WHEN DepartmentID=3 THEN '15%' -- 15% от ЗП выдать ИТ-шникам
ELSE '5%' -- всем остальным по 5%
END NewYearBonusPercent,
-- построим выражение с использованием CASE, чтобы увидеть сумму бонуса
Salary/100*
CASE
WHEN DepartmentID=2 THEN 10 -- 10% от ЗП выдать Бухгалтерам
WHEN DepartmentID=3 THEN 15 -- 15% от ЗП выдать ИТ-шникам
ELSE 5 -- всем остальным по 5%
END BonusAmount
FROM Employees
Так что, вторая форма – это всего лишь упрощенная запись для тех случаев, когда нам нужно сделать сравнение на равенство, одного и того же проверяемого значения с каждым WHEN - значением/выражением.
Примечание. Первая и вторая форма CASE входят в стандарт языка SQL, поэтому скорее всего они должны быть применимы во многих СУБД.
С MS SQL версии 2012 появилась упрощенная форма записи IIF. Она может использоваться для упрощенной записи конструкции CASE, в том случае если возвращаются только 2 значения. Конструкция IIF имеет следующий вид:
IIF(условие, true_значение, false_значение)
Т.е. по сути это обертка для следующей CASE конструкции:
CASE WHEN условие THEN true_значение ELSE false_значение END
Посмотрим на примере:
SELECT ID,Name,Salary,
IIF(Salary>=2500,'ЗП >= 2500','ЗП < 2500') DemoIIF,
CASE WHEN Salary>=2500 THEN 'ЗП >= 2500' ELSE 'ЗП < 2500' END DemoCASE
FROM Employees
Конструкции CASE, IIF могут быть вложенными друг в друга. Рассмотрим абстрактный пример:
SELECT ID,Name,Salary,
CASE
WHEN DepartmentID IN(1,2) THEN 'A'
WHEN DepartmentID=3 THEN
CASE PositionID -- вложенный CASE
WHEN 3 THEN 'B-1'
WHEN 4 THEN 'B-2'
END
ELSE 'C'
END Demo1,
IIF(DepartmentID IN(1,2),'A',
IIF(DepartmentID=3,CASE PositionID WHEN 3 THEN 'B-1' WHEN 4 THEN 'B-2' END,'C')) Demo2
FROM Employees
Так как конструкция CASE и IIF представляют из себя выражение, которые возвращают результат, то мы можем использовать их не только в блоке SELECT, но и в остальных блоках, допускающих использование выражений, например, в блоках WHERE или ORDER BY.
Для примера, пускай перед нами поставили задачу – создать список на выдачу ЗП на руки, следующим образом:
- Первым делом ЗП должны получить сотрудники у кого оклад меньше 2500
- Те сотрудники у кого оклад больше или равен 2500, получают ЗП во вторую очередь
- Внутри этих двух групп нужно упорядочить строки по ФИО (поле Name)
Попробуем решить эту задачу при помощи добавления CASE - выражение в блок ORDER BY:
SELECT ID,Name,Salary
FROM Employees
ORDER BY
CASE WHEN Salary >= 2500 THEN 1 ELSE 0 END, -- выдать ЗП сначала тем у кого она ниже 2500
Name -- дальше упорядочить список в порядке ФИО
Как видим, Иванов и Сидоров уйдут с работы последними.
И абстрактный пример использования CASE в блоке WHERE:
SELECT ID,Name,Salary
FROM Employees
WHERE CASE WHEN Salary >= 2500 THEN 1 ELSE 0 END=1 -- все записи у которых выражение равно 1
Можете попытаться самостоятельно переделать 2 последних примера с функцией IIF.
Если статья была Вам полезна, ставьте пальцы вверх и подписывайтесь. Оставляйте свои пожелания и вопросы в комментариях, с удовольствием отвечу.