SQL CASE

/ Database / By

When you are familiar with CASE, you can make your SQL statement more flexible and reduce redundant semantics.

CASE can be categorized into two cases. One is simple case

CASE

  WHEN condition THEN result

  [WHEN···]

  [ELSE result]

END;

and the other one is searched case

CASE expression

  WHEN value THEN result

  [WHEN···]

  [ELSE result]

END;

Notice:

  1. The return type of each case statement must be equal
  2. Add End
  3. Must add ELSE statement

The following examples manifest how to use CASE to minimize SQL redundant statement

Condition 1

Update Employee

SET salary = salary * 0.9

WHERE salary >= 30000

Condition 2

Update Employee

SET salary = salary * 1.2

WHERE salary >= 25000 AND salary < 28000

Now we use CASE

UPDATE Employee

SET salary = CASE WHEN salary >= 30000

THEN salary * 0.9

WHEN salary >= 25000 AND salary < 28000

THEN salary * 1.2

ELSE salary END;

We only need one sql statement by using SQL

Another two extra examples:

Example one

Table : sq1Q1

Try to get the maximum value of x and y

The result is

Example two

knowing how to use CASE is a compulsory skill for DBA. I hope this article gives you a basic knowledge of SQL CASE.