Я уверен, что вы уже знакомы с основами SQL CASE, поэтому я не буду мучит вас длинным введением. Давайте углубимся в понимание того, что происходит под капотом.
1. SQL CASE не всегда оценивается последовательно
Выражения в SQL CASE по большей части оцениваются последовательно или слева направо. Хотя совсем другое дело, когда они используются с агрегатными функциями. Давайте рассмотрим пример:
-- сначала оценивается агрегатная функция и генерирует ошибку
DECLARE @value INT = 0;
SELECT CASE WHEN @value = 0 THEN 1 ELSE MAX(1/@value) END;
Вышеприведенный код выглядит обычно. Если я спрошу вас, какой результат будет получен, вы, вероятно, ответите 1. Визуальная проверка скажет нам это, поскольку переменная @value установлена в 0. Если @value равна 0, то результат равен 1.
Но не в этом случае. Вот действительный результат, полученный в SQL Server Management Studio:
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.
Но почему?
Когда условное выражение использует агрегатные функции типа MAX() в SQL CASE, они оцениваются в первую очередь. Таким образом, MAX(1/@value) вызывает ошибку деления на нуль, поскольку @value равна 0.
Ситуация становится еще более неприятной, когда скрыта. Я объясню это позже.
2. Простое выражение SQL CASE оценивается многократно
Ну и что?
Хороший вопрос. Действительно, здесь вообще нет никаких проблем, если вы используете литералы или простые выражения. Но если вы используете подзапросы в качестве условного выражения, вы сильно удивитесь.
Прежде, чем проверять пример ниже, лучше восстановить отсюда копию базы данных. Мы будем использовать её в последующих примерах.
Теперь рассмотрим следующий очень простой пример:
SELECT TOP 1 manufacturerID FROM SportsCars
Очень простой, правда? Он возвращает 1 строку с одним столбцом данных. STATISTICS IO показывает минимальное число логических чтений.
Рис.1. Логические чтения таблицы SportsCars до использования запроса в качестве подзапроса в SQL CASE
Замечание для непосвященных. Чем больше логических чтений, тем медленнее запрос. О логических чтениях можно почитать здесь.
План выполнения тоже показывает простой процесс:
Рис.2. План выполнения для запроса к SportsCar до его использования как подзапроса в SQL CASE
Давайте теперь поместим этот запрос в выражение CASE:
-- Использование подзапроса в SQL CASE
DECLARE @manufacturer NVARCHAR(50)
SET @manufacturer = (CASE (SELECT TOP 1 manufacturerID FROM SportsCars)
WHEN 6 THEN 'Alfa Romeo'
WHEN 21 THEN 'Aston Martin'
WHEN 64 THEN 'Ferrari'
WHEN 108 THEN 'McLaren'
ELSE 'Others'
END)
SELECT @manufacturer;
Анализ
Скрестите пальцы, поскольку сейчас логические чтения увеличатся в 4 раза.
Рис.3. Логические чтения после использования подзапроса в SQL CASE
Удивительно! По сравнению всего с двумя логическими чтениями на рис.1 мы получили в 4 раза больше. Таким образом, запрос стал в 4 раза медленнее. Как это могло произойти? Мы видим подзапрос только в одном месте.
Но это не конец истории. Посмотрите план выполнения:
Рис.4. План выполнения после использования простого запроса в качестве выражения подзапроса в SQL CASE
Мы видим 4 экземпляра операторов Top и Index Scan на рис.4. Если каждый Top и Index Scan потребляет 2 логических чтения, это объясняет, почему число логических чтений стало 8 на рис.3. И, поскольку каждый Top и Index Scan имеют 25% стоимости, это подтверждает сказанное.
Но это еще не все. Свойства оператора Compute Scalar показывают, как обрабатывается весь оператор.
Рис.5. Свойства Compute Scalar показывают 4 выражения CASE WHEN
Мы видим 3 выражения CASE WHEN в свойстве Defined Values оператора Compute Scalar. Это выглядит так, как будто простое выражение CASE стало поисковым выражением CASE типа:
DECLARE @manufacturer NVARCHAR(50)
SET @manufacturer = (CASE
WHEN (SELECT TOP 1 manufacturerID FROM SportsCars) = 6 THEN 'Alfa Romeo'
WHEN (SELECT TOP 1 manufacturerID FROM SportsCars) = 21 THEN 'Aston Martin'
WHEN (SELECT TOP 1 manufacturerID FROM SportsCars) = 64 THEN 'Ferrari'
WHEN (SELECT TOP 1 manufacturerID FROM SportsCars) = 108 THEN 'McLaren'
ELSE 'Others'
END)
SELECT @manufacturer;
Хорошее исправление? Давайте посмотрим логические чтения в STATISTICS IO:
Рис.6. Логические чтения после извлечения подзапроса из выражения CASE
Мы видим меньше логических чтений в модифицированном запросе. Извлечение подзапроса и присвоение результата переменной получается значительно лучше. Что насчет плана выполнения? Посмотрите ниже:
Рис.7. План выполнения после извлечения подзапроса из выражения CASE
Оператор Top и Index Scan появляются однажды, а не 4 раза. Замечательно!
На заметку: Не используйте подзапрос в качестве условия в операторе CASE. Если необходимо получить значение, поместите сначала результат подзапроса в переменную. Затем используйте эту переменную в выражении CASE.
Эти три встроенные функции тайно преобразуются в SQL CASE
Есть секрет, и SQL CASE имеет к нему отношение. Если вы не знаете, как ведут себя эти 3 функции, вы не будете знать, что совершаете ошибку, которую мы пытались избежать в пунктах №1 и №2 выше. Вот они:
- IIF
- COALESCE
- CHOOSE
Давайте рассмотрим их по очереди.
IIF
Я использовал Immediate IF, или IIF, в Visual Basic и Visual Basic for Applications. Это является также эквивалентом тернарного оператор в C#: <условие> ? <результат, если истинно> : <результат, если ложно>.
Эта функция принимает условие и возвращает 1 из 2 аргументов в зависимости от результатов условия. И эта функция также имеется в T-SQL.
Но это просто обертка более длинного выражения CASE. Откуда нам это известно? Давайте проверим пример.
SELECT IIF((SELECT Model FROM SportsCars WHERE SportsCarID = 1276) =
'McLaren Senna', 'Yes', 'No');
Результатом этого запроса является ‘No’. Однако проверьте план выполнения, а также свойства Compute Scalar.
Рис.8. IIF оказывается CASE WHEN в плане выполнения
Поскольку IIF является CASE WHEN, как вы думаете, что произойдет, если выполнить что-то подобное этому?
DECLARE @averageCost MONEY = 1000000.00;
DECLARE @noOfPayments TINYINT = 0; -- умышленно вызвать ошибку
SELECT IIF((SELECT Model FROM SportsCars WHERE SportsCarID = 1276) = 'SF90 Spider', 83333.33,MIN(@averageCost / @noOfPayments));
Будет получена ошибка деления на нуль, если @noOfPayments равен нулю. То же самое происходило в первом случае, рассмотренном ранее.
Вы можете спросить, что вызывает эту ошибку, поскольку результатом запроса является TRUE, и должно получиться 83333.33. Опять вернитесь к случаю 1.
Таким образом, если вы столкнулись с такой ошибкой при использовании IIF, виноват SQL CASE.
COALESCE
COALESCE — это также сокращенная форма выражения SQL CASE. Она оценивает список значений и возвращает первое не-NULL значение. Вот пример, который показывает, что подзапрос вычисляется дважды.
SELECT
COALESCE(m.Manufacturer + ' ','') + sc.Model AS Car
FROM SportsCars sc
LEFT JOIN Manufacturers m ON sc.ManufacturerID = m.ManufacturerID
Давайте посмотрим план выполнения и свойство Defined Values оператора Compute Scalar.
Рис.9. COALESCE преобразуется в SQL CASE в плане выполнения
Разумеется SQL CASE. Нигде не упоминается COALESCE в окне Defined Values. Это доказывает тайный секрет этой функции.
Но это не все. Сколько раз вы увидели [Vehicles].[dbo].[Styles].[Style] в окне Defined Values? ДВАЖДЫ! Это согласуется с официальной документацией Microsoft. Представьте, что один из аргументов в COALESCE является подзапросом. Тогда получаем удвоение логических чтений и замедление выполнения.
CHOOSE
Наконец, CHOOSE. Она подобна функции CHOOSE в MS Access. Она возвращает одно значение из списка значений на основе позиции индекса. Она также действует как индекс массива.
Давайте посмотрим, сможем ли мы получить трансформацию в SQL CASE в примере. Проверьте нижеприведенный код:
;WITH McLarenCars AS
(
SELECT
CASE
WHEN sc.Model IN ('Artura','Speedtail','P1/ P1 GTR','P1 LM') THEN '1'
ELSE '2'
END AS [type]
,sc.Model
,s.Style
FROM SportsCars sc
INNER JOIN Styles s ON sc.StyleID = s.StyleID
WHERE sc.ManufacturerID = 108
)
SELECT
Model
,Style
,CHOOSE([Type],'Hybrid','Gasoline') AS [type]
FROM McLarenCars
Это наш пример с CHOOSE. Теперь давайте посмотрим план выполнения и свойство Defined Values в операторе Compute Scalar:
Рис.10. Как видно в плане выполнения CHOOSE преобразуется в SQL CASE
Вы видите ключевое слово CHOOSE в окне Defined Values на рис.10? Как насчет CASE WHEN?
Подобно предыдущим примерам, эта функция CHOOSE есть просто оболочка для более длинного выражения CASE. И поскольку запрос имеет 2 пункта для CHOOSE, ключевые слова CASE WHEN появляются дважды. Смотрите в окне Defined Values красные прямоугольники.
Однако CASE WHEN появлялось более двух раз. Это происходит из-за выражения CASE во внутреннем запросе CTE. Если посмотреть внимательно, эта часть внутреннего запроса также появляется дважды.
На заметку
Теперь, когда все секреты раскрыты, что мы узнали?
- SQL CASE ведет себя иначе, когда используются агрегатные функции. Будьте внимательны при передаче аргументов в агрегатные функции типа MIN, MAX или COUNT.
- Простое выражение CASE будет оцениваться несколько раз. Имейте это в виду и избегайте передачи подзапроса. Хотя это синтаксически корректно, это повредит производительности.
- IIF, CHOOSE и COALESCE имеют грязные секреты. Помните это, передавая значения в эти функции. Это вызовет преобразование к SQL CASE. В зависимости от значений вы можете получить ошибку или ухудшить производительность.
Надеюсь, что различие в поведении SQL CASE было полезным для вас.
- Remove From My Forums
-
Question
-
Hi All,
I am having problems with the following SELECT CASE statement when trying to handle NULL values. In the following select
SELECT
st3.description
, CASE st3.description WHEN NULL THEN ‘I am Null’ ELSE ‘This is else’ END AS Expr2
, ISNULL(st3.description, ‘Null Value’) AS Expr3
FROM structure AS st3
When st3.description is NULL I want EXPR2 to display ‘I AM NULL’ but ‘WHEN NULL’ does not seem to work. Column ‘description’ is of type VARCHAR(50).
This is the results I get are as follows:-
description Expr2 Expr3
NULL This is else Null Value
NULL This is else Null Value
Amber This is else Amber
Amber This is else Amber
How do I pick up NULL values in a ‘case when’ statement.
TIA
Andi
Answers
-
Here is the proper syntax for cheking a NULL value in a CASE statment.
Declare @test int
Select Case When @test IS NULL THEN ‘Value is Null’ ELSE ‘Value is not Null’ END Test
-
Hi Vladimir,
The CASE statement has 2 forms which need to be understood.
Form 1
CASE scalar_expression1
WHEN scalar_expression2 THEN …
WHEN scalar_expression3 THEN …
ELSE …
END
In this form, scalar_expression1 is comapred sequntially using = with each WHEN clause and the first match is executed. However NULL = NULL if false and hence you can’t use this form in your SQL
Form 2
CASE
WHEN boolean_expression1 THEN …
WHEN boolean_expression2 THEN …
ELSE …
END
In this form each boolean_expression is evaluated sequentially and the first to return true is executed. Here you can use the IS operator that is used to test for NULL values. e.g. WHEN column IS NULL THEN.
So in your case you want to use:
SELECT st3.description
, CASE WHEN st3.description IS NULL THEN ‘I am Null’ ELSE ‘I am NOT Null’ END Expr2
, ISNULL(st3.description, ‘Null Value’) AS Expr3
FROM structure AS st3
The main purpose of a SQL CASE expression returns a value based on one or more conditional tests. Use CASE expressions anywhere in a SQL statement expression is allowed. Though truly an expression, some people refer to them as “CASE statements.” This most likely stems from their use in programming languages.
The SQL CASE expression is extremely versatile and used throughout SQLServer queries. In particular it is used in the SELECT column list, GROUP BY, HAVING, and ORDER BY clauses. The CASE expression also standardizes (beautify) data or performs checks to protect against errors, such as divide by zero.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. Getting Started Using SQL Server using my free guide and free Microsoft tools.
Table of contents
- SQL Server CASE Statement Forms
- CASE expression Simple Form
- CASE expression Searched Form
- Comparison of Simple and Searched Forms SQL CASE
- Use CASE to Compare a Range of Values
- Data Transformation Example
- Data Standardization Example
- SQL CASE Prevents SQL Errors!
- CASE in SELECT
- CASE expression in ORDER BY
- CASE expression in GROUP BY
- CASE in WHERE Clause
- CASE with CTE
- Wrap Up
SQL Server CASE Statement Forms
There are two forms for the CASE clause: simple and searched. Both forms return a result based on testing an expression. Though technically expressions, you’ll see many people refer to it as a statement.
The simple SQL CASE statement is used for equality tests. It tests one expression against multiple values, this makes it great for transforming one set of values, such as abbreviations to their corresponding long form.
The searched SQL CASE statement uses a more comprehensive expression evaluation format. It is good when you wish to work with ranges of data, such as salary ranges or ages.
We first start out with the simple form, and then cover searched.
CASE expression Simple Form
The simple form of the CASE expression compares the results of an expression with a series of tests and return a “result” when the “test” returns true.
The general form for a simple form CASE expression is:
CASE expression
WHEN test THEN result
…
ELSE otherResult
END
The ELSE statement is optional in a CASE expression. It returns “otherResult” when no matches are made and ELSE is present. If there is no ELSE in the CASE statement, then it returns NULL.
The ELSE clause is a great way to catch bad or unexpected data values, and return a result other than NULL.
Here are some things to consider when using the simple CASE expression:
- Allows only equality comparisons.
- Evaluates tests are evaluated in the order defined.
- Returns the result corresponding to the first TRUE test.
- If no match is made, case returns NULL unless ELSE is present.
CASE expression Searched Form
The search form of the CASE expression allows for more versatile testing. Use it to evaluate a greater range of tests. In fact, any Boolean expression qualifies as a test.
A searched from CASE expression has this format
CASE
WHEN booleanExpression THEN result
…
ELSE otherResult
END
With the searched form, use WHEN clauses to evaluate Boolean expressions. The result of the first TRUE Boolean expression is returned.
Below is the searched CASE form of the employee gender example from the previous section.
SELECT JobTitle,
CASE
WHEN Gender = 'M' THEN 'Male'
WHEN Gender = 'F' THEN 'Female'
ELSE 'Unknown Value'
END
FROM HumanResources.Employee
We also used this same example for the simple SQL case statement. I did this so you could see the subtle difference. Notice that each WHEN clause now contains the test as a Boolean expression.
Comparison of Simple and Searched Forms SQL CASE
Here are the statements side-by-side:
Simple versus Searched CASE expression
I tend to use the searched CASE expression format in all my SQL. This reason is simple, I only have to remember one format!
Since we’re testing Boolean expressions, the searched CASE statement isn’t limited to just equality tests.
Use CASE to Compare a Range of Values
This makes this form really good for comparing ranges of values. Perhaps the sales manager of Adventure Works wants to organize products by price range. How could this be done with SQL?
Given the following names and ranges provided by the sales manager, we can construct a CASE expression to compare the ListPrice to a range of values and then return the appropriate price range name.
The case statement is placed in the SELECT column list and returns a character value. Here’s the SQL that does the trick:
SELECT Name,
ListPrice,
CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END as PriceRange
FROM Production.Product
When you run this query you’ll see PriceRange listed and displaying values according to the ranges specified in the CASE expression:
Data Transformation Example
There are several reasons to use a CASE statement. The first is to transform data from one set of values to another. For instance, to display an employee’s gender as “Male” or “Female,” when your data is encoded as “M” or “F,” use a CASE expression to test for the single character representation and return its corresponding long form.
The example for this is:
SELECT JobTitle,
CASE Gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
ELSE 'Unknown Value'
END
FROM HumanResources.Employee
Data Standardization Example
Similarly you can use a simple CASE clause to standardize several values into one. Extending our example maps several variations to either Male or Female:
SELECT JobTitle,
CASE Gender
WHEN 'M' THEN 'Male'
WHEN '0' THEN 'Male'
WHEN 'F' THEN 'Female'
WHEN '1' THEN 'Female'
ELSE 'Unknown Value'
END
FROM HumanResources.Employee
You may be wondering if you could just create another table in your database and use that to lookup the values. I would tend to agree that would be the best, but in many situations you won’t have permission to create tables in the database. In this case you’re left to your wits a SELECT statement’s provides.
SQL CASE Prevents SQL Errors!
CASE statements can also be used to help prevent errors. A good example is to test for valid values within expressions such as when you divide numbers.
Consider
SELECT ProductID,
Name,
ProductNumber,
StandardCost,
ListPrice,
StandardCost / ListPrice as CostOfGoodSold
FROM Production.Product
This statement return the message
Divide by zero error encountered.
By using a CASE clause we can ensure we don’t inadvertently divide by zero.
SELECT ProductID,
Name,
ProductNumber,
StandardCost,
ListPrice,
CASE
WHEN ListPrice = 0 Then NULL
ELSE StandardCost / ListPrice
END as CostOfGoodSold
FROM Production.Product
A CASE expression can be used wherever an expression can be used. This means you can use it to return a column value result or even use it in an ORDER BY clause.
CASE in SELECT
In the following section we’ll explore using CASE in the ORDER BY and GROUP BY clauses.
CASE expression in ORDER BY
Continuing on with the sales manager request, suppose she also wants to see the products sorted by price range and then product name. We’ve seen how we can display the price ranges as a column, but how do we sort?
Actually it is pretty easy. Since CASE is an expression, we can use it as once of the values from which order the results. Remember, we aren’t limited to just sorting table columns, we can also sort an expression.
Here is the query to sort by the price range.
SELECT Name,
ListPrice
FROM Production.Product
ORDER BY CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END,
Name
We can then add CASE statement to SELECT list to also display the price range.
SELECT Name,
ListPrice,
CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END as PriceRange
FROM Production.Product
ORDER BY CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END,
Name
As you can see, things start to get complicated. Do you see how the CASE statement is repeated in both the SELECT list and ORDER BY? Fortunately, we can simplify this a bit, but removing the CASE statement from the ORDER BY and replacing it with the SELECT list CASE expression’s alias name PriceRange as so:
SELECT Name,
ListPrice,
CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END as PriceRange
FROM Production.Product
ORDER BY PriceRange, Name
CASE expression in GROUP BY
Now that we’ve given the sales manager a detailed listing she wants to see summary data – doesn’t it ever end? In my experience it doesn’t, so knowing lots of SQL to satiate customer demands is your key to success.
Anyways, the good news is we can use the CASE expression we’ve built to create summary groups. In the following SQL we’re grouping the data by PriceRange. Summary statistics on the minimum, maximum, and average ListPrice are created.
SELECT
CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END as PriceRange,
Min(ListPrice) as MinPrice,
Max(ListPrice) as MaxPrice,
AVG(ListPrice) as AvgPrice,
Count(ListPrice) as NumberOfProducts
FROM Production.Product
GROUP BY CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END
ORDER BY MinPrice
Unlike the ORDER BY clause, we can’t reference the column alias PriceRange in the GROUP BY. The entire CASE expression has to be repeated. Here are the results of our query:
CASE in WHERE Clause
You can also use a CASE in the WHERE clause. This is particularly handy if want one of several values to evaluate to a condition.
Continuing with the Gender example, we can write the following CASE in a WHERE clause to test for female employees:
SELECT JobTitle, Gender
FROM HumanResources.Employee
WHERE CASE Gender
WHEN 'M' THEN 'Male'
WHEN '0' THEN 'Male'
WHEN 'F' THEN 'Female'
WHEN '1' THEN 'Female'
ELSE 'Unknown Value'
END = 'Female'
You could have also solved this problem using BOOLEAN logic, or a IN clause, but as the logic get more complex, you may find it is easier to express it with a CASE, rather than those other techniques. It can make your code easier to read and maintain.
CASE with CTE
Speaking of readability and maintainability, why not just wrap the CASE expression within a Common Table Express (CTE) to avoid “sprinkling” the CASE logic through out your code?
Here we use a CTE to define the CASE result once, then reuse that result in the outer query:
;WITH cteEmployee as
(
SELECT JobTitle, Gender,
CASE Gender
WHEN 'M' THEN 'Male'
WHEN '0' THEN 'Male'
WHEN 'F' THEN 'Female'
WHEN '1' THEN 'Female'
ELSE 'Unknown Value'
END GenderResult
FROM HumanResources.Employee
)
SELECT JobTitle, Gender, GenderResult
FROM cteEmployee
WHERE GenderResult = 'Female'
ORDER BY GenderResult
To make it more clear, check out the following which shows how this is done with a CASE statement. Notice the cteEmployee is used to calculate the GenderResult, which is then reused in the SELECT, WHERE, and ORDER BY clauses!
To learn more about Common Table Expressions, check out Common Table Expressions – The Ultimate Guide.
Wrap Up
As you can see, using CASE expressions adds versatility to your SQL statements. They not only allow you to transform data from one set of values to another, but can also be used to ensure statements don’t return errors.
Out of the two forms, simple and searched, I tend use the search form. The reason is that the simple form is limited to equality tests; whereas, the searched form can do that and more.
Since CASE expressions are expressions and not statements or clauses, they can be used where any other expression is used. That mean you can use throughout the SELECT statement and elsewhere in SQL.
-
Hello All —
I feel like a dope here, but I need some help. I have a case statement which is throwing a conversion error and i’m trying to figure this out. All fields involved in the CASE are all numeric (int), I check them with the ISNUMERIC() function. However, when run the following statement I get the corresponding error. When I cast everything to VARCHAR(), the AGE >= THRESHOLD comparison doesn’t work correctly.
any ideas?
SELECT CASE WHEN PROCSTATUS_NUM = 1 AND AGE >= THRESHOLD THEN 0 ELSE 4 END as Sort
ERROR:
Msg 245, Level 16, State 1, Line 11
Conversion failed when converting the ****** value ‘******’ to data type ******.
-
Steve Collins
SSCommitted
Points: 1895
Maybe the obscured error message contains some clues
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Jonathan AC Roberts
SSCoach
Points: 17793
From the limited information you have provided in your question and the error message I would guess that column PROCSTATUS_NUM is not a numeric data type (maybe it’s character?) and contains a value that cannot be converted to a number (maybe alphabetic or punctuation?).
-
MVDBA (Mike Vessey)
SSC-Insane
Points: 21797
Polymorphist wrote:
When I cast everything to VARCHAR(), the AGE >= THRESHOLD comparison doesn’t work correctly.
you need to give us a bit more — but if you are casting everything to varchar then you will get isssues — for example you would expect 21 to come before 112 — but since it’s a char then 112 comes first
at least give us the value types it is trying to convert from and to (in your error message). -i’m pretty sure that bigint, float, nvarchar etc are not confidential as part of a script
MVDBA
-
Polymorphist
SSCommitted
Points: 1909
Thanks for the replies. I have to apologize, this query is from an OLTP system behind a very large healthcare application, it’s very messy. I essentially got lost inside the code and didn’t realize that there was another field called THRESHOLD which had an IIF string formatted for use inside Excel, all I had to do was remove that.
I’m trying to move this code out of a VBScript file and into SSRS, and though I love the challenge, the code here is….ugh. I’m gonna make it better, though, i’m gonna make it better….
Thanks for the responses 🙂
-
Jason A. Long
SSC-Insane
Points: 23756
ISNUMERIC() doesn’t do what you think it does. I think you’d be surprised by what all SQL Server considers «numeric».
SELECT
cv.char_val,
is_numeric = ISNUMERIC(cv.char_val),
is_int = TRY_CONVERT(INT, cv.char_val),
is_decimal = TRY_CONVERT(DECIMAL(9, 2), cv.char_val),
is_float = TRY_CONVERT(FLOAT, cv.char_val)
FROM
( VALUES ('12345'), ('12345.678'), ('$100.00'), ('12345.54321E54'), ('5E+4'), ('123,456,90.0'), ('123.456.78,12') ) cv (char_val); -
Jeffrey Williams
SSC Guru
Points: 90002
Jason A. Long wrote:
ISNUMERIC() doesn’t do what you think it does. I think you’d be surprised by what all SQL Server considers «numeric».
SELECT
cv.char_val,
is_numeric = ISNUMERIC(cv.char_val),
is_int = TRY_CONVERT(INT, cv.char_val),
is_decimal = TRY_CONVERT(DECIMAL(9, 2), cv.char_val),
is_float = TRY_CONVERT(FLOAT, cv.char_val)
FROM
( VALUES ('12345'), ('12345.678'), ('$100.00'), ('12345.54321E54'), ('5E+4'), ('123,456,90.0'), ('123.456.78,12') ) cv (char_val);You should add the money data type to this — it shows that ISNUMERIC recognizes money formats and will convert those correctly.
-
Jeff Moden
SSC Guru
Points: 1002259
It’ll also say that ‘,,,,,,,,,,,’ is a valid numeric. And the cool part about converting to MONEY during imports (if you don’t need more than 4 decimal places) is that it will drop most of the «white space control characters» such as TAB, Cr, Lf, FF, Vt, and even «hard spaces» from after the number being converted.
-
Jason A. Long
SSC-Insane
Points: 23756
Jeffrey Williams wrote:
You should add the money data type to this — it shows that ISNUMERIC recognizes money formats and will convert those correctly.
I did. The 3rd value has a $…
-
Jeff Moden
SSC Guru
Points: 1002259
Jason A. Long wrote:
Jeffrey Williams wrote:
You should add the money data type to this — it shows that ISNUMERIC recognizes money formats and will convert those correctly.
I did. The 3rd value has a $…
… and commas! 😀
-
MVDBA (Mike Vessey)
SSC-Insane
Points: 21797
be careful with the money data type — Vietnamese Dong tend to overflow after a certain value. (i’m talking building a hospital costs, not buying a chocolate bar)
MVDBA
-
m-scally
SSC Journeyman
Points: 99
You could try using TRY_CAST or TRY_CONVERT in your CASE but this will just hide the problem.
You’re better to deal with the issue once then ignore it and deal with it constantly.
SELECT
CASE
WHEN
ISNULL(TRY_CONVERT(INT, PROCSTATUS_NUM),0) = 1
AND ISNULL(TRY_CONVERT(INT, AGE),0) >= ISNULL(TRY_CONVERT(INT, THRESHOLD),0)
THEN 0
ELSE 4
END AS Sort
Viewing 12 posts — 1 through 11 (of 11 total)
The SQL CASE statement allows you to perform IF-THEN-ELSE functionality within an SQL statement. Learn more about this powerful statement in this article.
This article applies to Oracle, SQL Server, MySQL, and PostgreSQL.
What Does the SQL CASE Statement Do?
The CASE statement allows you to perform an IF-THEN-ELSE check within an SQL statement.
It’s good for displaying a value in the SELECT query based on logic that you have defined. As the data for columns can vary from row to row, using a CASE SQL expression can help make your data more readable and useful to the user or to the application.
It’s quite common if you’re writing complicated queries or doing any kind of ETL work.
The syntax of the SQL CASE expression is:
CASE [expression]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2 ...
WHEN condition_n THEN result_n
ELSE result
END case_nameThe CASE statement can be written in a few ways, so let’s take a look at these parameters.
Parameters of the CASE Statement
The parameters or components of the CASE SQL statement are:
- expression (optional): This is the expression that the CASE statement looks for. If we’re comparing this to an IF statement, this is the check done inside the IF statement (e.g. for IF x > 10, the expression would be “x > 10”
- condtion_1/condition_n (mandatory): These values are a result of the expression parameter mentioned. They are the possible values that expression can evaluate to. Alternatively, they can be an expression on their own, as there are two ways to write an SQL CASE statement (as explained below). They also relate to the IF statement in the IF-THEN-ELSE structure.
- result_1/result_n (mandatory): These values are the value to display if the related condition is matched. They come after the THEN keyword and relate to the THEN part of the IF-THEN-ELSE structure.
- result (optional): This is the value to display if none of the conditions in the CASE statement are true. It is the ELSE part of the IF-THEN-ELSE structure and is not required for the CASE SQL statement to work.
- case_name (optional): This value indicates what the column should be referred to as when displayed on the screen or from within a subquery. It’s also called the column alias.
Simple and Searched CASE Expressions
There are actually two ways to use an SQL CASE statement, which are referred to as a “simple case expression” or a “searched case expression”.
Simple CASE expression
The expression is stated at the beginning, and the possible results are checked in the condition parameters.
For example:
CASE name
WHEN 'John' THEN 'Name is John'
WHEN 'Steve' THEN 'Name is Steve'
ENDSearched CASE expression
The expressions are used within each condition without mentioning it at the start of the CASE statement.
For example:
CASE
WHEN name = 'John' THEN 'Name is John'
WHEN name = 'Steve' THEN 'Name is Steve'
ENDAll data types for the expression and conditions for the Simple expressions, and all of the results for both expression types must be the same or have a numeric data type. If they all are numeric, then the database will determine which argument has the highest numeric precedence, implicitly convert the remaining argument to that data type, and return that datatype. Basically, it means the database will work out which data type to return for this statement if there is a variety of numeric data types (NUMBER, BINARY_FLOAT or BINARY_DOUBLE for example).
In SQL, IF statements in SELECT statements can be done with either of these methods. I’ll demonstrate this using more examples later in this article.
Want to see guides like this for all other Oracle functions? Check out this page here that lists all SQL functions along with links to their guides.
Examples of the CASE Statement
Here are some examples of the SQL CASE statement in SELECT queries. I find that examples are the best way for me to learn about code, even with the explanation above.
Simple CASE Statement
SELECT
first_name, last_name, country,
CASE country
WHEN 'USA' THEN 'North America'
WHEN 'Canada' THEN 'North America'
WHEN 'UK' THEN 'Europe'
WHEN 'France' THEN 'Europe'
ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;The results are:
| FIRST_NAME | LAST_NAME | COUNTRY | CONTINENT |
| Adam | Cooper | USA | North America |
| John | Smith | USA | North America |
| Mark | Allan | UK | Europe |
| Sally | Jones | USA | North America |
| Steve | Brown | Canada | North America |
This example is using the simple case statement structure. Notice how the expression (in this case the “country” field) comes right after the CASE keyword. This means the WHEN expressions are all compared to that field.
This example, like most of the examples here, shows the continent of each customer, based on their country.
Searched Case
SELECT first_name, last_name, country,
CASE
WHEN country = 'USA' THEN 'North America'
WHEN country = 'Canada' THEN 'North America'
WHEN country = 'UK' THEN 'Europe'
WHEN country = 'France' THEN 'Europe'
ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | COUNTRY | CONTINENT |
| Adam | Cooper | USA | North America |
| John | Smith | USA | North America |
| Mark | Allan | UK | Europe |
| Sally | Jones | USA | North America |
| Steve | Brown | Canada | North America |
This example performs the same check as the other examples but uses the searched case method. This means that each expression in the WHEN section is evaluated individually. It gives the same result as the previous example though.
Searched Case with Numbers
SELECT first_name, last_name, employees,
CASE
WHEN employees < 10 THEN 'Small'
WHEN employees >= 10 AND employees <= 50 THEN 'Medium'
WHEN employees >= 50 THEN 'Large'
END SizeOfCompany
FROM customers
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | EMPLOYEES | SIZEOFCOMPANY |
| Adam | Cooper | 55 | Large |
| John | Smith | 4 | Small |
| Mark | Allan | 23 | Medium |
| Sally | Jones | 10 | Medium |
| Steve | Brown | 15 | Medium |
This example performs a searched CASE using a number field, which is the number of employees. Notice how the second WHEN expression has two checks – to see if the number is between 10 and 50.
CASE Statement With IN Clause
SELECT first_name, last_name, country,
CASE
WHEN country IN ('USA', 'Canada') THEN 'North America'
WHEN country IN ('UK', 'France') THEN 'Europe'
ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | COUNTRY | CONTINENT |
| Adam | Cooper | USA | North America |
| John | Smith | USA | North America |
| Mark | Allan | UK | Europe |
| Sally | Jones | USA | North America |
| Steve | Brown | Canada | North America |
This example looks up the continent of the customer again. However, it uses an IN clause, which means the value is checked to see if it is in the IN parameter. It should have the same result, but it’s a bit cleaner and has less code.
Nested CASE Statement in SQL
This example shows a CASE statement within another CASE statement, also known as a “nested case statement” in SQL.
It first checks the country and then checks for a particular customer name to see if it is male or female (given that Sally is the only female here).
Notice how I didn’t give a name to the inner case statement. I didn’t need to – this is not displayed and the name is already specified for the Continent column.
SELECT first_name, last_name, country,
CASE
WHEN country IN ('USA', 'Canada') THEN
(CASE WHEN first_name = 'Sally' THEN 'North America F' ELSE 'North America M' END)
WHEN country IN ('UK', 'France') THEN
(CASE WHEN first_name = 'Sally' THEN 'Europe F' ELSE 'Europe M' END)
ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | COUNTRY | CONTINENT |
| Adam | Cooper | USA | North America M |
| John | Smith | USA | North America M |
| Mark | Allan | UK | Europe M |
| Sally | Jones | USA | North America F |
| Steve | Brown | Canada | North America M |
We can see that the results show different values based on the nested CASE statement.
CASE Statement with Functions
SELECT first_name, last_name, employees,
CASE
WHEN MOD(employees, 2) = 0 THEN 'Even Number of Employees'
WHEN MOD(employees, 2) = 1 THEN 'Odd Number of Employees'
ELSE 'Unknown'
END OddOrEven
FROM customers
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | EMPLOYEES | ODDOREVEN |
| Adam | Cooper | 55 | Odd Number of Employees |
| John | Smith | 4 | Even Number of Employees |
| Mark | Allan | 23 | Odd Number of Employees |
| Sally | Jones | 10 | Even Number of Employees |
| Steve | Brown | 15 | Odd Number of Employees |
This example uses the MOD function to demonstrate how you can use CASE statements with functions. It checks the number of employees and determines if they have an odd or even number of employees.
Multiple Matches
SELECT first_name, last_name, employees,
CASE
WHEN employees < 1 THEN 'No Employees'
WHEN employees < 10 THEN 'Small'
WHEN employees <= 50 THEN 'Medium'
WHEN employees >= 50 THEN 'Large'
END SizeOfCompany
FROM customers
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | EMPLOYEES | SIZEOFCOMPANY |
| Adam | Cooper | 55 | Large |
| John | Smith | 4 | Small |
| Mark | Allan | 23 | Medium |
| Sally | Jones | 10 | Medium |
| Steve | Brown | 15 | Medium |
This example shows what happens if there are records that match with multiple WHEN expressions. For example, some customers may have both <1 employees and <10 employees. What happens here? The CASE statement finds the first matching expression and uses that.
CASE Statement in WHERE Clause
SELECT first_name, last_name, country
FROM customers
WHERE
(CASE
WHEN country IN ('USA', 'Canada') THEN 'North America'
WHEN country IN ('UK', 'France') THEN 'Europe'
ELSE 'Unknown'
END) = 'North America'
ORDER BY first_name, last_name;| FIRST_NAME | LAST_NAME | COUNTRY |
| Adam | Cooper | USA |
| John | Smith | USA |
| Sally | Jones | USA |
| Steve | Brown | Canada |
This example shows how the CASE statement is used in a WHERE clause.
This example shows all customers who live in North America, using the CASE statement to restrict the records.
CASE Statement Frequently Asked Questions
What’s an IF Statement?
In case you’re not sure, an IF statement allows you to do something if a condition is true, and something else if the condition is false.
It’s a common feature of many programming languages.
However, SQL isn’t like other programming languages.
It’s not procedural. It doesn’t make several steps on different variables to get the result you want.
It’s set based. You tell the database everything you want, and it returns a set of results.
So, how can you have an SQL IF statement?
I’ll be writing about how to write the IF statement in SQL.
What If No Match Is Found In A CASE Statement?
If there is no match found in any of the conditions, that’s where the ELSE statement comes in. The value used in the ELSE statement is what is returned if no match is found.
However, this is an optional part of the SQL CASE statement. If there is no result, and there is no ELSE statement, then the value of NULL is returned.
Does The CASE Statement Search All Conditions Or Just Finds The First Match?
A common question on SQL CASE statements is if the database evaluates all of the conditions in the CASE statement, or does it stop after finding the first match?
The answer is that it stops after the first match. It finds the first match, or the first expression that is evaluated to be a match, and does not continue with the rest.
It also performs something called “short-circuit evaluation” for Simple CASE expressions. This might not be a concern to you, but it’s good to know for performance reasons. The database will evaluate the first condition, then compare it to the expression, then evaluate the second condition, then evaluate that to the expression, and so on. It doesn’t evaluate all conditions before comparing the first one to the expression.
How Many Conditions or Arguments Can a CASE Statement Use?
The maximum number of conditions in a CASE statement is 255. This includes:
- The initial expression in a simple CASE statement
- The optional ELSE expression
- The expression for the WHEN condition
- The expression for the THEN result
You can use nested CASE statements so that the return value is a CASE expression. However, if you’re reaching the limit of 255 expressions, I would be looking at the efficiency of the query itself, as most queries should not need 255 expressions.
Can You Use An SQL CASE Statement In WHERE Clause?
Yes, you can use an SQL CASE in a WHERE clause. The examples below will show how this is done.
Can You Use An SQL CASE within CASE?
Yes, you can use a CASE within CASE in SQL. The examples below will show how this is done.
Can I Use DECODE Instead Of CASE?
Oracle has a function called DECODE, which lets you check an expression and return different values.
It looks like this:
DECODE (expression, condition1, result1, condition_n, result_n)However, CASE is recommended for several reasons:
- DECODE is older, and CASE was made as a replacement for DECODE.
- CASE offers more flexibility.
- CASE is easier to read.
In terms of performance, they are both very similar. Experiments have shown that unless you’re using millions of records, you won’t get much of a difference, and any difference will be small.
MySQL has a DECODE function but it’s used for something completely different. SQL Server and PostgreSQL don’t have a DECODE function.
There Is No IIF or IF in Oracle
SQL Server 2012 introduced a statement called IIF, which allows for an IF statement to be written.
However, Oracle does not have this functionality. It’s SQL Server only.
The CASE statement should let you do whatever you need with your conditions.
In Oracle, there is no “IF” statement or keyword specifically in Oracle. If you want to use IF logic, then use the CASE statement.
Procedural Languages Have an IF Statement
The procedural languages for each database do have an IF statement:
- Oracle PL/SQL
- SQL Server T-SQL
- MySQL
- PostgreSQL PGSQL
This statement works just like other languages. You have IF, ELSE, ELSIF and END.
However, that’s when you’re working with PL/SQL.
If you’re writing functions or stored procedures, you could use this IF statement.
If you’re just using standard SQL in your application or database, then you can use the CASE statement.
Conclusion
Hopefully, that explains how the SQL CASE statement is used and answers any questions you had. If you want to know more, just leave a comment below.