The SQL Case Statement

In application development involving data processing, database structure, and data manipulation, are crucial factors. One important aspect of data processing is the ability to perform conditional operations. SQL (Structured Query Language) is a commonly used programming language for interacting with databases. One powerful feature in SQL is the Case Statement, which allows users to perform flexible and complex conditional operations. In this article, we will explore the basic concepts and usage of the Case Statement in SQL.

The ‘CASE’ statement in SQL is used to handle conditional logic similar to ‘IF’ statements. The function of the ‘CASE’ statement is to return a value based on fulfilled conditions. The ‘CASE’ statement is followed by at least one pair of ‘WHEN’ and ‘THEN’ statements. The ‘WHEN’ statement specifies the condition to be tested, while the ‘THEN’ statement specifies the command to be executed if the condition in the ‘WHEN’ statement is satisfied. This function is similar to the ‘IF-THEN’ function in Excel.

SQL CASE Syntax:

SELECT column1, column2,…,

CASE

WHEN condition THEN result

END AS Alias

FROM table;

Explanation :

- column1, column2,… are the column names to be displayed in the terminal.

- CASE is used for condition checking.

- condition is the condition statement.

- result is the outcome to be entered into a new column if the ‘condition’ is satisfied.

- END is used to end the CASE statement.

- AS Alias is used to give an alias name to the new column formed by the CASE statement.

- table is the selected table name.

- The CASE syntax must start with CASE and end with END, followed by the assignment of the alias column name.

Example 1 (Simple Case Statement):

Here is a simple example of the CASE statement function.

SELECT Name,
       IndepYear,
       CASE WHEN IndepYear > 1900 THEN 'Yes'
            ELSE 'No' END AS 'Year > 1900'
FROM country

Result :

Example 1 Result

Explanation:

In this code, a condition is given for the IndepYear variable. If it is greater than 1900, the output will be ‘Yes’; otherwise, it will be ‘No’.

Example 2 (Multiple Case Statement):

Here is an example with multiple conditions in the CASE statement.

SELECT Name,
       IndepYear,
       CASE WHEN IndepYear >= 1900 AND IndepYear < 2000 THEN '20th Century'
            WHEN IndepYear >= 1800 AND IndepYear < 1900 THEN '19th Century'
            WHEN IndepYear >= 1700 AND IndepYear < 1800 THEN '18th Century'
            ELSE 'Besides 18-20th Century' END AS 'Indep Year'
FROM country;

Result :

Example 2 Result

In this code, the following conditions are given:

• First condition: If the value of IndepYear is greater than or equal to 1900 and less than 2000, the output will be ‘20th Century’.
• Second condition: If the value of IndepYear is greater than or equal to 1800 and less than 1900, the output will be ‘19th Century’.
• Third condition: If the value of IndepYear is greater than or equal to 1700 and less than 1800, the output will be ‘18th Century’.
• Fourth condition: For any other conditions, the output will be ‘Besides 18–20th Century’.