dark

How to Use CASE WHEN in SQL SELECT Statement

How to Use CASE WHEN in SQL SELECT Statement

The CASE WHEN statement in SQL SELECT statement is used to conditionally return results based on a specified condition. This tutorial will guide you on how to use this statement in your SQL SELECT queries.

Syntax

The basic syntax of the CASE WHEN statement is as follows:

SELECT
   column1,
   column2,
   ...,
   CASE
      WHEN condition1 THEN result1
      WHEN condition2 THEN result2
      ...
      ELSE result
   END AS alias
FROM
   table_name;

In the above syntax, condition1 is a condition that must be met for result1 to be returned, and so on. If none of the conditions are met, result is returned.

Example 1

Suppose we have a table employees containing information about employees of a company, including their id, name, age, and salary. We want to create a query that returns a column salary_level, which indicates whether an employee’s salary is high, medium or low based on the following criteria:

  • High: salary greater than or equal to $80,000.
  • Medium: salary greater than or equal to $50,000 and less than $80,000.
  • Low: salary less than $50,000.

Here’s the SQL code to accomplish this:

SELECT
   id,
   name,
   age,
   salary,
   CASE
      WHEN salary >= 80000 THEN 'High'
      WHEN salary >= 50000 THEN 'Medium'
      ELSE 'Low'
   END AS salary_level
FROM
   employees;

Example output:

idnameagesalarysalary_level
1John Doe3095000High
2Jane Doe2560000Medium
3Bob Smith4035000Low

Example 2

Suppose we have another table orders containing information about customer orders, including their id, customer_id, order_date, order_amount, and order_status. We want to create a query that returns a column order_type, which indicates whether an order is a first-time order or a repeat order based on the following criteria:

  • First-time order: the order is the customer’s first order.
  • Repeat order: the order is not the customer’s first order.

Here’s the SQL code to accomplish this:

SELECT
   id,
   customer_id,
   order_date,
   order_amount,
   order_status,
   CASE
      WHEN ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC) = 1 THEN 'First-time order'
      ELSE 'Repeat order'
   END AS order_type
FROM
   orders;

In the above SQL code, we are using the ROW_NUMBER() function to assign a row number to each order record for each customer based on the order_date column. Then, we are using the PARTITION BY clause to partition the data by customer_id and the ORDER BY clause to order the data by order_date. The CASE WHEN statement checks if the order record is the first one for its customer, and returns ‘First-time order’ if it is, or ‘Repeat order’ otherwise.

Troubleshooting Tips

  • Make sure your CASE WHEN statement is properly nested within your SQL SELECT statement.
  • Double-check your syntax and make sure all parentheses and commas are in the correct places.
  • Test your SQL query with a small subset of data before running it on your full dataset.

Conclusion

You have now learned how to use the CASE WHEN statement in SQL SELECT statement to conditionally return results based on a specified condition. With this knowledge, you can now write more complex SQL queries that are tailored to your specific needs.

Related tutorials