The CASE statement in SQL returns a value for the condition specified. It tests a list of conditions and returns one of the multiple possible results. We mostly use a case expression in SQL stored procedures or as a formula for a particular column that optimizes the SQL statements. In this article, we would explore the SQL CASE statement and its various use cases.
The CASE statement is used to implement the logic where you want to set the value of one column depending upon the values in other columns.
The SQL Server CASE Statement consists of at least one pair of WHEN and THEN statements. The WHEN statement specifies the condition to be tested. The THEN statement specifies the action if the WHEN condition returns TRUE.
The ELSE statement is optional and executes when none of the WHEN conditions return true. The CASE statement ends with an END keyword.
In this article, we will take a look at a number of different examples of the CASE statement. But before we do that, we’ll create some dummy data to work with.
Creating Dummy Data
Execute the following script to create the dummy data:
CREATE Database ShowRoom;
GO
USE ShowRoom;
CREATE TABLE Cars
(
id INT,
name VARCHAR(50) NOT NULL,
company VARCHAR(50) NOT NULL,
power INT NOT NULL,
color VARCHAR(50) NOT NULL,
model INT NOT NULL,
condition VARCHAR(50) NOT NULL
);
The script above has created a dummy database called ShowRoom with one Table in it called Cars. The Cars table has seven columns: id, name, company, power, color, model, and condition.
Now let’s insert some dummy data into the Cars table. Execute the following script:
USE ShowRoom
INSERT INTO Cars
VALUES
(1, 'Corrolla', 'Toyota', 1800, 'red', 1995, 'X'),
(2, 'City', 'Honda', 1500 , 'black', 2015, 'X'),
(3, 'C200', 'Mercedez', 2000 , 'white', 1992, 'X'),
(4, 'Vitz', 'Toyota', 1300 , 'blue', 2007, 'X'),
(5, 'Baleno', 'Suzuki', 1500 , 'white', 2012, 'X'),
(6, 'C500', 'Mercedez', 5000 , 'grey', 1994, 'X'),
(7, '800', 'BMW', 8000 , 'blue', 2016, 'X'),
(8, 'Mustang', 'Ford', 5000 , 'red', 1997, 'X'),
(9, '208', 'Peugeot', 5400, 'black', 1999, 'X'),
(10, 'Prius', 'Toyota', 3200 , 'red', 2003, 'X');
Let’s check how our dataset looks, execute the following script:
SELECT * FROM Cars;
The output looks like this:
You can see that the condition column contains an X in each row at the moment. We will set the value of the condition column, depending on the model column, using the CASE statement so that you can see clearly what is going on.
SQL CASE Statement Syntax
The syntax of the CASE statement is pretty straight-forward:
SELECT column1,
column2,
CASE WHEN CONDITION THEN 'Value1'
ELSE 'Value2' END AS columnX
FROM Cars;
The CASE statement has to be included inside the SELECT Statement. It starts with the CASE keyword followed by the WHEN keyword and then the CONDITION.
The condition can be any valid SQL Server expression that returns a boolean value. For instance, the condition can be model > 2000, the THEN clause is used after the CONDITION. If the CONDITION returns true the value that follows the THEN clause is stored in columnX. Else, the value after the ELSE clause will also be stored in columnX. The SQL Server CASE statement ends with the END clause.
CASE Statement Examples
Let’s now see the CASE statement in action.
In a previous section, we created a table named Cars inside the ShowRoom database.
The condition column had the value X for all rows. We will use the SQL Server CASE statement to set the value of the condition column to “New” if the model column has a value greater than 2000, otherwise the value for the condition column will be set to “Old”.
Look at the following script:
SELECT name,
model,
CASE WHEN model > 2000 THEN 'New'
ELSE 'Old' END AS condition
FROM Cars;
The above script displays the name, model and condition columns from the Cars table. The output of the script above looks like this:
You can see that the value of X in the condition column has been replaced by “New” and “Old” depending upon the model of the car.
Multiple Conditions in CASE Statement
You can evaluate multiple conditions in the CASE statement.
Let’s write a SQL Server CASE statement which sets the value of the condition column to “New” if the value in the model column is greater than 2010, to ‘Average’ if the value in the model column is greater than 2000, and to ‘Old’ if the value in the model column is greater than 1990.
Look at the following script:
SELECT name,
model,
CASE WHEN model > 2010 THEN 'New'
WHEN model > 2000 THEN 'Average'
WHEN model > 1990 THEN 'Old'
ELSE 'Old' END AS condition
FROM Cars;
The output of the script above looks like this:
In the script above, we assigned three different values to the condition column depending on the value in the model column.
However, in the above script, the conditions are overlapping as. the model with a value greater than 2010 also has a value greater than 2000 and 1990. A better way to implement multiple conditions is to use logical operators like AND, OR, NOT, etc. Look at the following script:
SELECT name,
model,
CASE WHEN model > 2010 THEN 'New'
WHEN model > 2000 AND model <2010 THEN 'Average' WHEN model > 1990 AND model <2000 THEN 'Old'
ELSE 'Old' END AS condition
FROM Cars;
The output of the script above looks like this:
We can also evaluate multiple conditions from different columns using the SQL Server CASE statement. In the following example, we will assign the value of “New White” to the condition column where the model is greater than 2010 and the color is white. Look at the following script:
SELECT name,
color,
model,
CASE WHEN model > 2010 AND color = 'white' THEN 'New White'
WHEN model > 2010 THEN 'New'
WHEN model > 2000 AND model <2010 THEN 'Average' WHEN model > 1990 AND model <2000 THEN 'Old'
ELSE 'Old' END AS condition
FROM Cars;
The output looks like this:
You can see from row 5 that since the color is white and the model is greater than 2010, value for the condition column has been set to “New White” in the output.
Using GROUP BY with CASE Statement in SQL
The CASE statement can also be used in conjunction with the GROUP BY statement in order to apply aggregate functions.
For example, if we want to count the number of new (model number greater than 2000) and old (model number less than 2000) vehicles, we can use the GROUP BY clause with the CASE statement as follows:
SELECT
CASE WHEN model > 2000 THEN 'New'
ELSE 'Old' END AS condition,
COUNT(1) AS count
FROM Cars
GROUP BY CASE WHEN model > 2000 THEN 'New'
ELSE 'Old' END;
In the script above we use the COUNT aggregate function with the CASE statement.
The SQL Server CASE statement sets the value of the condition column to “New” or “Old”. Inside the GROUP BY clause, we specify that the corresponding count for “New” is incremented by 1, whenever a model value of greater than 2000 is encountered. The Else section means that we increase the count for “Old” by 1 if the value of the model is 2000 or less. The output of the script above looks like this:
Since our dataset has five vehicles with a value for ‘model’ of greater than 2000, you can see a 5 in the count column for “New” vehicles. Similarly, we had 5 old vehicles and hence we can see 5 for the count column of “Old” vehicles.
Similarly, we can GROUP BY more than two values. Look at the following script:
SELECT
CASE WHEN model > 2010 THEN 'New'
WHEN model > 2000 THEN 'Average'
WHEN model > 1990 THEN 'Old'
ELSE 'Old' END AS condition,
COUNT(1) AS count
FROM Cars
GROUP BY CASE WHEN model > 2010 THEN 'New'
WHEN model > 2000 THEN 'Average'
WHEN model > 1990 THEN 'Old'
ELSE 'Old' END;
In the script above, we grouped the data into three categories: “New”, “Average” and “Old”.
The output looks like this:
You can see the count for “New”, “Average” and “Old” condition cars.
