What is it?

Conditional logic is simply the ability to take one of several paths during program execution.

Every database’s SQL implementation includes the case expression, which is useful in many situations, including simple translations:

select first_name, last_name,
	case
    when active = 1 then 'active'
    else 'inactive'
   end activity_type
from customer;

The case Expression

Database servers have included built-in functions to mimic if-then-else statement like:

Oracle’s decode() function, MySQL’s if() function, and SQL Server’s coalesce()

case() are also designed to facilitate if-then-else logic.

Searched case Expressions

CASE
	WHEN C1 THEN E1
	WHEN C2 THEN E2
	...
	WHEN CN THEN EN
	[ELSE ED] # optional
END
# C means condition

select first_name,last_name,
	case when active = 0 then 0
		else (select count(*) from rental as r
			where r.customer_id = c.customer_id)
	end num_rentals
from customer as c;

Context: Using a subquery to return the number of rentals, but only for active customers.

• The whole case expression define a column num_rentals, where it will simply does aggregate function and return 0 if active=0.

Simple case Expressions

CASE V0
	WHEN V1 THEN E1
	WHEN V2 THEN E2
	...
	WHEN VN THEN EN
	[ELSE ED] # optional
END
# V means value

Examples of case Expressions

Result Set Transformations

select monthname(rental_date) rental_month,
	count(*) num_rentals
from rental as r
where rental_date between '2005-05-01' and '2005-08-01'
group by 1;