If ELSE in SQL (use of Case statement)

You may be familiar with the IF Else statement, which is used to control the process. A CASE statement statement has a similar effect in the world of SQL. The following is a simple introduction to the use of case statements. Consider the following scenario, assuming that there is a user table that is defined as follows:

CREATE TABLE USER
(
Name VARCHAR () not NULL,---name
Sex INTEGER,---gender (1, male 2, female)
BIRTHDAY DATE---Birthday
);
CREATE TABLE USER
(
Name VARCHAR () not NULL,---name
Sex INTEGER,---gender (1, male 2, female)
BIRTHDAY DATE---Birthday
);

Case Scenario 1: Export the user table to generate a file that requires gender to be male or female instead of 1 and 2. We can deal with the following statements:

SELECT
NAME,
Case SEX
When 1 Then ' Male '
ELSE ' Women '
END as SEX,
BIRTHDAY
From USER;
SELECT
NAME,
Case SEX
When 1 Then ' Male '
ELSE ' Women '
END as SEX,
BIRTHDAY
From USER;

Case Scenario 2: Assume that the user currently has no value, and then you import a batch of data to the user, but unfortunately, the wrong man set to 2, and the female set to 1, now ask you to change over, how to do?

Method 1: Use three statements, first update 2 to 3, then 1 update to 2, and finally update 3 to 1, very troublesome, is not it?

UPDATE USER SET sex=3 WHERE sex=2;
UPDATE USER SET sex=1 WHERE sex=3;
UPDATE USER SET sex=2 WHERE sex=1;
UPDATE USER SET sex=3 WHERE sex=2;
UPDATE USER SET sex=1 WHERE sex=3;
UPDATE USER SET sex=2 WHERE sex=1;

Method 2: Use the case statement

UPDATE USER SET sex=
(
Case SEX
When 1 then 2
When 2 then 1
ELSE SEX
END
);
UPDATE USER SET sex=
(
Case SEX
When 1 then 2
When 2 then 1
ELSE SEX
END
);

Careful friend may have found that the above method 1 of the three statements of the order of execution has a problem, yes, I deliberately those write, just to turn 1 into 2, 2 into 1 is so troublesome, and very prone to error, imagine, if there are many such values need to transform, that is a kind of situation. Fortunately, we have a case statement, there are a lot of such values need to be transformed, there will be no problem. Maybe some friends still have doubts, will this do not die cycle ah? Haha, the idea is very good, if you find this to be a dead loop, be sure to tell IBM that I didn't find it anyway.

Case Use Scenario 3: Let's say you put Zhang San's birthday updated to 1949-10-1, John Doe's birthday updated to 1997-7-1, etc., like this update by a lot. What should we do? Very simple, most people would do that.

Update USER set birthday= ' 1949-10-1 ' where Name= ' Zhang San ';
Update USER set birthday= ' 1997-7-1 ' where name= ' John Doe ';
Update USER set birthday= ' 1949-10-1 ' where Name= ' Zhang San ';
Update USER set birthday= ' 1997-7-1 ' where name= ' John Doe ';

When the data volume of the user table is very large, and the name field does not have an index, each statement will have a full table scan, if there are a lot of such statements, the efficiency is very poor, then we can use case statements, as follows:

UPDATE USER SET birthday=
(
Case NAME
When ' Zhang San ' and ' 1949-10-1 '
When ' John Doe ' and ' 1997-7-1 '
ELSE BIRTHDAY
END
)
where NAME in (' Zhang San ', ' John Doe ');
UPDATE USER SET birthday=
(
Case NAME
When ' Zhang San ' and ' 1949-10-1 '
When ' John Doe ' and ' 1997-7-1 '
ELSE BIRTHDAY
END
)
where NAME in (' Zhang San ', ' John Doe ');

The above statement is very efficient, with only one full table scan.

---acknowledgements: Higny found a mistake in this article, which expresses its serious thanks

---more see: DB2 SQL Essentials

----statement: Reprint please indicate the source.

----Last update at 2010.5.7

----Write by wave at 2009.9.23

----End

http://blog.csdn.net/courageously/article/details/5769757

If ELSE in SQL (use of Case statement)