Using Greater-Than Operator in a SQL CASE Statement

1. Overview

In SQL, we use CASE statements to perform conditional logic within queries and define different outputs based on different conditions. A common condition we can use within a CASE statement is the comparison operator greater-than (>), which we can use to evaluate and compare data values.

In this tutorial, we’ll explore how to use the greater-than operator within an SQL CASE statement in MySQL, PostgreSQL, and SQL Server, using the Baeldung database schema to demonstrate.

2. Overview of the Student Table

We’ll focus on the Student table, which contains information about each student:

+------+-----------------+-------------+------------+-----------------+-----------------+------+
| id   | name            | national_id | birth_date | enrollment_date | graduation_date | gpa  |
+------+-----------------+-------------+------------+-----------------+-----------------+------+
| 1001 | John Liu        |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
| 1003 | Rita Ora        |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 |
| 1007 | Philip Lose     |   321345566 | 2001-06-15 | 2020-01-15      | 2024-06-15      |  3.8 |
| 1010 | Samantha Prabhu |  3217165566 | 2001-03-21 | 2020-01-15      | 2024-06-15      |  4.9 |
| 1011 | Vikas Jain      |   321345662 | 2001-07-18 | 2020-01-15      | NULL            |  3.3 |
| ...  | ...             |  ...        | ...        | ...             | ...             | ...  |
| 2017 | Potu Singh      |  1312445677 | 2003-03-11 | 2022-01-15      | NULL            | NULL |
+------+-----------------+-------------+------------+-----------------+-----------------+------+

Using the above data, we’ll create several queries demonstrating how to use the CASE statement with the greater-than operator.

3. Understanding the CASE Statement

The CASE statement enables us to perform complex calculations, create categories, and manage data presentation. We can use it within SELECT, WHERE, and ORDER BY clauses.

Additionally, we can write it in two forms: a simple CASE expression or a searched CASE expression. A simple CASE expression compares a single expression against multiple values. On the other hand, a searched CASE expression compares multiple conditions separately.

Let’s review the CASE statement’s syntax:

CASE 
    WHEN condition1 THEN result1 
    WHEN condition2 THEN result2
    ... 
    WHEN conditionN THEN resultN 
    ELSE default_result 
END

Above, each WHEN is followed by a condition. If the condition is true, we return the corresponding result. ELSE default_result specifies the value to return if none of the WHEN conditions are true.

Here, we’ll use the searched CASE expression since it allows us to use conditional logic, like a greater-than operator.

4. Grading Students Based on GPA

We can use the gpa of each student to determine their academic performance. By utilizing a CASE statement with the greater-than operator, we can categorize the students into performance groups such as High Achiever, Average, or Needs Improvement:

SELECT 
    id, 
    name, 
    gpa,
    CASE 
        WHEN gpa > 3.9 THEN 'High Achiever'
        WHEN gpa > 3.0 THEN 'Average'
        ELSE 'Needs Improvement'
    END AS gpa_category
FROM Student;

Let’s explain the above query:

• WHEN gpa > 3.9 THEN ‘High Achiever’ – checks if a student’s gpa is greater than 3.9, and if true, we assign a value of High Achiever to the gpa_category column of the result
• WHEN gpa > 3.0 THEN ‘Average’ – if the first condition is false, this condition checks if the student’s gpa is greater than 3.0 but not greater than 3.9, and if true, we assign the gpa_category a value of Average
• ELSE ‘Needs Improvement’ – assigns the gpa_category a value of Needs Improvement if the previous conditions evaluate to false

Using the above query, we evaluate each student’s gpa and categorize them into different groups, based on the conditions we specified in the CASE statement. We then display the results in a new column named gpa_category:

+------+-----------------+------+-------------------+
| id   | name            | gpa  | gpa_category      |
+------+-----------------+------+-------------------+
| 1001 | John Liu        | 4.00 | High Achiever     |
| 1003 | Rita Ora        | 4.20 | High Achiever     |
| 1007 | Philip Lose     | 3.80 | Average           |
| 1010 | Samantha Prabhu | 4.90 | High Achiever     |
| 1011 | Vikas Jain      | 3.30 | Average           |
| ...  | ...             | ...  | ...               |
| 1621 | Vineet Jha      | 2.90 | Needs Improvement |

The query works in MySQL, PostgreSQL, and SQL Server.

5. Determining Graduation Status

In this section, we’ll use the graduation_date column to determine whether a student has graduated or not. We’ll achieve this by using a CASE statement with the greater-than operator to compare the graduation date against the current date:

SELECT
    id,
    name,
    graduation_date,
    CASE
        WHEN graduation_date IS NOT NULL AND CURRENT_DATE > graduation_date THEN TRUE
        ELSE FALSE
    END AS graduated
FROM Student;

Let’s understand the above query:

• WHEN graduation_date IS NOT NULL AND CURRENT_DATE > graduation_date THEN TRUE – checks if the graduation_date is not null and the current date is after the graduation_date; if both conditions are true we set the graduated column to TRUE indicating that the student has already graduated
• ELSE FALSE – if the graduation_date is NULL or the current date is before the graduation_date the expression evaluates to FALSE indicating the student has not yet graduated

The query works in MySQL and PostgreSQL. We use the CURRENT_DATE function to get the current date. When we execute the query, it returns each student’s graduation status based on the graduation_date column:

+------+-----------------+-----------------+-----------+
| id   | name            | graduation_date | graduated |
+------+-----------------+-----------------+-----------+
| 1001 | John Liu        | 2024-06-15      |         1 |
| 1003 | Rita Ora        | 2024-06-15      |         1 |
| 1007 | Philip Lose     | 2024-06-15      |         1 |
| 1010 | Samantha Prabhu | 2024-06-15      |         1 |
| 1011 | Vikas Jain      | NULL            |         0 |
| ...  | ...             | ...             |       ... |
| 1607 | Peter Liu       | 2025-06-15      |         0 |

In MySQL, the boolean value TRUE is represented as 1 and FALSE as 0, while in PostgreSQL, we represent TRUE as t and FALSE as f.

In SQL Server, we’ll use the GETDATE() function to get the current date and time. To ensure we only compare the date, we use the CAST() function:

SELECT 
    id, 
    name, 
    graduation_date,
    CASE 
        WHEN graduation_date IS NOT NULL AND CAST(GETDATE() AS DATE) > graduation_date THEN 1
        ELSE 0
    END AS graduated
FROM Student;

Here, we use 1 to represent TRUE while 0 represents FALSE. Since SQL Server doesn’t have a dedicated boolean data type, it treats boolean-like values as integers.

6. Categorizing Students Into Age Groups

Additionally, we can categorize students into different age groups based on their approximate age. By calculating each student’s age from their birth_date column, we can group each student into approximate age ranges, such as 18-20, 21-23, or 24 and above. For demonstration purposes, we’ll only concern ourselves with the difference between the birth year and the current year.

6.1. MySQL

In MySQL, we’ll approximate each student’s age by subtracting the current year from the birth year. We’ll use the YEAR() function to get the current year and birth year:

SELECT 
    id, 
    name, 
    birth_date,
    CASE 
        WHEN YEAR(CURRENT_DATE) - YEAR(birth_date) > 23 THEN '24 and above'
        WHEN YEAR(CURRENT_DATE) - YEAR(birth_date) > 20 THEN '21-23'
        ELSE '18-20'
    END AS age_group
FROM Student;

Let’s understand the above query:

• WHEN YEAR(CURRENT_DATE) – YEAR(birth_date) > 23 THEN ’24 and above’ – sets student age_group to 24 and above if the student’s age is greater than 23
• WHEN YEAR(CURRENT_DATE) – YEAR(birth_date) > 20 THEN ’21-23′ – sets a student age_group to 21-23 if the student’s age is greater than 20
• ELSE ’18-20′ – set student’s age_group between 18-20 if the student’s age is below 20

Here’s the resulting output:

+------+-----------------+------------+-----------+
| id   | name            | birth_date | age_group |
+------+-----------------+------------+-----------+
| 1001 | John Liu        | 2001-04-05 | 21-23     |
| 1003 | Rita Ora        | 2001-01-14 | 21-23     |
| 1007 | Philip Lose     | 2001-06-15 | 21-23     |
| 1010 | Samantha Prabhu | 2001-03-21 | 21-23     |
| 1011 | Vikas Jain      | 2001-07-18 | 21-23     |
| ...  | ...             | ...        | ...       |

The above query categorizes each student into age groups based on their approximate age.

6.2. PostgreSQL

For PostgreSQL, we’ll use the EXTRACT() function to retrieve the current year and birth year of each student and subtract them to approximate the student’s age:

SELECT 
    id, 
    name, 
    birth_date,
    CASE 
        WHEN EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM birth_date) > 23 THEN '24 and above'
        WHEN EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM birth_date) > 20 THEN '21-23'
        ELSE '18-20'
    END AS age_group
FROM Student;

This query uses a CASE statement with the greater-than operator to determine the age group of each student based on their birth year.

6.3. SQL Server

To get the approximate age of each student in SQL Server, we’ll use the DATEDIFF() function to calculate the difference in years between the current date and birth date:

SELECT 
    id, 
    name, 
    birth_date,
    CASE 
        WHEN DATEDIFF(YEAR, birth_date, GETDATE()) > 23 THEN '24 and above'
        WHEN DATEDIFF(YEAR, birth_date, GETDATE()) > 20 THEN '21-23'
        ELSE '18-20'
    END AS age_group
FROM Student;

In this query, we use a CASE statement and the greater-than operator to assign an age group to each student based on their approximate age.

7. Conclusion

In this article, we discussed using the greater-than operator in CASE statements in SQL databases such as MySQL, PostgreSQL, and SQL Server. Furthermore, we demonstrated this by grading each student based on their GPA, determining their graduation status, and classifying students by their birth year.