vuthanhdatt's Second Brain

SQL Learn - Conditional Logic

2 min read

two different types of case expressions.

Searched case Expression

Syntax

CASE WHEN C1 THEN E1 
	WHEN C2 THEN E2
	...
	WHEN Cn THEN En
	[ELSE E]
END

keep in mind that case expressions may return any type of expression, including subqueries

mysql> SELECT c.first_name, c.last_name, 
-> CASE 
-> WHEN active = 0 THEN 0 
-> ELSE 
-> (SELECT count(*) FROM rental r 
	-> WHERE r.customer_id = c.customer_id) 
-> END num_rentals 
-> FROM customer c;
 
| BARBARA | JONES | 22 | 
| ELIZABETH | BROWN | 38 | 
| JENNIFER | DAVIS | 28 | 
... 
| TERRANCE | ROUSH | 0 | 
| RENE | MCALISTER | 26 | 
| EDUARDO | HIATT | 27 |

Depending on the percentage of active customers, using this approach may be more efficient than joining the customer and rental tables and grouping on the customer_id column.

Examples of case Expressions

mysql> SELECT 
-> SUM(CASE WHEN monthname(rental_date) = 'May' THEN 1 
	   -> ELSE 0 END) May_rentals, 
	   -> SUM(CASE WHEN monthname(rental_date) = 'June' THEN 1
		-> ELSE 0 END) June_rentals, 
		-> SUM(CASE WHEN monthname(rental_date) = 'July' THEN 1
		 -> ELSE 0 END) July_rentals 
		 -> FROM rental 
		 -> WHERE rental_date BETWEEN '2005-05-01' AND '2005-08-01';
 
| May_rentals | June_rentals | July_rentals | 
| 1156 | 2311 | 6709 |

Graph View