Introduction
Simple CASE WHEN
Searched CASE WHEN
10. Searched CASE WHEN with NULLs
CASE WHEN with aggregates
CASE WHEN with GROUP BY
Revision

Instruction

Good. You can also work with NULLs in a CASE WHEN statement:

SELECT CASE
  WHEN fee IS NULL THEN 'no fee recorded'
  WHEN fee IS NOT NULL THEN 'fee recorded'
  END AS fee_information
FROM application;

We used the standard IS (NOT) NULL construction to check if the column is NULL. Note that we didn't have to use the ELSE part – a column value can be NULL or NOT NULL only. There is no other option, so we didn't need to specify it.

Exercise

For each candidate, show the first and last name along with the third column contact_info with the values:

  • 'provided' when the column preferred_contact is not NULL,
  • or 'not provided' otherwise.

Stuck? Here's a hint!

Use

CASE WHEN preferred_contact IS NULL THEN …

Console

Code editor

Result

TableConsole