Logical IN

The last CASE statement example gives us the opportunity to discuss the IN keyword.

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;

We are checking if the record's fund is in a list of two award types (Pell, SEOG) or in another list (Subsidized loan, Unsubsidised loan). But what is we need to check if the award is one of a long list of funds? CASE statements and COALESCE() functions can begin to become very cumbersome. The IN keyword can help. It allows us to check if a value is in a list of values. The values are placed inside parenthesis and separated with commas.

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

IN conditional operations can be used wherever a conditional operator is required.