Когда тогда иначе конец SQL: использование операторов условий в базе данных

В SQL оператор "CASE" используется для выполнения различных действий в зависимости от значения определенного выражения. Он имеет следующий синтаксис:

        
            SELECT column_name,
                CASE
                    WHEN condition1 THEN result1
                    WHEN condition2 THEN result2
                    ELSE result
                END
            FROM table_name;
        
    

В этом примере, если условие condition1 истинно, то возвращается result1. Если condition1 ложно, но condition2 истинно, то возвращается result2. Если ни одно из условий не истинно, то возвращается result, указанный после ELSE.

Оператор CASE может иметь несколько блоков WHEN, в которых определяются различные условия и их результаты. Он также может иметь блок ELSE, который выполняется, если ни одно из условий не истинно.

Вот пример, демонстрирующий использование оператора CASE:

        
            SELECT column_name,
                CASE
                    WHEN age < 18 THEN 'Дети'
                    WHEN age >= 18 AND age < 65 THEN 'Взрослые'
                    ELSE 'Пенсионеры'
                END AS category
            FROM employees;
        
    

В этом примере, в зависимости от значения столбца "age", будет определена категория сотрудников: "Дети" для сотрудников моложе 18 лет, "Взрослые" для сотрудников возрастом от 18 до 64 лет и "Пенсионеры" для сотрудников возрастом 65 и старше.

Understanding the "WHEN THEN ELSE END" Statement in SQL

When working with databases and writing SQL queries, it is often necessary to make decisions and perform different actions based on certain conditions. In such cases, the "WHEN THEN ELSE END" statement comes in handy. It allows you to define conditional logic within your queries, letting you specify different actions to be taken based on a given condition.

The Syntax of the "WHEN THEN ELSE END" Statement

The syntax for the "WHEN THEN ELSE END" statement in SQL is as follows:

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

Let's break down the different parts of this syntax:

• CASE: This is the keyword that starts the "WHEN THEN ELSE END" statement.
• WHEN condition: This is where you specify the condition to be evaluated. If the condition evaluates to TRUE, the corresponding result will be returned.
• THEN result: This is the value that will be returned if the condition evaluates to TRUE.
• ELSE result: This is an optional part of the statement. If none of the previous conditions evaluate to TRUE, the result specified here will be returned.
• END: This is the keyword that ends the "WHEN THEN ELSE END" statement.

Example Usage

Let's consider a simple example to understand how the "WHEN THEN ELSE END" statement works. Suppose we have a table named "employees" with the following columns:

• employee_id: the unique identifier for each employee
• first_name: the first name of the employee
• last_name: the last name of the employee
• salary: the salary of the employee
• department: the department the employee belongs to

Now, let's say we want to retrieve a list of employees along with their salary grades based on the following criteria:

• Grade A: Salary greater than 5000
• Grade B: Salary greater than 3000
• Grade C: Salary greater than 2000
• Grade D: Salary less than or equal to 2000

We can achieve this using the "WHEN THEN ELSE END" statement. Here's an example SQL query:

SELECT 
   employee_id,
   first_name,
   last_name,
   salary,
   CASE
      WHEN salary > 5000 THEN 'Grade A'
      WHEN salary > 3000 THEN 'Grade B'
      WHEN salary > 2000 THEN 'Grade C'
      ELSE 'Grade D'
   END AS salary_grade
FROM
   employees;

In this query, we use the "WHEN THEN ELSE END" statement to assign salary grades to each employee based on their salary. The result column is named "salary_grade". If the salary is greater than 5000, the grade is 'Grade A'. If it's greater than 3000 but less than or equal to 5000, the grade is 'Grade B'. Similarly, for salaries greater than 2000 but less than or equal to 3000, the grade is 'Grade C'. And for salaries less than or equal to 2000, the grade is 'Grade D'.

The result of this query will be a list of employees along with their respective salary grades.

Conclusion

The "WHEN THEN ELSE END" statement in SQL allows you to define conditional logic within your queries. It enables you to perform different actions based on specified conditions, which is particularly useful when you need to categorize or classify data based on certain criteria. By using the "WHEN THEN ELSE END" statement, you can make your SQL queries more powerful and flexible.