SQL CASE provides the author of the query with the ability to perform conditional logic in their SQL queries for SELECT, INSERT, UPDATE, DELETE. It is also possible to use it with SET, IN, HAVING, ORDER BY and WHERE.
It comes in two formats:
- simple case
- search case
Simple SQL CASE
The simple CASE expression compares an expression to a set of simple expressions to determine the result.
What that means in practice is that you can test a value such as “Labrador” and output a value such as “Dog”.
Let’s look at an example. Here is a table script and some data I added.
Table creation script
CREATE TABLE [dbo].[Breeds]( [ID] [int] IDENTITY(1,1) NOT NULL, [Breed] [varchar](50) NOT NULL, [Value] [int] NOT NULL, CONSTRAINT [PK_Breeds] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
The data added is here:
We want to query this data and produce a new column that contains the type of animal as it was not supplied in the original data. Here is the code to do that:
SELECT ID, Breed, Value,
CASE
WHEN Breed = 'Labrador' THEN 'Dog'
WHEN Breed = 'King Charles Spaniel' THEN 'Dog'
WHEN Breed = 'Golden Retriever' THEN 'Dog'
WHEN Breed = 'Yorkshire Terrier' THEN 'Dog'
WHEN Breed = 'French Bulldog' THEN 'Dog'
WHEN Breed = 'Siamese' THEN 'Cat'
WHEN Breed = 'Persian' THEN 'Cat'
WHEN Breed = 'Bengal' THEN 'Cat'
WHEN Breed = 'Ragdoll' THEN 'Cat'
WHEN Breed = 'American Shorthair' THEN 'Cat'
END AS Animal
FROM Breeds;
Which produces:
Having looked at the data and knowing that it only contains two types of animal, the SQL CASE could also be written in a more simple way using an ELSE:
SELECT ID, Breed, Value,
CASE
WHEN Breed = 'Labrador' THEN 'Dog'
WHEN Breed = 'King Charles Spaniel' THEN 'Dog'
WHEN Breed = 'Golden Retriever' THEN 'Dog'
WHEN Breed = 'Yorkshire Terrier' THEN 'Dog'
WHEN Breed = 'French Bulldog' THEN 'Dog'
ELSE 'Cat'
END AS Animal
FROM Breeds;
Another way to write this would be to use IN:
SELECT ID, Breed, Value,
CASE
WHEN Breed IN ('Labrador', 'King Charles Spaniel','Golden Retriever','Yorkshire Terrier','French Bulldog') THEN 'Dog'
ELSE 'Cat'
END AS Animal
FROM Breeds;
Search SQL CASE
The searched CASE expression evaluates a set of Boolean expressions to determine the result.
What that means in practice is that you can make a test such as <= 10 and output a value such as “£0 – 10”.
With the sample data we have been working with, this format can be used with it.
SELECT ID, Breed, Value
CASE
WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
END AS Animal
FROM Breeds;
The test against the ID column produces a Boolean result and this enables us to produce the same result with less code. This Boolean matching can be really useful when dealing with numeric type data types as it allows rows to be grouped easily based on the numeric value.
For example:
SELECT ID, Breed, Value,
CASE
WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
END AS Animal,
CASE
WHEN Value < 400 THEN '£0 - £399'
WHEN Value < 700 THEN '£400 - £699'
ELSE '£700 +'
END AS [Price Bracket]
FROM Breeds
ORDER BY Value;
This produces:
Can you spot the bug in the SQL code? What if for some reason another record were added with a value of less than zero. This record would appear in the £0 – £399 price bracket.
So you could handle this in a couple of ways:
- use between
- use nested case
Between example:
SELECT ID, Breed, Value, CASE WHEN ID <= 5 THEN 'Dog' ELSE 'Cat' END AS Animal, CASE WHEN Value BETWEEN 0 AND 399 THEN '£0 - £399' WHEN Value BETWEEN 400 AND 699 THEN '£400 - £699' WHEN Value > 699 THEN '£700 + ' END AS [Price Bracket] FROM Breeds ORDER BY Value;
Nested case example:
SELECT
ID, Breed, Value, CASE WHEN ID <= 5 THEN 'Dog' ELSE 'Cat' END AS Animal,
CASE WHEN Value > 0 THEN
CASE
WHEN Value < 400 THEN '£0 - £399'
WHEN Value < = 700 THEN '£400 - £699'
ELSE '£700 +' END
END AS [Price Bracket]
FROM Breeds
ORDER BY Value;
Using SQL CASE with ORDER BY
It is possible to use CASE with ORDER BY to order results based on if then else logic. Extending our last query to have an ORDER BY included, the criteria here is to ensure that the ‘Persian’ breed is the first row.
SELECT ID, Breed,
CASE
WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
END AS Animal
FROM Breeds
ORDER BY CASE WHEN Breed = 'Persian' THEN 1 ELSE 2 END;
This produces:
Using SQL CASE in an UPDATE statement
If you remember, the data was supplied without an ‘Animal’ column and we’re having to calculate this in the SQL. It would be better to create a new table to contain the animal description and create a relationship between our Breeds table and the new Animals table.
Here is our new table:
CREATE TABLE [dbo].[Animals]( [ID] [int] IDENTITY(1,1) NOT NULL, [Animal] [varchar](50) NOT NULL, CONSTRAINT [PK_Animals] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
First we need to populate it with the animal values. We know we can do this with the help of our earlier use of CASE
WITH cte_Breeds AS ( SELECT ID, Breed, Value, CASE WHEN ID <= 5 THEN 'Dog' ELSE 'Cat' END AS Animal, CASE WHEN Value BETWEEN 0 AND 399 THEN '£0 - £399' WHEN Value BETWEEN 400 AND 699 THEN '£400 - £699' WHEN Value > 699 THEN '£700 + ' END AS [Price Bracket] FROM Breeds ) INSERT INTO Animals SELECT DISTINCT Animal FROM cte_Breeds;
This is our data:
We now need to add a new column to the Breeds table to house the ID from the Animals table to complete the relationship.
ALTER TABLE Breeds ADD AnimalID INT NULL, FOREIGN KEY(AnimalID) REFERENCES Animals(ID);
Now we will run an UPDATE on our Breeds table using CASE
WITH cte_Breeds AS ( SELECT ID, Breed, Value, CASE WHEN ID <= 5 THEN 'Dog' ELSE 'Cat' END AS Animal, CASE WHEN Value BETWEEN 0 AND 399 THEN '£0 - £399' WHEN Value BETWEEN 400 AND 699 THEN '£400 - £699' WHEN Value > 699 THEN '£700 + ' END AS [Price Bracket] FROM Breeds ) UPDATE Br SET AnimalID = CASE WHEN cte.Animal = 'Cat' THEN 1 ELSE 2 END FROM cte_Breeds cte JOIN Breeds Br ON cte.ID = Br.ID;
So this has worked successfully as we now have a relationship between the two tables:
For the purposes of this example, the UPDATE SET CASE is hard coded. This would be a problem if there were many more breeds to update with different animal types and so in that case it would be more efficient not to use CASE. Instead one might do this:
WITH cte_Breeds AS ( SELECT ID, Breed, Value, CASE WHEN ID <= 5 THEN 'Dog' ELSE 'Cat' END AS Animal, CASE WHEN Value BETWEEN 0 AND 399 THEN '£0 - £399' WHEN Value BETWEEN 400 AND 699 THEN '£400 - £699' WHEN Value > 699 THEN '£700 + ' END AS [Price Bracket] FROM Breeds ) UPDATE Br SET AnimalID = An.ID FROM Animals An JOIN cte_Breeds cte ON cte.Animal = An.Animal JOIN Breeds Br ON cte.ID = Br.ID;
I hope you found this post useful. Check out the official documentation with more examples about SQL CASE here – CASE (Transact – SQL)
Leave a Reply