2.1 CASE statement
There are two ways to write a statement in SQL CASE
. The first entry makes it look like switch from the Java language, while the second format is more like plural if-else
.
Let's start with the first option - an analog of switch. The general format is:
CASE case_value
WHEN value1 THEN result1
[WHEN value2 THEN result2] ...
[ELSE resultN]
END
Really very similar to switch
, only the words are slightly different:
SQL | Java |
---|---|
case x | switch (x) { |
when value | case value: |
then result | return result; |
else result | default: return result; |
end | } |
Let's translate an example from Java into SQL:
Java | SQL |
---|---|
|
|
2.2 Second version of the CASE statement
There is also a second version of the operator CASE
, which, as mentioned above, is more like plural if-else
. The general format is:
CASE
WHEN condition 1 THEN result1
[WHEN condition 2 THEN result2] ...
[ELSE resultN]
END
Here, the conditions are simply checked sequentially, if any of them is true, then the specified result will be returned. If none of the conditions are true, then the value specified in ELSE
.
Let's translate an example from Java into SQL:
Java | SQL |
---|---|
|
|
|
|
|
|
2.3 Solve the problem using the CASE statement
Let's write down some interesting query. Remember, we had a task - to add the word "EXPIRED!" to the name of past tasks in the task table . It can be easily done with the CASE
.
For the deadline field , it will look like the second version of the statement CASE
:
CASE
WHEN deadline < CURDATE() THEN CONCAT('EXPIRED!', name)
ELSE name
END
An example of a complete query using the operator CASE
:
SELECT
id,
emploee_id,
CASE WHEN deadline < CURDATE() THEN CONCAT('EXPIRED!', name) ELSE name END AS name,
deadline
FROM task
The result of this query will be:
id | emploee_id | name | deadline |
---|---|---|---|
1 | 1 | EXPIRED! Fix a bug on the frontend | 2022-06-01 |
2 | 2 | Fix a bug on the backend | 2022-06-15 |
3 | 5 | Buy coffee | 2022-07-01 |
4 | 5 | Buy coffee | 2022-08-01 |
5 | 5 | Buy coffee | 2022-09-01 |
6 | (NULL) | Clean up the office | (NULL) |
7 | 4 | Enjoy life | (NULL) |
8 | 6 | Enjoy life | (NULL) |