Case when else 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 – это простое сравнение значения (выражения) с набором других значений (выражений);
• Поисковое выражение CASE – в данном случае CASE содержит набор логических выражений, которые вычисляются, чтобы вернуть результат.

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

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

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

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

• input_expression — выражение, которое необходимо проверить в простом формате CASE;
• WHEN when_expression — выражение, с которым сравнивается input_expression, в случае с простым форматом. Тип данных when_expression должен быть такой же, как и у input_expression, или хотя бы неявно преобразовываться;
• THEN result_expression — выражение, которое будет возвращено, если текущее условие выполняется;
• ELSE else_result_expression – дополнительный параметр ELSE, который предназначен для случаев, когда ни одно из перечисленных в CASE условий не выполнилось. Это необязательный параметр. Если ELSE не указано, а условия не выполнились, вернётся NULL;
• WHEN Boolean_expression — логическое выражение, используемое в поисковом формате CASE, которое служит для вычисления результата. Это своего рода проверочное условие и таких условий может быть несколько.

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

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

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

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

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

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

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

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

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

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

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

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

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

Как видите, условие WHEN Price = 300 AND Product >

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

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

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

Оператор CASE может быть использован в одной из двух синтаксических форм записи:

Все предложения WHEN должны иметь одинаковую синтаксическую форму, то есть нельзя смешивать первую и вторую формы. При использовании первой синтаксической формы условие WHEN удовлетворяется, как только значение проверяемого выражения станет равным значению выражения, указанного в предложении WHEN . При использовании второй синтаксической формы условие WHEN удовлетворяется, как только предикат принимает значение TRUE . При удовлетворении условия оператор CASE возвращает значение, указанное в соответствующем предложении THEN . Если ни одно из условий WHEN не выполнилось, то будет использовано значение, указанное в предложении ELSE . При отсутствии ELSE , будет возвращено NULL -значение. Если удовлетворены несколько условий, то будет возвращено значение предложения THEN первого из них, так как остальные просто не будут проверяться.

В приведенном выше примере была применена вторая форма оператора CASE .

Заметим, что для проверки на NULL стандарт предлагает более короткую форму — оператор COALESCE . Он имеет произвольное число параметров и возвращает значение первого из них, отличного от NULL . Для двух параметров оператор COALESCE(A, B) эквивалентен следующему оператору CASE :

Решение рассмотренного выше примера при использовании оператора COALESCE можно переписать следующим образом:

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Оценка списка условий и возвращение одного из нескольких возможных выражений результатов. Evaluates a list of conditions and returns one of multiple possible result expressions.

Выражение CASE имеет два формата: The CASE expression has two formats:

простое выражение CASE для определения результата сравнивает выражение с набором простых выражений; The simple CASE expression compares an expression to a set of simple expressions to determine the result.

поисковое выражение CASE для определения результата вычисляет набор логических выражений. The searched CASE expression evaluates a set of Boolean expressions to determine the result.

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

Выражение CASE может использоваться в любой инструкции или предложении, которые допускают допустимые выражения. CASE can be used in any statement or clause that allows a valid expression. Например, выражение CASE можно использовать в таких инструкциях, как SELECT, UPDATE, DELETE и SET, а также в таких предложениях, как select_list, IN, WHERE, ORDER BY и HAVING. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

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

Синтаксис Syntax

Аргументы Arguments

input_expression input_expression
Выражение, полученное при использовании простого формата функции CASE. Is the expression evaluated when the simple CASE format is used. input_expression — это любое допустимое выражение. input_expression is any valid expression.

WHEN when_expression WHEN when_expression
Простое выражение, с которым сравнивается input_expression при использовании простого формата CASE. Is a simple expression to which input_expression is compared when the simple CASE format is used. when_expression — это любое допустимое выражение. when_expression is any valid expression. Типы данных аргумента input_expression и каждого из выражений when_expression должны быть одинаковыми или неявно приводимыми друг к другу. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

THEN result_expression THEN result_expression
Выражение, возвращаемое, когда равенство input_expression и when_expression имеет значение TRUE или Boolean_expression имеет значение TRUE. Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression — это любое допустимое выражение. result expression is any valid expression.

ELSE else_result_expression ELSE else_result_expression
Это выражение, возвращаемое, если ни одна из операций сравнения не дает в результате TRUE. Is the expression returned if no comparison operation evaluates to TRUE. Если этот аргумент опущен и ни одна из операций сравнения не дает в результате TRUE, функция CASE возвращает NULL. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression — это любое допустимое выражение. else_result_expression is any valid expression. Типы данных аргумента else_result_expression и каждого из выражений result_expression должны быть одинаковыми или неявно приводимыми друг к другу. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

WHEN Boolean_expression WHEN Boolean_expression
Логическое выражение, полученное при использовании поискового формата функции CASE. Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression — это любое допустимое логическое выражение. Boolean_expression is any valid Boolean expression.

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

Возвращает тип с наивысшим приоритетом из набора типов в выражении result_expressions и необязательном выражении else_result_expression. Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. Дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL). For more information, see Data Type Precedence (Transact-SQL).

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

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

Простое выражение CASE сравнивает первое выражение с выражением в каждом предложении WHEN. The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. Если эти выражения эквивалентны, то возвращается выражение в предложении THEN. If these expressions are equivalent, the expression in the THEN clause will be returned.

Допускается только проверка равенства. Allows only an equality check.

В указанном порядке сравнивает значения выражений input_expression и when_expression для каждого предложения WHEN. In the order specified, evaluates input_expression = when_expression for each WHEN clause.

Возвращает выражение result_expression, соответствующее первой операции input_expression = when_expression, равной TRUE. Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.

Если ни одна из операций input_expression = when_expression не дает значения TRUE, Компонент SQL Server Database Engine SQL Server Database Engine возвращает выражение else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано. If no input_expression = when_expression evaluates to TRUE, the Компонент SQL Server Database Engine SQL Server Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

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

Вычисляет в указанном порядке выражения Boolean_expression для каждого предложения WHEN. Evaluates, in the order specified, Boolean_expression for each WHEN clause.

Возвращает выражение result_expression, соответствующее первому выражению Boolean_expression, которое имеет значение TRUE. Returns result_expression of the first Boolean_expression that evaluates to TRUE.

Если ни одно выражение Boolean_expression не равно TRUE, Компонент Database Engine Database Engine возвращает выражение else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано. If no Boolean_expression evaluates to TRUE, the Компонент Database Engine Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Remarks Remarks

SQL Server SQL Server допускает применение в выражениях CASE не более 10 уровней вложенности. allows for only 10 levels of nesting in CASE expressions.

Выражение CASE нельзя использовать для управления потоком выполнения инструкций Transact-SQL, блоков инструкций, определяемых пользователем функций и хранимых процедур. The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. Список методов управления потоком см. в статье Язык управления потоком (Transact-SQL). For a list of control-of-flow methods, see Control-of-Flow Language (Transact-SQL).

Выражение CASE последовательно оценивает свои условия и останавливается, когда находит первое выполнимое условие. The CASE expression evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. В некоторых ситуациях выражение оценивается до того, как выражение CASE получает результаты выражения в качестве входных данных. In some situations, an expression is evaluated before a CASE expression receives the results of the expression as its input. При оценке этих выражений возможны ошибки. Errors in evaluating these expressions are possible. Агрегатные выражения в аргументах WHEN выражения CASE вначале оцениваются, после чего передаются выражению CASE. Aggregate expressions that appear in WHEN arguments to a CASE expression are evaluated first, then provided to the CASE expression. Например в следующем запросе создается ошибка деления на ноль при вычислении значения агрегата MAX. For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. Это происходит до оценки выражения CASE. This occurs prior to evaluating the CASE expression.

Следует создавать зависимости только от порядка оценки условий WHEN для скалярных выражений (в том числе нескоррелированных вложенных запросов, возвращающих скалярные значения), а не для агрегатных выражений. You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions.

Примеры Examples

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

При использовании в инструкции SELECT простое выражение CASE позволяет выполнить только проверку на равенство. Другие проверки не выполняются. Within a SELECT statement, a simple CASE expression allows for only an equality check; no other comparisons are made. В следующем примере выражение CASE используется для изменения способа отображения категорий линейки продуктов с целью сделать их более понятными. The following example uses the CASE expression to change the display of product line categories to make them more understandable.

Б. B. Использование инструкции SELECT с поисковым выражением CASE Using a SELECT statement with a searched CASE expression

При использовании в инструкции SELECT поисковое выражение CASE позволяет заменять значения в результирующем наборе в зависимости от результатов сравнения. Within a SELECT statement, the searched CASE expression allows for values to be replaced in the result set based on comparison values. В следующем примере отображается список цен в виде текстового комментария, основанного на диапазоне цен для продукта. The following example displays the list price as a text comment based on the price range for a product.

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

В следующем примере выражение CASE используется в предложении ORDER BY, чтобы определить порядок сортировки строк на основе значения заданного столбца таблицы. The following examples uses the CASE expression in an ORDER BY clause to determine the sort order of the rows based on a given column value. В первом примере вычисляется значение столбца SalariedFlag таблицы HumanResources.Employee . In the first example, the value in the SalariedFlag column of the HumanResources.Employee table is evaluated. Сотрудники, для которых столбец SalariedFlag имеет значение 1, возвращаются в порядке BusinessEntityID (по убыванию). Employees that have the SalariedFlag set to 1 are returned in order by the BusinessEntityID in descending order. Сотрудники, для которых столбец SalariedFlag имеет значение 0, возвращаются в порядке BusinessEntityID (по возрастанию). Employees that have the SalariedFlag set to 0 are returned in order by the BusinessEntityID in ascending order. Во втором примере результирующий набор упорядочивается по столбцу TerritoryName , если столбец CountryRegionName содержит значение «United States», и по столбцу CountryRegionName в остальных строках. In the second example, the result set is ordered by the column TerritoryName when the column CountryRegionName is equal to ‘United States’ and by CountryRegionName for all other rows.

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

В следующем примере выражение CASE используется в инструкции UPDATE, чтобы определить значение, установленное в столбце VacationHours для сотрудников, у которых столбец SalariedFlag имеет значение 0. The following example uses the CASE expression in an UPDATE statement to determine the value that is set for the column VacationHours for employees with SalariedFlag set to 0. Если при вычитании 10 часов из VacationHours получается отрицательное значение, VacationHours увеличивается на 40 часов. В противном случае значение VacationHours увеличивается на 20 часов. When subtracting 10 hours from VacationHours results in a negative value, VacationHours is increased by 40 hours; otherwise, VacationHours is increased by 20 hours. С помощью предложения OUTPUT отображаются исходная и обновленная продолжительности отпуска. The OUTPUT clause is used to display the before and after vacation values.

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

В следующем примере выражение CASE используется в инструкции SET для функции dbo.GetContactInfo с табличным значением. The following example uses the CASE expression in a SET statement in the table-valued function dbo.GetContactInfo . В базе данных AdventureWorks2012 AdventureWorks2012 все данные, связанные с людьми, хранятся в таблице Person.Person . In the AdventureWorks2012 AdventureWorks2012 database, all data related to people is stored in the Person.Person table. Например, человек может быть сотрудником, представителем поставщика или заказчиком. For example, the person may be an employee, vendor representative, or a customer. Функция возвращает имя и фамилию человека с заданным BusinessEntityID и соответствующий тип контакта для этого пользователя. Выражение CASE в инструкции SET определяет отображаемое значение для столбца ContactType в зависимости от наличия столбца BusinessEntityID в таблицах Employee , Vendor или Customer . The function returns the first and last name of a given BusinessEntityID and the contact type for that person.The CASE expression in the SET statement determines the value to display for the column ContactType based on the existence of the BusinessEntityID column in the Employee , Vendor , or Customer tables.

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

В следующем примере выражение CASE используется в предложении HAVING, чтобы ограничить строки, возвращаемые инструкцией SELECT. The following example uses the CASE expression in a HAVING clause to restrict the rows returned by the SELECT statement. Инструкция возвращает максимальную почасовую ставку для каждой должности в таблице HumanResources.Employee . The statement returns the maximum hourly rate for each job title in the HumanResources.Employee table. Предложение HAVING ограничивает должности, оставляя только те, которые заняты мужчинами с максимальной почасовой ставкой более 40 долларов или женщинами с максимальной почасовой ставкой более 42 долларов. The HAVING clause restricts the titles to those that are held by men with a maximum pay rate greater than 40 dollars or women with a maximum pay rate greater than 42 dollars.

Примеры: Azure Synapse Analytics (хранилище данных SQL) Azure Synapse Analytics (SQL DW) и Параллельное хранилище данных Parallel Data Warehouse Examples: Azure Synapse Analytics (хранилище данных SQL) Azure Synapse Analytics (SQL DW) and Параллельное хранилище данных Parallel Data Warehouse

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

При использовании в инструкции SELECT выражение CASE позволяет заменять значения в результирующем наборе в зависимости от результатов сравнения. Within a SELECT statement, the CASE expression allows for values to be replaced in the result set based on comparison values. В приведенном ниже примере выражение CASE используется для изменения способа отображения категорий линейки продуктов с целью сделать их более понятными. The following example uses the CASE expression to change the display of product line categories to make them more understandable. Если значение отсутствует, выводится текст "Not for sale". When a value does not exist, the text "Not for sale’ is displayed.

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

В следующем примере выражение CASE используется в инструкции UPDATE, чтобы определить значение, установленное в столбце VacationHours для сотрудников, у которых столбец SalariedFlag имеет значение 0. The following example uses the CASE expression in an UPDATE statement to determine the value that is set for the column VacationHours for employees with SalariedFlag set to 0. Если при вычитании 10 часов из VacationHours получается отрицательное значение, VacationHours увеличивается на 40 часов. В противном случае значение VacationHours увеличивается на 20 часов. When subtracting 10 hours from VacationHours results in a negative value, VacationHours is increased by 40 hours; otherwise, VacationHours is increased by 20 hours.