SQL CASE WHEN: синтаксис и примеры

SQL CASE WHEN

SQL CASE WHEN is a construct that can be used in the SELECT statement to perform conditional checks and return different values based on the results of these checks. It is a powerful tool that allows you to use logical operators and functions to execute complex conditions within a single SELECT statement.

It is important to note that the CASE WHEN construct can be used not only in the SELECT statement, but also in the UPDATE and DELETE statements to perform conditional changes and deletions of data, respectively. It has the following syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    WHEN conditionN THEN resultN
    ELSE result
END

Where condition1, condition2, ..., conditionN are conditions, result1, result2, ..., resultN are the values to be returned in case the corresponding condition is satisfied, and result is the value to be returned if none of the conditions are satisfied.

Let's consider some code examples to better understand how to use CASE WHEN in SQL.

Example 1:

Suppose we have a table "Employees" with a column "Salary". We want to return "High" for employees with a salary greater than 5000, "Medium" for employees with a salary between 3000 and 5000, and "Low" for employees with a salary less than 3000. We can write the following SQL query:

SELECT Name, 
    CASE
        WHEN Salary > 5000 THEN 'High'
        WHEN Salary >= 3000 AND Salary <= 5000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryCategory
FROM Employees;

Example 2:

Let's assume we have a table "Products" with columns "ProductID" and "ProductName". We want to return "Expensive" for products with prices in the range of 100 to 150, "Affordable" for products with prices between 50 and 100, and "Cheap" for products with prices less than 50. Our SQL query may look like this:

SELECT ProductName, 
    CASE
        WHEN Price >= 100 AND Price <= 150 THEN 'Expensive'
        WHEN Price > 50 AND Price < 100 THEN 'Affordable'
        ELSE 'Cheap'
    END AS PriceCategory
FROM Products;

These are just two examples of using CASE WHEN in SQL. The CASE WHEN construct offers a wide range of possibilities when working with conditional statements and helps create flexible and expressive logic for data processing. It allows you to write compact code with an easy understanding of its functionality. It is important to know the syntax and usage of this construct in order to use it most effectively.