Applying Logic with CASE

Introduction

In many programming languages, you use if -> then statements to apply logic.

In programming languages, you do this as follows:

if x > 0:
    print("The value is positive")
if x < 0:
    print("The value is negative")
else:
    print("The value is zero")

To apply if -> then logic in SQL, you use a CASE statement.

You also use WHEN, THEN, ELSE, and END expressions.

The above example would look like this in SQL:

CASE
  WHEN x > 0 then "The value is positive"
  WHEN x < 0 then "The value is negative"
  ELSE "The value is zero"
END

---

1. CASE example

Let's look at the following example:

SELECT
  name,
  year,
  CASE
    WHEN year > 1986 THEN 'After 1986'
    WHEN year = 1986 THEN 'From 1986'
    ELSE 'Before 1986'
  END
FROM
  movies;

name	year	case
Aliens	1986	From 1986
Animal House	1978	Before 1986
Apollo 13	1995	After 1986
...	...	...

Columns: 3
Rows: 36

Here's what happens:

• We select columns name and year from table movies.
• We apply a CASE statement.
  • If (WHEN) values in column year are greater than 1986, then (THEN) we get value After 1986.
  • If (WHEN) values in column year are equal to 1986, then (THEN) we get value From 1986.
  • Otherwise (ELSE) we get the value Before 1986.
• With END, we end the CASE statement.

The values from the CASE statement appear in a new column: case.

With this, you have added a new column using logic.

You can use comparison operators like =, >, and >= for comparisons.

---

2. Naming a new column from CASE with AS

We have seen that the new column is named case by default.

This is not always desired.

By using AS, you can give the column a specific name.

We do this in the following example:

SELECT
  name,
  year,
  CASE
    WHEN year > 1986 THEN 'After 1986'
    WHEN year = 1986 THEN 'From 1986'
    ELSE 'Before 1986'
  END AS year_category
FROM
  movies;

name	year	year_category
Aliens	1986	From 1986
Animal House	1978	Before 1986
Apollo 13	1995	After 1986
...	...	...

Columns: 3
Rows: 36

We added after END: AS year_category.

You see that the new column is now called year_category instead of case.

---

3. Using functions in combination with CASE

With a CASE statement, you compare scenarios.

You can use comparison operators like =, >, and >=.

You can also use functions.

We do this in the following example:

SELECT
  name,
  CASE
    WHEN LENGTH(name) >= 10 THEN 'Long name'
    ELSE 'Short length'
  END AS name_length_category
FROM
  movies;

name	name_length_category
Aliens	Short length
Animal House	Long name
Apollo 13	Short length
...	...

Columns: 2
Rows: 36

• With the function LENGTH(), we determine the length of a movie's name.
• Depending on the length, we create the new column name_length_category with the CASE statement.

---

4. Using logical operators in combination with CASE

With a CASE statement, you compare scenarios.

You can use comparison operators like =, >, and >=.

You can also use logical operators like AND, OR, and IN.

We do this in the following example:

SELECT
  name,
  CASE
    WHEN LENGTH(name) >= 10 THEN 'Long name'
    WHEN (
      LENGTH(name) > 5
      AND LENGTH(name) < 10
    ) THEN 'Medium length'
    ELSE 'Short length'
  END AS name_length_category
FROM
  movies;

name	name_length_category
Aliens	Medium length
Animal House	Long name
Apollo 13	Medium length
...	...

Columns: 2
Rows: 36

• With the function LENGTH(), we determine the length of a movie's name.
• Depending on the length, we create the new column name_length_category with the CASE statement.
• With the logical operator AND, we create the category Medium length, where the length is between 5 and 10 characters.

---

Summary

• With a CASE statement, you can apply logic.
  • You also use WHEN, THEN, ELSE, and END.
  • With this, you can add a column using comparisons.
  • For comparisons, you can use comparison operators like =, >, and >=.
  • Example of a CASE statement:

SELECT
name,
year,
CASE
    WHEN year > 1986 THEN 'After 1986'
    WHEN year = 1986 THEN 'From 1986'
    ELSE 'Before 1986'
END
FROM
movies;

• With AS, you can give the new column a specific name, for example:

SELECT
name,
year,
CASE
    WHEN year > 1986 THEN 'After 1986'
    WHEN year = 1986 THEN 'From 1986'
    ELSE 'Before 1986'
END AS year_category
FROM
movies;

• You can use functions in comparisons.
• You can use logical operators like AND, OR, and IN.