case statement in mysql code example

Example 1: mysql case

-- LABEL MOVIE LENGTH 
select title, length,
CASE 
	 when length < 50 then 'very short film'
     when length < 90 then 'short film'
     when length < 120 then 'medium length film'
     when length > 120 then 'long film'
     else 'unknown length'
END -- AS film_length
from film;

-- SAME AS ABOVE BUT ON A CUSTOM NAMED COLUMN
select title, length,
CASE 
	 when length < 50 then 'very short film'
     when length < 90 then 'short film'
     when length < 120 then 'medium length film'
     when length > 120 then 'long film'
     else 'unknown length'
END  AS film_length
from film;

-- RESULT
+-----------------------------+--------+--------------------+
| title                       | length | film_length        |
+-----------------------------+--------+--------------------+
| ACADEMY DINOSAUR            |     86 | short film         |
| ACE GOLDFINGER              |     48 | very short film    |
| ADAPTATION HOLES            |     50 | short film         |
| AFFAIR PREJUDICE            |    117 | medium length film |
| AFRICAN EGG                 |    130 | long film          |
| AGENT TRUMAN                |    169 | long film          |
| AIRPLANE SIERRA             |     62 | short film         |
| AIRPORT POLLOCK             |     54 | short film         |
| ALABAMA DEVIL               |    114 | medium length film |
| ALADDIN CALENDAR            |     63 | short film         |
| ALAMO VIDEOTAPE             |    126 | long film          |
| ALASKA PHANTOM              |    136 | long film          |
| ALI FOREVER                 |    150 | long film          |
| ALICE FANTASIA              |     94 | medium length film |
| ALIEN CENTER                |     46 | very short film    |

Example 2: mysql switch case

SELECT 
t2.company_name,
t2.expose_new,
t2.expose_used,
t1.title,
t1.status,
 CASE status
   when 'New' and t2.expose_new = 1 then 1
   when 'New' and t2.expose_new = 2 then 2
   when 'New' and t2.expose_new = 3 then 3
   when 'Used' and t2.expose_used = 1 then 1
   when 'Used' and t2.expose_used = 2 then 2
   when 'Used' and t2.expose_used = 3 then 3
END as expose
FROM `products` t1
join manufacturers t2 on t2.id = t1.seller
where t1.seller = 4238

Example 3: case statement in sql

Case Statement basically
Like IF - THEN - ELSE statement.

The CASE statement goes through conditions
and returns a value when the
first condition is met and
once a condition is true,
it will stop reading and return the result.
If no conditions are true,
it returns the value in the ELSE clause.

If there is no ELSE part and
no conditions are true, it returns NULL.

FOR EXAMPLE =

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END 

-- example:
SELECT 
	CASE
      WHEN (1+6 = 6) THEN 'A'
      WHEN (1+6 = 7) THEN 'B'
      WHEN (1+6 = 8) THEN 'C'
      ELSE 'D'
	END 
FROM DUAL;

Result would be 'B' since it is the first
correct answer