CASE WHEN SQL: What is it and how to use it?

CASE WHEN is one of the most powerful and versatile functions in SQL, allowing you to perform advanced operations on your data tables. This way, it is possible to perform selection, update, insertion and classification operations according to specific rules defined by the user.

However, many people still don’t quite understand how CASE WHEN works or how to apply it to their own applications. In this sense, this function can be used in any SQL database. For example, a MySQL database present in  Copahosting website.

In this article, we will explore what CASE WHEN is. Also, how it works and how to use it to improve your data management.

What is CASE WHEN in SQL and how does it work?

CASE WHEN in SQL is a conditional function that allows you to define specific rules for operations on a data table. Thus, we use it when a specific condition is true. The CASE WHEN is composed of three parts: the “ CASE ” clause, the “ WHEN ” clause and the “ THEN ” clause.

Thus, we use the “ CASE ” clause to start the CASE WHEN statement. Therefore, we apply the “ WHEN ” clause to define the conditions that must be true for the action to be executed. And finally, we use the “ THEN ” clause to specify the action that should be performed when the condition is true.

Syntax

CASE WHEN syntax in SQL is simple and easy to understand. Thus, it is composed of three main parts: the “CASE” clause, the “WHEN” clause and the “THEN” clause.

In this sense, we use the “CASE” clause to start the CASE WHEN statement and we can write it as follows:

CASE

We use the “WHEN” clause to define the conditions that must be true for the action to be performed. Therefore, it is written as follows:

WHEN condition

Where we use the “THEN” clause to specify the action we should perform when the condition is true. So, it is written as follows:

THEN action

The complete CASE WHEN syntax in SQL can be written as follows:

CASE condition
WHEN conditional_values
THEN actions
ELSE default_action
END

Where:

• “condition” refers to the condition that must be true for the action to be executed;
• “conditional_values” are the conditional values ​​that are evaluated to determine the action that is being executed;
• “actions” are the actions that will be executed when the condition is true;
• “default_action” refers to the action that is being executed when the condition is false.

Therefore, we see flexibility and adaptation to the specific needs of each application in the CASE WHEN syntax.

Next we will see how we can apply CASE WHEN to perform select, update, insert and sort operations on a data table. For example, you can use CASE WHEN to select only records that meet a certain condition. Or, to update the value of a column based on another column.

How to use CASE WHEN to perform selection operations on a table?

We can also apply the CASE WHEN function to perform selection operations on a data table. So, we need to define a condition that is true for a given record. Then specify the action that should be performed when this condition is true.

There are some differences between SQL’s CASE WHEN and Python’s SWITCH CASE .

For example, assuming we have a “sales” table with the following columns: id, product, value and date. We can use CASE WHEN to select only records where the value of the “product” column is equal to “computer”. The syntax for this would be as follows:

SELECT *
FROM sales
WHERE CASE WHEN product= 'computer' THEN 1 ELSE 0 END = 1

In this example, we are using the “SELECT” clause to select all columns from the “sales” table. Next, we are using the “WHERE” clause to select only those records where the “product” column value equals “computer”. Thus, we use CASE WHEN to define the condition “product = ‘computer’”, and we use the column “1” to indicate that the condition is true.

Thus, the query above will select all records from the “sales” table in which the value of the “product” column is equal to “computer”.

It is important to note that CASE WHEN we also use to select multiple conditions and to select fields calculated from other columns. The syntax is the same, the only difference is the amount of “WHEN” you want to use.

How to use CASE WHEN to perform update operations on a table?

CASE WHEN can also be applied to perform update operations on a data table. In this sense, we define a condition that is true for a given record. We then specify the action that should be performed when this condition is true.

For example, assuming we have a “customers” table with the following columns: id, name, email and age. We can use CASE WHEN to update the value of the “age” column. Only for records in which the value of the “age” column is less than 18. The syntax for this would be as follows:

UPDATE customers
SET age = CASE WHEN age < 18 THEN 18 ELSE age END
WHERE age < 18

In this example, we are using the “ UPDATE ” clause to update the “age” column of the “customers” table. Next, we are using the “SET” clause to specify that the “age” column should be updated with the value of the “age” column when it is less than 18. CASE WHEN is used to define the “age < 18” condition , and the “age” column is used to specify the value that should be updated.

Therefore, the above query will only update the records in the “customers” table. Only where the value of the “age” column is less than 18. The value of the “age” column will be updated to 18.

It is important to note that CASE WHEN can also be used to update multiple columns. Also to update fields calculated from other columns. 

How to use CASE WHEN SQL to perform insertion operations on a table?

We can apply CASE WHEN to perform insertion operations on a data table. In this sense, we need to define a condition that will be true for a given record. And then, we specify the action that will be executed when this condition is true.

For example, assuming we have a “products” table with the following columns: id, name, price and category. So, we can apply CASE WHEN to insert a new record into the table. Only if the value of the “category” column is equal to “electronic”. The syntax for this would be as follows:

INSERT INTO products (name, price, category)
VALUES ('LCD TV', 1200, CASE WHEN category = 'electronic' THEN 'electronic' ELSE 'other' END)

In this example, we are using the “INSERT INTO” clause to insert a new record into the “products” table. Next, we are using the “VALUES” clause to specify the values ​​of the “name”, “price” and “category” columns. Thus, we use CASE WHEN to define the condition “category = ‘electronic’”. Next, we use the “electronic” column to specify the value that should be inserted in the “category” column.

Thus, the above query will insert a new record into the “products” table. With the values ​​“LCD TV” for the “name” field, “1200” for the “price” field and “electronic” for the “category” field. Only if the category is equal to “electronic”.

How to use CASE WHEN to perform sort operations on a table?

We can also use CASE WHEN to perform sort operations on a data table. To do this, we need to define a condition that is true for a given record. Next, we specify the action to be performed when this condition is true.

For example, assuming we have a “sales” table with the following columns: id, product, value and date. We can use CASE WHEN to sort the records in the table based on the value in the “value” column. The syntax for this would be as follows:

SELECT product, value,
  CASE WHEN value > 100 THEN 'High'
      WHEN value > 50 THEN 'Medium'
      ELSE 'Low' END AS rating
FROM sales

In this example, we are using the “SELECT” clause to select the columns “product”, “value” and a new column called “classification”. Therefore, we use CASE WHEN to define the condition “value > 100” and “value > 50”. And we use the “classification” column to specify the classification of the value.

Thus, the above query will classify the records in the “sales” table as “High” if the value in the “value” column is greater than 100. “Medium” if the value is greater than 50 and “Low” if the value is equal to or less than 50.

Comparison between CASE WHEN and other selection functions in SQL.

The CASE function in SQL allows you to create a new column based on one or more conditions. In SQL, there are several alternatives to the CASE function, including the IIF function, the IF function, and the SWITCH function.

IIF function

The IIF function is similar to the CASE function, but it is easier to read and write. The syntax of the IIF function is as follows:

IIF(condition, result_if_true, result_if_false)

For example, you can create a new column that indicates whether a sale is being made to a loyal customer or not. To do this, you can use the IIF function as follows:

SELECT
  id,
  name,
  IIF(loyalty = 1, 'yes', 'no') as loyalist
FROM customers

IF function

The IF function is similar to the CASE function, but it is simpler and does not support as many conditions. The syntax of the IF function is as follows:

IF(condition, result_if_true, result_if_false)

For example, you can create a new column that indicates whether a sale is being made to a loyal customer or not. To do this, you can use the IF function as follows:

SELECT
  id,
  name,
  IF(loyalty = 1, 'yes', 'no') as loyal
FROM customers

SWITCH function

The SWITCH function is a new function in SQL that allows you to create a new column based on one or more conditions. The syntax of the SWITCH function is as follows:

SWITCH(condition
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE default_default
END

For example, you can create a new column that indicates whether a sale is being made to a loyal customer or not. To do this, you can use the SWITCH function as follows:

SWITCH(loyalty
  WHEN 1 THEN 'yes'
  WHEN 0 THEN 'no'
  ELSE 'unknown'
END as loyal
FROM sales

In summary, there are several alternatives to the CASE WHEN function in SQL. Thus, each of these functions has its own specific characteristics and uses. Choosing the appropriate function depends on what we need to do with the data.

Practical examples of using CASE WHEN SQL

CASE WHEN is a data flow control clause that allows you to create a choice structure in SQL. This way, we use it to perform different actions based on different conditions. Here are some practical examples of how we use CASE WHEN in different business scenarios:

1. Sales Analysis:

A business can use CASE WHEN to analyze the sales of its products. For example, a business might use the following query to determine the best-selling product of the month:

SELECT ProductName, SUM(QuantitySold) as TotalSales
FROM Sales
WHERE Month = MONTH(GETDATE()) AND Year = YEAR(GETDATE())
GROUP BY ProductName
ORDER BY TotalSales DESC

2. Credit rating:

Financial institutions use CASE WHEN to classify their customers’ credit. For example, a financial institution might use the following query to rate a customer’s credit based on their payment history:

SELECT
    CASE
        WHEN PaymentHistory >= 60 THEN 'A'
        WHEN PaymentHistory >= 30 AND PaymentHistory < 60 THEN 'B'
        WHEN PaymentHistory >= 12 AND PaymentHistory < 30 THEN 'C'
        ELSE 'D'
    END as CreditRating,
    CustomerName
FROM Customers

3. Spending analysis:

Businesses can use CASE WHEN to analyze their spending and identify areas for cost reduction. For example, a company might use the following query to determine the highest expenses for the month:

SELECT DepartmentName, SUM(Expenses) as TotalExpenses
FROM Expenses
WHERE Month = MONTH(GETDATE()) AND Year = YEAR(GETDATE())
GROUP BY DepartmentName
ORDER BY TotalExpenses DESC

4. Product Classification:

A business can use CASE WHEN to classify its products based on their characteristics. For example, a business might use the following query to classify its products based on their category:

SELECT
    CASE
        WHEN Category = 'Electronics' THEN 'A'
        WHEN Category = 'Clothing' THEN 'B'
        WHEN Category = 'Home & Garden' THEN 'C'
        ELSE 'D'
    END as ProductCategory,
    ProductName
FROM Products
ORDER BY ProductCategory

In this example, the clause  ORDER BY is used to order the products based on their category. The first category is Sort A, the second is Sort B, and so on.

5. Sales analysis by category:

A business can use CASE WHEN in conjunction with the GROUP BY clause to analyze its sales by category. For example, a business might use the following query to determine total sales for each product category:

SELECT
    CASE
        WHEN Category = 'Electronics' THEN 'A'
        WHEN Category = 'Clothing' THEN 'B'
        WHEN Category = 'Home & Garden' THEN 'C'
        ELSE 'D'
    END as ProductCategory,
    SUM(QuantitySold) as TotalSales
FROM Sales
GROUP BY ProductCategory
ORDER BY TotalSales DESC

In this example, the clause  GROUP BY is used to group sales by product category. The clause  ORDER BY is used to order the results based on total sales in descending order. The clause  CASE WHEN is used to define the product category based on its name.