SQL - CASE

---

.

The SQL CASE statement is a conditional statement that helps us to make decisions based on certain conditions. It evaluates the set of conditions and returns the respective when a condition is satisfied.

The CASE statement works like a simplified IF-THEN-ELSE statement and allows for multiple conditions to be tested.

This starts with the keyword CASE followed by multiple conditionals statements. Each conditional statement consists of at least one pair of WHEN and THEN statements. Where WHEN specifies conditional statements and THEN specifies the actions to be taken.

It is often used to create a new column with values based on the value of an existing column.

Let us look at a simple scenario to understand this statement. Suppose we are analyzing the data; we often wish to define rules based on certain conditions.

For e.g. when the credit limit of a customer is above ‘10,000’, then the customer will be recognized as a ‘High value customer’; when the credit limit is above ‘5000’, then the customer will be recognized as a ‘Mid value customer’; otherwise the customer will be recognized as the ‘Low value customer’ as shown in the table below −

Syntax

Following is the syntax of SQL CASE statement −

CASE
   WHEN condition1 THEN statement1,
   WHEN condition2 THEN statement2,
   WHEN condition THEN statementN
   ELSE result
END;

Where, condition1, condition2, etc. are the conditional statements and statement1, statement2, etc.. are the actions to be taken when the condition is true.

Once the condition is met, the CASE statement will stop verifying further and it will return the result.

• If none of the conditions are met (TRUE), then it returns the value mentioned in the ELSE clause.

• It returns NULL if the ELSE part is not mentioned and also none of the conditions are TRUE.

Example

Assume we have created a table named CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
); 

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

The table will be created as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

In the following query, we are using multiple WHEN and THEN conditions to the CASE statement along with the ELSE clause.

If the age of the customer is greater than 30, it returns Gen X otherwise moves to the further WHEN and THEN conditions. If none of the conditions is matched with the CUSTOMERS table, CASE returns the 'Gen Alpha’ value as mentioned in the ELSE part in the query as shown below −

SELECT NAME, AGE,
CASE 
WHEN AGE > 30 THEN 'Gen X'
WHEN AGE > 25 THEN 'Gen Y'
WHEN AGE > 22 THEN 'Gen Z'
ELSE 'Gen Alpha' 
END AS Generation
FROM CUSTOMERS

Output

The output produced is as follows −

+----------+-----+------------+
| NAME     | AGE | Generation |
+----------+-----+------------+
| Ramesh   |  32 | Gen X      |
| Khilan   |  25 | Gen Z      |
| kaushik  |  23 | Gen Z      |
| Chaitali |  25 | Gen Z      |
| Hardik   |  27 | Gen Y      |
| Komal    |  22 | Gen Alpha  |
| Muffy    |  24 | Gen Z      |
+----------+-----+------------+

Example

Let's take a look at another query where we want to provide a 25% increment to each customer if the amount is less than 4500 from the CUSTOMERS table previously created −

SELECT *,
CASE WHEN SALARY < 4500 THEN (SALARY + SALARY * 25/100)
END AS INCREMENT
FROM CUSTOMERS;

Output

Here, the SQL command checks if the salary is less than 4500. If this condition is satisfied, a new column ‘INCREMENT’ will contain the values that is equal to salary with 25% of increment.

Since the ELSE part is not mentioned in the above query and none of the conditions are true for few customers, NULL is returned which shows that they didn’t get any increment as shown below −

+----+----------+-----+-----------+----------+-------------+
| ID | NAME     | AGE | ADDRESS   | SALARY   | INCREMENT   |
+----+----------+-----+-----------+----------+-------------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 2500.000000 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 1875.000000 |
|  3 | kaushik  |  23 | Kota      |  2000.00 | 2500.000000 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |        NULL |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |        NULL |
|  6 | Komal    |  22 | MP        |  4500.00 |        NULL |
|  7 | Muffy    |  24 | Indore    | 10000.00 |        NULL |
+----+----------+-----+-----------+----------+-------------+

CASE statement with ORDER BY clause

We can use CASE statement with ORDER BY clause. The ORDER BY clause in SQL sorts the result in ascending (default) or descending order.

Example

In this query, the CASE statement is used to sort the results based on either the ‘NAME’ column or the ‘ADDRESS’ column, depending on the value of the ‘NAME’ column. If the ‘NAME’ column starts with 'K', the results are sorted by the ‘NAME’ column; otherwise, the results are sorted by the ‘ADDRESS’ column −

SELECT * FROM CUSTOMERS
ORDER BY
(CASE
    WHEN NAME LIKE 'k%' THEN NAME
    ELSE ADDRESS
END);

Output

The result obtained by executing the above query is as shown below −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
+----+----------+-----+-----------+----------+

CASE statement with GROUP BY clause

We can also use the CASE statement with GROUP BY clause. The GROUP BY clause in SQL groups the rows that have same values within one or more columns where an aggregate function is applied to produce summaries.

Example

In the following query we are trying to group the customers based on their salaries and calculate the sum of the salary for a specified range of customer data.

If the value in ‘SALARY is less than or equal to 4000, the data will be grouped as ‘Lowest paid’. If the value is greater than 4000 and less than or equal to 6500, it will be grouped as ‘Average paid’. All other values will be grouped as ‘Highest paid’. The SUM function is used to calculate the total of the SALARY for each group −

SELECT 
   CASE 
      WHEN SALARY <= 4000 THEN 'Lowest paid'
      WHEN SALARY > 4000 AND SALARY <= 6500 THEN 'Average paid'
   ELSE 'Highest paid' 
      END AS SALARY_STATUS,
   SUM(SALARY) AS Total
   FROM CUSTOMERS
   GROUP BY 
   CASE 
      WHEN SALARY <= 4000 THEN 'Lowest paid'
      WHEN SALARY > 4000 AND SALARY <= 6500 THEN 'Average paid'
   ELSE 'Highest paid'
END

Output

Following is the output of the above query −

+---------------+----------+
| SALARY_STATUS | Total    |
+---------------+----------+
| Lowest paid   |  5500.00 |
| Average paid  | 11000.00 |
| Highest paid  | 18500.00 |
+---------------+----------+

CASE statement with WHERE clause

We can use the CASE statement with the WHERE clause as well. The WHERE clause is used to filter the rows in a table based on a specified condition.

Example

In the following query, the CASE statement is used to return the different designations of the customers based on their age. The WHERE clause is used to filter the rows based on the salary of the customers −

SELECT NAME, ADDRESS, 
   CASE 
      WHEN AGE < 25 THEN 'Intern'
      WHEN AGE >= 25 and AGE <= 27 THEN 'Associate Engineer'
      ELSE 'Senior Developer'
   END as Designation
FROM CUSTOMERS
WHERE SALARY >= 2000

Output

Output of the above query is as follows −

+----------+-----------+--------------------+
| NAME     | ADDRESS   | Designation        |
+----------+-----------+--------------------+
| Ramesh   | Ahmedabad | Senior Developer   |
| kaushik  | Kota      | Intern             |
| Chaitali | Mumbai    | Associate Engineer |
| Hardik   | Bhopal    | Associate Engineer |
| Komal    | MP        | Intern             |
| Muffy    | Indore    | Intern             |
+----------+-----------+--------------------+

CASE statement with UPDATE statement

We can use CASE statement within the UPDATE statement to perform conditional updates on data in a table.

Example

In the following query we are trying to update the salary of all the customers based on their age.

If the age of the customer is equal to ’25’, their salary will be updated to ‘17000’. If the age is equal to ‘32’, it will be updated to ‘25000’. For the customers with other ages, salaries will be updated to ‘12000’ −

UPDATE CUSTOMERS
SET SALARY= 
CASE AGE
WHEN 25 THEN 17000
WHEN 32 THEN 25000
ELSE 12000
END;

Output

We get the following result. We can observe that the changes have been done in 7 rows −

(7 rows affected)

Verification

We can rectify the changes done in the CUSTOMERS table using the below query −

Select * from customers;

The table is displayed as follows −

+----+----------+-----+-----------+-----------+
| ID | NAME     | AGE | ADDRESS   | SALARY    |
+----+----------+-----+-----------+-----------+
|  1 | Ramesh   |  32 | Ahmedabad |  25000.00 |
|  2 | Khilan   |  25 | Delhi     |  17000.00 |
|  3 | kaushik  |  23 | Kota      |  12000.00 |
|  4 | Chaitali |  25 | Mumbai    |  17000.00 |
|  5 | Hardik   |  27 | Bhopal    |  12000.00 |
|  6 | Komal    |  22 | MP        |  12000.00 |
|  7 | Muffy    |  24 | Indore    |  12000.00 |
+----+----------+-----+-----------+-----------+

As we can see in the above table, the SALARY of all the customers has been updated corresponding to their age.

CASE statement with INSERT statement

We can also insert the data into SQL tables with the help of CASE statement. In SQL, we need to specify variables to store column values on the basis of which we need to insert the data in CASE statement.

Example

Here, we have declared the variable ‘AGE’ with the value ‘28’ to store the age of the customer. Then the ‘AGE’ variable acts as the criteria for the salary of the customer. If the age of the customer is greater than or equal to 25, then the salary will be 23000; otherwise the salary will be 14000 −

Declare @AGE INT
Set @AGE = 28
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (10, 'Viren', 28, 'Varanasi', 
CASE 
   WHEN @AGE >= 25 THEN 23000
   ELSE 14000
END);

Output

We get the following result. We can observe that the change has been done in 1 row −

(1 row affected)

Verification

We can rectify the changes done in the CUSTOMERS table using the below query −

Select * from customers;

The table is displayed as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
| 10 | Viren    |  28 | Varanasi  | 23000.00 |
+----+----------+-----+-----------+----------+