Usage of case statements in SQL

How to Use Case in SQL

Case has two formats. Simple Case functions and Case search functions.
Copy codeThe Code is as follows:
-- Simple Case Function

CASE sex

WHEN '1' THEN 'male'

WHEN '2' THEN 'female'

ELSE 'others' END

-- Case search function

Case when sex = '1' THEN 'male'

WHEN sex = '2' THEN 'female'

ELSE 'others' END

These two methods can achieve the same function. Simple Case functions are relatively simple in writing, but compared with Case search functions, there are some functional limitations, such as writing Case functions.

Note that the Case function returns only the first value that meets the condition, and the rest of the Case will be automatically ignored.

-- For example, in the following SQL statement, you will never get the result of the second type.
Copy codeThe Code is as follows:
Case when col_1 IN ('A', 'B') then' first class'

WHEN col_1 IN ('A') then' Second Class'

ELSE 'others' END

Let's take a look at what we can do with the Case function.

1. Known Data is grouped and analyzed in another way.

There are the following data: (for better understanding, I did not use country code, but directly use the country name as the Primary Key)

Country population (population)

China 600

US 100

Canada 100

UK 200

France 300

Japan 250

Germany 200

Mexico 50

India 250

The population of Asia and North America is counted based on the population data of this country. The following result is returned.

Continent population

Asia 1100

North America 250

Other 700

What do you do if you want to solve this problem? Generating a View with continent Code is a solution, but it is difficult to dynamically change the statistical method.

If the Case function is used, the SQL code is as follows:
Copy codeThe Code is as follows:
Select sum (population ),

CASE country

WHEN 'China' THEN 'Asian'

WHEN 'India 'then' Asia'

WHEN 'Japan 'then' Asia'

WHEN 'American 'then' North American'

WHEN 'Canada 'then' North America'

WHEN 'Mexico 'then' North America'

ELSE 'others' END

FROM Table_A

Group by case country

WHEN 'China' THEN 'Asian'

WHEN 'India 'then' Asia'

WHEN 'Japan 'then' Asia'

WHEN 'American 'then' North American'

WHEN 'Canada 'then' North America'

WHEN 'Mexico 'then' North America'

ELSE 'others' END;

Similarly, we can use this method to determine the wage level and count the number of people at each level. The SQL code is as follows;
Copy codeThe Code is as follows:
SELECT

Case when salary <= 500 THEN '1'

WHEN salary> 500 AND salary <= 600 THEN '2'

WHEN salary> 600 AND salary <= 800 THEN '3'

WHEN salary> 800 AND salary <= 1000 THEN '4'

Else null end salary_class,

COUNT (*)

FROM Table_A

GROUP

Case when salary <= 500 THEN '1'

WHEN salary> 500 AND salary <= 600 THEN '2'

WHEN salary> 600 AND salary <= 800 THEN '3'

WHEN salary> 800 AND salary <= 1000 THEN '4'

Else null end;

2. Use an SQL statement to group different conditions.

The following data is available:

Country sex population (population)

China 340

China 260

US 1 45

US 2 55

Canada 1 51

Canada 2 49

UK 1 40

UK 2 60

Group by country and gender. The result is as follows:

National Men and Women

China 340 260

US 45 55

Canada 51 49

UK 40 60

In normal cases, UNION can also be used to query with a statement. However, this will increase the consumption (two Select clauses), and the SQL statement will be relatively long.

The following is an example of using the Case function to complete this function.
Copy codeThe Code is as follows:
SELECT country,

SUM (case when sex = '1' THEN

Population ELSE 0 END), -- male population

SUM (case when sex = '2' THEN

Population ELSE 0 END) -- female population

FROM Table_A

Group by country;


In this way, Select is used to complete the output form of the explain table, which fully shows the power of the Case function.

3. Use the Case function in Check.

Using the Case function in Check is a good solution in many cases. There may be a lot of people who don't need Check at all, so I suggest you try to use Check in SQL after reading the example below.

The following is an example.

Company A, the company has A rule that the salary of A female employee must be higher than A dollar. If you use Check and Case
Copy codeThe Code is as follows:
CONSTRAINT check_salary CHECK

(Case when sex = '2'

Then case when salary> 1000

THEN 1 ELSE 0 END

ELSE 1 END = 1)

If you simply use Check, as shown below:
Copy codeThe Code is as follows:
CONSTRAINT check_salary CHECK

(Sex = 's2' AND salary> 1000)


The conditions of the female employee are met, and the male employee cannot enter the information.

4. Conditional UPDATE.

For example, the following update conditions are available:

Employees with higher salaries are reduced by %

The employee's salary increases by %.

You can easily choose to execute two UPDATE statements, as shown below:
Copy codeThe Code is as follows:
-- Condition

UPDATE Personnel

SET salary = salary * 0.9

WHERE salary >=5000;

-- Condition

UPDATE Personnel

SET salary = salary * 1.15

WHERE salary> = 2000 AND salary <4600;

However, it is not as simple as you think. Suppose there is a personal wage. First, according to the conditions, the salary is reduced by % and changed to salary. Next, when running the second SQL statement, because the salary of this person is within the reached range, you need to increase by %. Finally, the salary of this person is not reduced, but increased. If it is executed in turn, the wage will be reduced on the contrary. No matter how absurd this rule is, if you want an SQL statement to implement this function, we need to use the Case function. The Code is as follows:
Copy codeThe Code is as follows:
UPDATE Personnel

SET salary = case when salary >=5000

THEN salary x 0.9

WHEN salary> = 2000 AND salary <4600

THEN salary X 1.15

ELSE salary END;

Note that the ELSE salary in the last line is necessary. If there is no such line, the salary of a person who does not meet the two conditions will be written as NUll, which is not a good deal. In the Case function, the default value of the Else part is NULL, which is worth attention.

This method can also be used in many places, such as changing the primary key.

Generally, to exchange the Primary key, a, and B of two data, you need to temporarily store, copy, and read the data back. If you use the Case function, everything is much easier.

P_key col_1 col_2

A 1 Zhang San

B 2 Li Si

C 3 Wang Wu

Assume that the primary keys a and B need to be exchanged for each other. The Code is as follows:
Copy codeThe Code is as follows:
UPDATE SomeTable

SET p_key = case when p_key = 'A'

THEN 'B'

WHEN p_key = 'B'

THEN 'A'

ELSE p_key END

WHERE p_key IN ('A', 'B ');

You can also exchange two Unique keys. It should be noted that if primary keys need to be exchanged, most of the reasons are that the design of the table was not enough. We recommend that you check whether the table is properly designed.

5. Check whether the data of the two tables is consistent.

The Case function is different from the DECODE function. IN the Case function, BETWEEN, LIKE, is null, IN, EXISTS, and so on can be used. For example, you can use IN and EXISTS to perform subqueries to implement more functions.

Two tables, tbl_A and tbl_ B, both of which have keyCol columns. Now we compare the two tables. If the data in the keyCol column of tbl_A can be found in the data in the keyCol column of tbl_ B, the return result is 'matched'. If not, the returned result is 'unmatched '.

To implement the following functions, you can use the following two statements:
Copy codeThe Code is as follows:
-- When Using IN

SELECT keyCol,

Case when keyCol IN (SELECT keyCol FROM tbl_ B)

THEN 'matched'

ELSE 'unmatched' END Label

FROM tbl_A;

-- When Using EXISTS

SELECT keyCol,

Case when exists (SELECT * FROM tbl_ B

WHERE tbl_A.keyCol = tbl_ B .keyCol)

THEN 'matched'

ELSE 'unmatched' END Label

FROM tbl_A;

The results of using IN and EXISTS are the same. You can also use not in and not exists, but pay attention to NULL.

6. Use the aggregate function in the Case Function

Assume that the following table exists.

Student ID (std_id) course ID (class_id) Course name (class_name) major flag (main_class_flg)

100 1 economics Y

100 2 history N

200 2 history N

200 3 archaeology Y

200 4 Computer N

300 4 Computer N

400 5 chemistry N

500 6 mathematics N

Some students choose to take several courses (100,200) at the same time, and some students choose only one course (300,400,500 ). If you are a student of multiple courses, You must select a course as your major. The major is flag, which is written into Y. Students who only select one course, whose major is flag is N (in fact, if I write Y, there will be no troubles below. For example, please include more ).

Now we need to query the table according to the following two conditions:

The person who takes only one course returns the ID of the course.

Returns the ID of the selected primary course.

The simple idea is to execute two different SQL statements for query.

Condition
Copy codeThe Code is as follows:
-- Condition: only students who have selected one course

SELECT std_id, MAX (class_id) AS main_class

FROM Studentclass

Group by std_id

Having count (*) = 1;

Execution result

STD_ID MAIN_class

----------------

300 4

400 5

500 6

Condition
Copy codeThe Code is as follows:
-- Condition: select students of multiple courses

SELECT std_id, class_id AS main_class

FROM Studentclass

WHERE main_class_flg = 'y ';

Execution result

STD_ID MAIN_class

----------------

100 1

200 3

If the Case function is used, we only need one SQL statement to solve the problem, as shown below:
Copy codeThe Code is as follows:
SELECT std_id,

Case when count (*) = 1 -- select only the students of one course

Then max (class_id)

Else max (case when main_class_flg = 'y'

THEN class_id

ELSE NULL END

)

End as main_class

FROM Studentclass

Group by std_id;

Running result

STD_ID MAIN_class

----------------

100 1

200 3

300 4

400 5

500 6

By embedding Case functions in Case functions and using Case functions in aggregate functions, we can easily solve this problem. The use of the Case function gives us greater freedom.

Finally, I would like to remind the novice who uses the Case function not to make the following mistakes.
Copy codeThe Code is as follows:
CASE col_1

WHEN 1 THEN 'right'

When null then 'wrong'

END

In this statement, the When Null Line always returns unknown, so Wrong will never occur. This statement can be replaced with WHEN col_1 = NULL. This IS an incorrect usage. In this case, we should use WHEN col_1 is null.