Case Statements

Let's review an example from the tutorial on nulls values.

SELECT
	a.award_id,
	a.award_fund,
	COALESCE(a.award_disbursed, a.award_accepted, a.award_offered) AS amount
FROM award AS a
WHERE a.award_accepted IS NULL
OR a.award_disbursed IS NULL;

award_id	award_fund	amount
X00002	SEOG	20.0
X00009	SEOG	90.0
X00010	PELL	100.0
X00010	SEOG	110.0

This query selects the records with null values. It selects looks at the disbursed, accepted, and offered amounts looking for a non-null value. If it cannot find one it defaults to zero. What we really need is a column indicating the status of the award. We can do that with a CASE statement.

A CASE statement allows you to select a value based on a list of tests. The tests are the same conditional statements used in WHERE statements. A CASE statement uses the following template.

CASE
	WHEN [condition] THEN [value]
	WHEN [condition2] THEN [value]
	ELSE [default_value]
END AS column_name

We can implement this pattern like this.

SELECT
	a.award_id,
	a.award_fund,
	CASE
		WHEN a.award_disbursed IS NOT NULL THEN 'Disbursed'
		WHEN a.award_accepted IS NOT NULL THEN 'Accepted'
		WHEN a.award_offered IS NOT NULL THEN 'Offered'
		ELSE 'Cancelled'
	END AS status,
	COALESCE(a.award_disbursed, a.award_accepted, a.award_offered) AS amount
FROM award AS a
WHERE a.award_accepted IS NULL
OR a.award_disbursed IS NULL;

award_id	award_fund	status	amount
X00002	SEOG	Accepted	20.0
X00009	SEOG	Offered	90.0
X00010	PELL	Offered	100.0
X00010	SEOG	Offered	110.0

The CASE statement creates the status column. It checks the disbursed amount to see if it is null. If it is not null the status value is set as the string literal 'Disbursed'. It then does the same with the accepted amount and the offered amount with the resulting values of 'Accepted' and 'Offered', respectively. If no WHEN statement resolves as logically true then the optional ELSE keyword allows a default value. If no ELSE value is provided the default is null.

WHEN statements are always resolved from the top down. Once a logically true value is found that value is used and the other tests are skipped. This is why we checked the disbursed, accepted, and offered values in that order. All of the records we selected have an offered amount. If we reverse the order of WHEN statements the status columns would all be 'Offered'.

Logical Chains of Conditionals

We can provide a WHEN statement more than one conditional if we chain the conditionals with logical operators such as AND, or OR. Suppose we want to split Pell awards into 'None', 'Small', 'Medium', and 'Large' categories.

SELECT
	a.award_id,
	a.award_fund,
	a.award_offered,
	CASE
		WHEN award_offered = 0 THEN 'None'
		WHEN award_offered > 0 AND award_offered < 100 THEN 'Small'
		WHEN award_offered >= 100 AND award_offered < 500 THEN 'Medium'
		WHEN award_offered >= 500 THEN 'Large'
	END AS size
FROM award AS a
WHERE award_fund = 'PELL'

award_id	award_fund	award_offered	size
X00001	PELL	100.0	Medium
X00002	PELL	200.0	Medium
X00003	PELL	3000.0	Large
X00005	PELL	555.0	Large
X00008	PELL	88.0	Small
X00010	PELL	100.0	Medium

The first and last WHEN statements are similar to our previous example. The first one checks if the offered amount is zero and the last one checks if it is greater than or equal to 500.

But the second one checks if the offered amount is greater than zero AND it checks if the amount is less than 100. The third WHEN statement also has two conditionals. It checks if the value is greater than or equal to 100 AND if less than 500.

CASE vs. COALESCE()

CASE statements can also replace COALESCE() functions. COALESCE() runs through a list of values and conditionals looking for the first one that is not null. If your WHEN statements also check for nulls the behavior is identical. As an example, if you wanted to the select the offered amount with nulls replaced with zeros the following code would create two columns with identical data.

SELECT
	COALESCE(a.award_offered, 0) AS offered,
	CASE
		WHEN a.award_offered IS NOT NULL THEN a.award_offered
		ELSE 0
	END AS offered
FROM award AS a;

Different Columns

You can also use CASE statements to select from different input columns for a single output column. In this example we want the offered amounts for Pell or SEOG awards, but the accepted amounts for loans.

SELECT
	a.award_id,
	a.award_fund,
	CASE
		WHEN a.award_fund = 'PELL' THEN a.award_offered
		WHEN a.award_fund = 'SEOG' THEN a.award_offered
		WHEN a.award_fund = 'UNSUB' OR a.award_fund = 'SUB' THEN a.award_accepted
	END AS "Off_Acc"
FROM award AS a;

I decided to use a WHEN statement for Pell and another for SEOG. But for loans I used a single WHEN statement with two conditionals linked by a logical OR. Both are valid. It is just a stylistic choice if your want your CASE statement to be taller or wider.