Что такое case в sql

Выражение CASE в SQL: объяснение на примерах

Перевод статьи «SQL Case Statement Tutorial – With When-Then Clause Example Queries».

Данные для примера

Представьте, что вы преподаете литературу в школе. ваши ученики должны написать сочинение.

Вы создали следующую таблицу, чтобы отслеживать, кто из учеников уже сдал сочинение (там же проставляются оценки). Если ученик еще не сдал сочинение, в графе оценок значится NULL.

Как написать выражение CASE в SQL

Вместо этого вы можете использовать выражение CASE и вывоить разные сообщения, основываясь на статусе в submitted_essay.

Базовая структура выражения CASE :

В приведенном выше примере мы выбрали имена учеников, а затем вывели разные сообщения в столбце status, основываясь на значении submitted_essay. Результирующая таблица выглядит так:

Усложняем пример

В этом примере кода мы вывели имена учеников, их оценки, а также комментарии, соответствующие оценкам.

Обратите внимание, что в этом случае предложение ELSE призвано захватить все сочинения с оценками NULL (т. е. еще не сданные сочинения), но в других ситуациях для проверки, является ли значение null, вы могли бы использовать IS NULL.

В таблице представлены результаты этого запроса:

Заключение

Выражения CASE легко понять и изучить. Их применение — это лаконичный способ внести ясность в ваши SQL-запросы.

Источник

Выражение CASE (Transact-SQL)

Оценка списка условий и возвращение одного из нескольких возможных выражений результатов.

Выражение CASE имеет два формата:

простое выражение CASE для определения результата сравнивает выражение с набором простых выражений;

поисковое выражение CASE для определения результата вычисляет набор логических выражений.

Оба формата поддерживают дополнительный аргумент ELSE.

Выражение CASE может использоваться в любой инструкции или предложении, которые допускают допустимые выражения. Например, выражение CASE можно использовать в таких инструкциях, как SELECT, UPDATE, DELETE и SET, а также в таких предложениях, как select_list, IN, WHERE, ORDER BY и HAVING.

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

input_expression
Выражение, полученное при использовании простого формата функции CASE. input_expression — это любое допустимое выражение.

WHEN when_expression
Простое выражение, с которым сравнивается input_expression при использовании простого формата CASE. when_expression — это любое допустимое выражение. Типы данных аргумента input_expression и каждого из выражений when_expression должны быть одинаковыми или неявно приводимыми друг к другу.

THEN result_expression
Выражение, возвращаемое, когда равенство input_expression и when_expression имеет значение TRUE или Boolean_expression имеет значение TRUE. result expression — это любое допустимое выражение.

ELSE else_result_expression
Это выражение, возвращаемое, если ни одна из операций сравнения не дает в результате TRUE. Если этот аргумент опущен и ни одна из операций сравнения не дает в результате TRUE, функция CASE возвращает NULL. else_result_expression — это любое допустимое выражение. Типы данных аргумента else_result_expression и каждого из выражений result_expression должны быть одинаковыми или неявно приводимыми друг к другу.

WHEN Boolean_expression
Логическое выражение, полученное при использовании поискового формата функции CASE. Boolean_expression — это любое допустимое логическое выражение.

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Типы возвращаемых данных

Возвращает тип с наивысшим приоритетом из набора типов в выражении result_expressions и необязательном выражении else_result_expression. Дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL).

Возвращаемые значения

Простое выражение CASE

Простое выражение CASE сравнивает первое выражение с выражением в каждом предложении WHEN. Если эти выражения эквивалентны, то возвращается выражение в предложении THEN.

Допускается только проверка равенства.

В указанном порядке сравнивает значения выражений input_expression и when_expression для каждого предложения WHEN.

Возвращает выражение result_expression, соответствующее первой операции input_expression = when_expression, равной TRUE.

Если ни одна из операций input_expression = when_expression не дает значения TRUE, Компонент SQL Server Database Engine возвращает выражение else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано.

Поисковое выражение CASE

Вычисляет в указанном порядке выражения Boolean_expression для каждого предложения WHEN.

Возвращает выражение result_expression, соответствующее первому выражению Boolean_expression, которое имеет значение TRUE.

Если ни одно выражение Boolean_expression не равно TRUE, Компонент Database Engine возвращает выражение else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано.

Remarks

SQL Server допускает применение в выражениях CASE не более 10 уровней вложенности.

Выражение CASE нельзя использовать для управления потоком выполнения инструкций Transact-SQL, блоков инструкций, определяемых пользователем функций и хранимых процедур. Список методов управления потоком см. в статье Язык управления потоком (Transact-SQL).

Выражение CASE последовательно оценивает свои условия и останавливается, когда находит первое выполнимое условие. В некоторых ситуациях выражение оценивается до того, как выражение CASE получает результаты выражения в качестве входных данных. При оценке этих выражений возможны ошибки. Агрегатные выражения в аргументах WHEN выражения CASE вначале оцениваются, после чего передаются выражению CASE. Например в следующем запросе создается ошибка деления на ноль при вычислении значения агрегата MAX. Это происходит до оценки выражения CASE.

Следует создавать зависимости только от порядка оценки условий WHEN для скалярных выражений (в том числе нескоррелированных вложенных запросов, возвращающих скалярные значения), а не для агрегатных выражений.

Примеры

A. Использование инструкции SELECT с простым выражением CASE

При использовании в инструкции SELECT простое выражение CASE позволяет выполнить только проверку на равенство. Другие проверки не выполняются. В следующем примере выражение CASE используется для изменения способа отображения категорий линейки продуктов с целью сделать их более понятными.

Б. Использование инструкции SELECT с поисковым выражением CASE

При использовании в инструкции SELECT поисковое выражение CASE позволяет заменять значения в результирующем наборе в зависимости от результатов сравнения. В следующем примере отображается список цен в виде текстового комментария, основанного на диапазоне цен для продукта.

В. Использование выражения CASE в предложении ORDER BY

Г. Использование выражения CASE в инструкции UPDATE

В следующем примере выражение CASE используется в инструкции UPDATE, чтобы определить значение, установленное в столбце VacationHours для сотрудников, у которых столбец SalariedFlag имеет значение 0. Если при вычитании 10 часов из VacationHours получается отрицательное значение, VacationHours увеличивается на 40 часов. В противном случае значение VacationHours увеличивается на 20 часов. С помощью предложения OUTPUT отображаются исходная и обновленная продолжительности отпуска.

Д. Использование выражения CASE в инструкции SET

Е. Использование выражения CASE в предложении HAVING

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

Ж. Использование инструкции SELECT с выражением CASE

При использовании в инструкции SELECT выражение CASE позволяет заменять значения в результирующем наборе в зависимости от результатов сравнения. В приведенном ниже примере выражение CASE используется для изменения способа отображения категорий линейки продуктов с целью сделать их более понятными. Если значение отсутствует, выводится текст «Not for sale».

З. Использование выражения CASE в инструкции UPDATE

В следующем примере выражение CASE используется в инструкции UPDATE, чтобы определить значение, установленное в столбце VacationHours для сотрудников, у которых столбец SalariedFlag имеет значение 0. Если при вычитании 10 часов из VacationHours получается отрицательное значение, VacationHours увеличивается на 40 часов. В противном случае значение VacationHours увеличивается на 20 часов.

Источник

Что такое case в sql

Если возможностей этих условных выражений оказывается недостаточно, вероятно, имеет смысл перейти к написанию хранимых процедур на более мощном языке программирования.

9.17.1. CASE

Типы данных всех выражений результатов должны приводиться к одному выходному типу. Подробнее это описано в Разделе 10.5.

Показанный ранее пример можно записать по-другому, используя простую форму CASE :

В выражении CASE вычисляются только те подвыражения, которые необходимы для получения результата. Например, так можно избежать ошибки деления на ноль:

Примечание

9.17.2. COALESCE

Функция COALESCE возвращает первый попавшийся аргумент, отличный от NULL. Если же все аргументы равны NULL, результатом тоже будет NULL. Это часто используется при отображении данных для подстановки некоторого значения по умолчанию вместо значений NULL:

Аргументы должны быть приводимыми к одному общему типу, который и будет типом результата (подробнее об этом говорится в Разделе 10.5).

9.17.3. NULLIF

9.17.4. GREATEST и LEAST

Функции GREATEST и LEAST выбирают наибольшее или наименьшее значение из списка выражений. Все эти выражения должны приводиться к общему типу данных, который станет типом результата (подробнее об этом в Разделе 10.5). Значения NULL в этом списке игнорируются, так что результат выражения будет равен NULL, только если все его аргументы равны NULL.

Заметьте, что функции GREATEST и LEAST не описаны в стандарте SQL, но часто реализуются в СУБД как расширения. В некоторых других СУБД они могут возвращать NULL, когда не все, а любой из аргументов равен NULL.

Источник

Примеры использования выражения CASE в Transact-SQL

В этой статье мы рассмотрим выражение CASE языка Transact-SQL, Вы узнаете, что это за выражение, его синтаксис, а также мы разберем несколько примеров использования выражения CASE.

CASE в Transact-SQL

CASE – это инструкция, которая проверяет список условий и возвращает соответствующий результат. Если говорить в целом о программировании, то CASE – это что-то вроде многократного использования конструкции IF-ELSE, во многих языках есть похожая конструкция SWITCH, так вот CASE, как я уже отметил, делает примерно то же самое.

Выражение CASE можно использовать практически в любой инструкции T-SQL, где есть возможность использовать допустимые выражения, например: SELECT, UPDATE, WHERE, SET и даже в ORDER BY.

CASE имеет два так называемых формата:

Синтаксис CASE в Transact-SQL

Простое выражение CASE

Поисковое выражение CASE

Описание параметров:

CASE возвращает результат первого выражения (THEN result_expression), условие которого выполнилось, т.е. WHEN возвращает TRUE. Таким образом, если CASE содержит несколько эквивалентных условий WHEN, которые будут возвращать TRUE, вернется результат (указанный в THEN) первого выражения.

Тип данных возвращаемого результата выражением CASE, будет соответствовать наиболее приоритетному типу данных из набора типов в выражениях result_expressions и else_result_expression.

Примеры CASE в Transact-SQL

Сейчас давайте рассмотрим несколько примеров использования выражения CASE, но сначала разберем исходные данные, которые я буду использовать в примерах, чтобы было более понятно и наглядно.

Исходные данные для примеров

Допустим, что у нас есть таблица с товарами, она имеет следующую структуру и данные.

Заметка! Если Вы не знаете, что делает вышеуказанная инструкция, рекомендую посмотреть мой видеокурс «T-SQL. Путь программиста от новичка к профессионалу. Уровень 1 – Новичок», который предназначен для начинающих. В нем подробно рассмотрены все базовые конструкции языка SQL, включая все вышеперечисленные.

Пример простого выражения CASE в инструкции SELECT

В этом примере мы проверяем значение столбца ProductId, если оно равняется одному из перечисленных значений в выражении WHEN, то будет выводиться соответствующее значение из выражения THEN. Если нам встретится значение, которого мы не указали, CASE вернет пусто, т.е. значение из ELSE.

Пример поискового выражения CASE в инструкции SELECT

Следующий запрос эквивалентен предыдущему, но в нем используется уже поисковый формат CASE.

Как видим, результат точно такой же.

Давайте немного усложним запрос, добавим в него некий анализ цены (Price). Также я здесь покажу, что в случае, если по факту у нас может выполниться несколько условий, результат будет возвращен первого выражения с TRUE (т.е. условие выполнилось), выражения, идущие после, не обрабатываются.

Как видите, условие WHEN Price = 300 AND ProductId = 1 с виду выполняется, но CASE вернул результат первого выражения, после которого анализ был прекращен, и дело до следующих условий не дошло.

Пример выражения CASE в инструкции SET

Как я уже отмечал, CASE можно использовать во многих инструкциях, например, в SET для установки значений переменных. В следующем примере мы проверяем значение одной переменной, чтобы записать соответствующее текстовое значение в другую переменную.

Примечание! Про основы программирования на языке T-SQL в Microsoft SQL Server можете почитать в материале «Основы программирования на T-SQL».

Источник

Инструкция CASE (многомерные выражения)

Позволяет возвращать указанные значения из нескольких сравнений согласно условию. Существуют два типа инструкции CASE.

Простая инструкция CASE, сравнивающая выражение с набором простых выражений и возвращающая определенные значения.

Инструкция CASE для поиска, вычисляющая набор выражений логического типа и возвращающая определенные значения.

Синтаксис

Аргументы

input_expression
Многомерное выражение, результатом вычисления которого является скалярное значение.

when_true_result_expression
Скалярное значение, которое возвращается, если результатом предложения WHEN является TRUE.

else_result_expression
Скалярное значение, возвращаемое в том случае, если при вычислении ни одно из предложений WHEN не вернуло TRUE.

Boolean_expression
Многомерное выражение, результатом вычисления которого является скалярное значение.

Remarks

Если нет предложения ELSE, а все предложения WHEN дали значение false, тогда результатом будет пустая ячейка.

Простое выражение CASE

MDX вычисляет простое выражение CASE, разрешающее input_expression скалярному значению. Это скалярное значение затем сравнивается с скалярным значением when_expression. Если два скалярных значения совпадают, оператор CASE возвращает значение when_true_expression. Если скалярные значения не совпадают, вычисляется следующее предложение WHEN. Если все предложения WHEN имеют значение false, то возвращается значение else_result_expression из предложения Else (при наличии).

Выражение CASE для поиска

Для более сложных вычислений можно использовать выражение CASE для поиска. Оно позволяет узнать, принадлежит ли значение входного выражения диапазону значений. Многомерное выражение вычисляет предложения WHEN, чтобы они появились в инструкции CASE.

Источник