Course Module 4. Working with databases - Lecture: CASE statement

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
`switch (a) { case 1: return "one"; case 2: return "two"; case 3: return "three"; default: return "unknown"; }`	`CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' WHEN 3 THEN 'three' ELSE 'unknown' END`

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

if (a == 3)
	return "three";

CASE
   WHEN a=3 THEN 'three'
END

if (a == 3)
	return "three";
else
	return "zero";

CASE
   WHEN a=3 THEN 'three'
   ELSE 'zero'
END

if (a == 1)
	return "three";
else if (a == 2)
	return "two";
else if (a == 3)
	return "three";
else
	return "zero";

CASE
   WHEN a=1 THEN 'one'
   WHEN a=2 THEN 'two'
   WHEN a=3 THEN 'three'
   ELSE 'zero'
END

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)