CASE

Sometimes you'll need to do different things based on if a value is NULL or not. One useful keyword for that is CASE. Before we look at that with NULL, let's look at CASE by itself.

If-Then Logic

CASE gives you "if this then that" type of logic. Let's look at an example with the numbers table.

SELECT
  x,
  CASE
    WHEN x % 2 = 0 THEN 'even'
    ELSE 'odd'
  END AS parity
FROM numbers;

Case expressions are delimited by the keywords CASE and END. In between, you can have 1 or more WHEN THEN clauses. The WHEN expression must evaluate to a boolean. If TRUE, the corresponding THEN expression becomes the chosen value of the case expression. Optionally, you can have an ELSE clause with a final expression to be chosen if none of the other WHEN clauses are TRUE.

Notice that I call it a "case expression" and not a "case clause". This is an important distinction because expressions are things that go inside a clause, like in a SELECT or WHERE clause.

Here is an example with multiple WHENs where we play the famous children's game "FizzBuzz".

SELECT
  x,
  CASE
    WHEN x % 15 = 0 THEN 'FizzBuzz'
    WHEN x % 5 = 0 THEN 'Buzz'
    WHEN x % 3 = 0 THEN 'Fizz'
    ELSE x
  END AS output
FROM numbers;

error: invalid input syntax for type integer: "FizzBuzz"

Wait!! There's an error?

Type Casting

The problem with the above example is that a CASE expression expects every case to return the same type. We can't have some cases return text and some integers. So, we need the ability to convert x to TEXT instead of INTEGER. You can do that with CAST. It looks like this: CAST(some_value AS OTHER_TYPE).

SELECT
  x,
  CASE
    WHEN x % 15 = 0 THEN 'FizzBuzz'
    WHEN x % 5 = 0 THEN 'Buzz'
    WHEN x % 3 = 0 THEN 'Fizz'
    ELSE CAST(x AS TEXT)
  END AS output
FROM numbers;

Postgres-Specific Casting

CAST is part of standardized SQL. It should work on any SQL database. However, Postgres has its own alternative. Instead of CAST(x AS TEXT), you can use x::TEXT.

SELECT
  x,
  CASE
    WHEN x % 15 = 0 THEN 'FizzBuzz'
    WHEN x % 5 = 0 THEN 'Buzz'
    WHEN x % 3 = 0 THEN 'Fizz'
    ELSE x::TEXT
  END AS output
FROM numbers;

This is something in programming that we call "syntactic sugar," which is a more convenient/appealing but functionally equivalent way to express something.

You can use either CAST or the :: operator with Postgres, as they are interchangeable. I recommend using CAST. Generally, the standard method of doing things is more beneficial to know for the long-term because that knowledge will transfer when you use a different SQL database.

Filling NULL Values

Sometimes we want to change NULL to something else, either because NULL's properties are confusing/inconvenient or because we want to fill it with a different value.

For example, suppose our app "Yulp" want to fill the cuisine_type with the string "Other" instead of NULL.

SELECT
  name,
  cuisine_type,
  CASE
    WHEN cuisine_type IS NOT NULL THEN cuisine_type
    ELSE 'Other'
  END AS cuisine_type_filled
FROM restaurants
WHERE delivery_fee_usd IS NOT NULL;

Coalesce

There is a nice little shortcut for CASE WHEN x IS NOT NULL THEN x ELSE 0 END. You can call the function coalesce(x, 0) instead!

SELECT
  name,
  cuisine_type,
  coalesce(cuisine_type, 'Other') AS cuisine_type_filled
FROM restaurants
WHERE delivery_fee_usd IS NOT NULL;