How to use SQL CASE Expression?

SQL CASE expression is a powerful feature that enables you to conditionally evaluate an expression and return different results depending on the result of the evaluation. It provides a way to write a more flexible and complex query by allowing you to create different cases or scenarios in which different expressions or conditions are met.

Why and When to Use SQL CASE Expression?

SQL CASE expression is useful in situations where you want to apply conditional logic to an expression in your SQL query.

It allows you to compare one or more values to a set of conditions and return a result depending on the match.

You can use the SQL CASE expression to perform the following:

Transform data

You can use the SQL CASE expression to transform data by applying different expressions to different conditions. This can be useful when you want to convert one value to another based on certain conditions.

Create custom grouping

You can use the SQL CASE expression to create custom groups based on certain conditions. For example, you can group products by their category or price range.

Calculate derived values

You can use the SQL CASE expression to calculate derived values based on certain conditions. For example, you can calculate discounts based on the order amount.

How to Use SQL CASE Expression?

SQL CASE expression has two forms: simple and searched.

The simple CASE expression has the following syntax:

CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE default_result
END

The searched CASE expression has the following syntax:

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END

The “expression” in the simple CASE expression represents the expression to be evaluated, and the “value1”, “value2”, etc. represent the values that are being compared to the expression. The “result1”, “result2”, etc. are the values that are returned when the corresponding value is matched. The “default_result” is the value that is returned when none of the values are matched.

The “condition1”, “condition2”, etc. in the searched CASE expression represent the conditions to be evaluated, and the “result1”, “result2”, etc. are the values that are returned when the corresponding condition is matched. The “default_result” is the value that is returned when none of the conditions are matched.

SQL CASE Expression Examples:

Here are some examples of how to use SQL CASE expression:

Simple CASE Expression

SELECT 
  product_name, 
  unit_price, 
  CASE unit_price 
    WHEN 10 THEN 'Low price'
    WHEN 20 THEN 'Medium price'
    WHEN 30 THEN 'High price'
    ELSE 'Unknown price'
  END AS price_category 
FROM 
  products;

In this example, the SQL CASE expression is used to categorize products based on their unit price. If the unit price matches any of the values, the corresponding price category is returned. If none of the values match, “Unknown price” is returned.

Searched CASE Expression

SELECT order_id, order_date, 
  CASE
    WHEN order_total < 50 THEN 'Low'
    WHEN order_total BETWEEN 50 
    AND 100 THEN 'Medium' 
    ELSE 'High'
  END AS order_category 
FROM 
  orders;

In this example, the SQL CASE expression is used to categorize orders based on their total amount. If the order total is less than 50, “Low” is returned. If the order total is between 50 and 100, “Medium” is returned. If the order total is greater than 100, “High” is returned.

Using CASE expression with aggregate functions

SELECT 
  SUM(CASE WHEN age >= 18 THEN 1 ELSE 0 END) AS num_adults, 
  SUM(CASE WHEN age < 18 THEN 1 ELSE 0 END) AS num_children, 
  AVG(
    CASE WHEN gender = 'male' THEN income ELSE NULL END
  ) AS avg_male_income, 
  AVG(
    CASE WHEN gender = 'female' THEN income ELSE NULL END
  ) AS avg_female_income 
FROM 
  users;

This query uses the CASE expression inside aggregate functions to calculate the number of adults and children in a table, as well as the average income for males and females separately.

Using CASE expression with multiple conditions

SELECT name, 
  CASE
   WHEN age < 18 THEN 'Minor'
   WHEN age BETWEEN 18 AND 65 THEN 'Adult'
   ELSE 'Senior'
 END AS age_group 
FROM 
  users;

This query uses a CASE expression with multiple conditions to categorize users into different age groups based on their age.

Using CASE expression with subqueries:

SELECT 
  name, 
  (
    SELECT 
      COUNT(*) 
    FROM 
      orders 
    WHERE 
      customer_id = users.id
  ) AS num_orders, 
  CASE WHEN (
    SELECT 
      COUNT(*) 
    FROM 
      orders 
    WHERE 
      customer_id = users.id
  ) >= 3 THEN 'Frequent customer' ELSE 'Occasional customer' END AS customer_type 
FROM 
  users;

This query uses a CASE expression with a subquery to determine whether a user is a frequent or occasional customer based on the number of orders they have placed.

Using CASE expression with date functions:

SELECT order_date,
CASE
 WHEN DATEPART(month, order_date) BETWEEN 1 AND 3 THEN 'Q1'
 WHEN DATEPART(month, order_date) BETWEEN 4 AND 6 THEN 'Q2'
 WHEN DATEPART(month, order_date) BETWEEN 7 AND 9 THEN 'Q3'
ELSE 'Q4'
END AS quarter
FROM orders;

This query uses a CASE expression with the DATEPART function to group orders into quarters based on their order date.

Using CASE expression with joins:

SELECT 
  users.name, 
  CASE
   WHEN orders.id IS NULL THEN 'No orders'
   ELSE 'Has orders' END AS order_status 
FROM 
  users 
  LEFT JOIN orders ON users.id = orders.customer_id;

This query uses a CASE expression with a LEFT JOIN to determine whether a user has placed any orders or not. If the user has not placed any orders, the query returns “No orders”, otherwise it returns “Has orders”.