case when usage in sql

case when in sql is used to perform different operations based on different conditions in SQL queries. You can make judgments based on any conditions and perform corresponding operations based on the results of the conditions. The working principle of the CASE WHEN statement is to judge the conditions one by one from top to bottom. Once a condition is met, the corresponding operation will be performed, and only the first one that satisfies the condition will be executed. Conditional operations.

The CASE WHEN statement in SQL is a conditional expression used to perform different operations based on different conditions in SQL queries. It can judge based on any conditions and perform corresponding operations based on the results of the conditions. In SQL, the syntax of the CASE WHEN statement is as follows:

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

In this syntax, condition1 and condition2 are conditional expressions, which can be any conditions compatible with SQL. result1 and result2 are execution results, which can be column names, constant values ​​or expressions. The final ELSE result is optional and is used to handle other situations where the conditions are not met.

The working principle of the CASE WHEN statement is to judge the conditions one by one from top to bottom. Once a condition is met, the corresponding operation will be executed, and only the first operation that satisfies the condition will be executed. If no condition is met, the operations in the ELSE block are performed.

Here are some examples of CASE WHEN statements:

1. Simple CASE WHEN statement:

SELECT
customer_name,
CASE
WHEN gender = 'Male' THEN 'Mr.'
WHEN gender = 'Female' THEN 'Ms.'
ELSE 'Unknown'
END AS salutation
FROM
customers;

The above query will generate different results based on the gender field If the gender is Male, the title is 'Mr.', if the gender is Female, the title is 'Ms.', and in other cases, the title is 'Unknown'.

2. The combination of CASE WHEN statement and aggregate function:

SELECT
department_id,
COUNT(*) AS employees_count,
CASE
WHEN COUNT(*) > 10 THEN 'Large'
WHEN COUNT(*) > 5 THEN 'Medium'
ELSE 'Small'
END AS department_size
FROM
employees
GROUP BY
department_id;

The above query will divide the departments into large, medium and small according to the number of employees in each department. Small three different categories.

3. Application of CASE WHEN statement in WHERE clause:

SELECT
order_id,
order_date,
CASE
WHEN order_status = 'Shipped' THEN 1
ELSE 0
END AS shipped_flag
FROM
orders
WHERE
CASE
WHEN order_status = 'Shipped' THEN 1
ELSE 0
END = 1;

The above query will only return orders that have been shipped, and the filter condition uses the CASE WHEN statement .

Summary

The CASE WHEN statement is a very useful conditional expression in SQL that can perform different operations based on different conditions. It provides a flexible way to handle different scenarios, making queries more concise and intuitive. Whether generating new columns, calculating statistics, or filtering data, the CASE WHEN statement can meet your needs and help optimize SQL queries.

The above is the detailed content of case when usage in sql. For more information, please follow other related articles on the PHP Chinese website!