Back-To-School Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Simple CASE WHEN
Searched CASE WHEN
10. Searched CASE WHEN with NULLs
CASE WHEN with aggregates
CASE WHEN with GROUP BY
Summary

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_name and last_name along with the third column contact_info with the values:

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

Stuck? Here's a hint!

Use the following piece of code:

CASE
  WHEN preferred_contact IS NOT NULL THEN ...