SQL Case Statement Explained with Examples

Structured Query Language, or SQL, is the main language used to communicate with databases in the field of database management. The SQL CASE statement, one of its many potent capabilities, gives SQL queries the ability to execute conditional logic. The syntax, use cases, and practical applications of the SQL CASE statement will all be covered in detail in this article.

Overview

As a conditional expression, the SQL CASE statement enables developers to carry out various operations in response to predefined circumstances. It works similarly to other programming languages' IF...ELSE statement. The SQL CASE statement has the following fundamental syntax:

SELECT column1, column2,... , 

CASE 

  WHEN condition THEN result

END AS alias_name

FROM table;

The CASE statement in this form assesses each condition one after the other. The matching result is returned if a condition evaluates to true. If none of the requirements are satisfied, the default outcome, if any, is given back.

The SQL Case Statement Syntax

Let’s break down the SQL Case statement syntax:

SELECT column1, column2,... , 

CASE 

  WHEN condition THEN result

END AS alias_name

FROM table;

• The column1,column2, ...: These column names are to be included in the results.
• The case: It checks the specified condition.
• Table: It represents the name of the table.
• AS gives the name alias_name for the new column. 
• If the condition is met, the result is the value to be placed into the new column. 
• END terminates the CASE statement.
  

CASE Statement Parameters

The following are the parameters or parts of the CASE SQL statement:

• expression (optional): The CASE statement searches for this expression. This is the check carried out inside the IF statement if we are comparing this to an IF statement (for example, if x > 10, the expression would be "x > 10").
• condition_1/condition_n(Required): These numbers are the outcome of the specified expression parameter. These are the potential values that the expression may assess. As an alternative, they can be an expression by themselves because an SQL CASE statement can be written in two different methods, which are detailed below. They also have to do with the IF statement in the form IF-THEN-ELSE.
• result_1/result_n (required): If the associated condition is met, these values will be shown. They relate to the THEN section of the IF-THEN-ELSE structure and appear after the THEN keyword.
• result (optional): If the CASE statement's conditions are false, this is the value that should be shown. It is the ELSE component of the IF-THEN-ELSE structure, and the CASE SQL statement can function without it.
• case_name (not required): This value specifies the name by which the column should be referred to in a subquery or when it is presented on the screen. Another name for it is the column alias.
  

SQL CASE Statement Example

Here is a sample from the sample database's ‘Customer’ table:

CREATE TABLE Customer(

    CustomerID INT PRIMARY KEY,

    CustomerName VARCHAR(50),

    LastName VARCHAR(50),

    Country VARCHAR(50),

    Age int(2),

  Phone int(10)

);

-- Insert some sample data into the Customers table

INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)

VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),

       (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),

       (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),

       (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),

       (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

The Output:

The SQL CASE Statement in WHERE Clause

It is true that the SQL CASE statement, which is usually used in conjunction with SELECT queries to alter result sets, may also be used to filter data according to certain requirements in the WHERE clause. This feature gives developers a strong tool to create more complex and customized queries.

The SQL CASE statement functions similarly within the WHERE clause. However, it filters rows according to conditions instead of altering the obtained data.

Filtering Rows with SQL CASE Statement in WHERE Clause:

To filter rows using a SQL CASE statement in the WHERE clause based on a specific condition, let's say we want to retrieve customers from India and Australia only, we can use the following code:

SELECT *

FROM Customer

WHERE Country IN (

    SELECT CASE

               WHEN Country = 'India' THEN 'India'

               WHEN Country = 'Australia' THEN 'Australia'

               ELSE ''

           END

);

This query will select all rows from the 'Customer’ table where the country is either 'India' or 'Australia'. It utilizes a CASE statement within the WHERE clause to filter rows based on the specified conditions.

SQL CASE Statement in WHERE Clause Multiple Values

To filter rows using a SQL CASE statement in the WHERE clause based on multiple values, let's say we want to retrieve customers from India, Australia, and Sri Lanka, we can use the following code:

SELECT *

FROM Customer

WHERE Country IN (

    SELECT CASE

               WHEN Country = 'India' THEN 'India'

               WHEN Country = 'Australia' THEN 'Australia'

               WHEN Country = 'Sri Lanka' THEN 'Sri Lanka'

               ELSE ''

           END

);

This query will select all rows from the ‘Customer’ table where the country is 'India', 'Australia', or 'Sri Lanka'. It utilizes a CASE statement within the WHERE clause to filter rows based on the specified conditions with multiple values.

Adding SQL CASE Statement with Multiple Conditions

Developers can successfully manage complex cases by allowing for more sophisticated decision-making within queries, by adding numerous conditions to an SQL CASE statement. The adaptability and usefulness of a CASE statement are increased when numerous conditions are included in it. Examples of these uses include classifying data according to different criteria and creating conditional logic with multiple branches.

Syntax Overview:

An SQL CASE statement with multiple conditions follows the same general structure as one with a single condition. Every condition is assessed one after the other, and when one evaluates to true, the associated result is given back. Here’s the syntax:

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    ...

    ELSE default_result

END

Handling Multiple Conditions

To filter rows using a SQL CASE statement in the WHERE clause with multiple conditions, let's say we want to retrieve customers aged 21 or younger from India and customers aged 22 or older from Australia. We can use the following code to obtain the result:

SELECT *

FROM Customer

WHERE CASE

        WHEN Country = 'India' AND Age <= 21 THEN 1

        WHEN Country = 'Australia' AND Age >= 22 THEN 1

        ELSE 0

      END = 1;

This query will select all rows from the ‘Customer’ table where the specified conditions are met: customers aged 21 or younger from India and customers aged 22 or older from Australia. It utilizes a CASE statement within the WHERE clause to filter rows based on multiple conditions.

Combining Conditions

Another common scenario is combining conditions within a single branch of the CASE statement. To filter rows using a SQL CASE statement in the WHERE clause by combining conditions, let's say we want to retrieve customers aged 21 or younger from India To obtain our result, we can use the following code:

SELECT *

FROM Customer

WHERE CASE

        WHEN (Country = 'India' AND Age <= 21) OR (Country = 'Australia' AND Age >= 22) THEN 1

        ELSE 0

      END = 1;

This query will select all rows from the ‘Customer’ table where the specified conditions are met: customers aged 21 or younger from India and customers aged 22 or older from Australia. It utilizes a CASE statement within the WHERE clause to filter rows by combining multiple conditions.

Real-World Application

Let's say, you have a dataset of customer transactions and you wish to divide up your clientele into groups according to the frequency and total amount of their purchases. To do this, you can use a SQL CASE statement with several conditions:

SELECT customer_id,

       CASE

           WHEN total_purchases < 100 AND purchase_frequency < 5 THEN 'Low-Spending, Infrequent'

           WHEN total_purchases >= 100 AND purchase_frequency < 5 THEN 'High-Spending, Infrequent'

           WHEN total_purchases < 100 AND purchase_frequency >= 5 THEN 'Low-Spending, Frequent'

           WHEN total_purchases >= 100 AND purchase_frequency >= 5 THEN 'High-Spending, Frequent'

           ELSE 'Other'

       END AS customer_segment

FROM customers;

Output:

Customers are grouped in this scenario according to their overall purchase amount and the frequency of those purchases, which offers important insights into their purchasing habits.

CASE Statement with ORDER BY Clause

Consider the ‘Customer’ Table, which includes the following fields: Phone, Age, Country, Last Name, CustomerID, and CustomerName. The SQL query that follows can be used to examine the data in the ‘Customer’ table:

Query:

SELECT CustomerName, Country

FROM Customer

ORDER BY

(CASE

    WHEN Country  IS 'India' THEN Country

    ELSE Age

END);

Output:

A Few Crucial Details Regarding CASE Statements

• The case statement must always include a SELECT.
• While WHEN THEN examples need to be included in the CASE statement, END ELSE is an optional component.
• We may use any conditional operator (such as WHERE) to create any kind of conditional statement between WHEN and THEN. This involves the use of AND and OR to join several conditional statements together.
• To counter ignored situations, we can use numerous WHEN statements and an ELSE statement.
  

Best Practices for SQL CASE Statements

• A sequential approach is used to process the several conditions in the CASE statements. After the first successful condition, it ceases to check conditions.
• It is preferable to utilize the ELSE block in CASE statements to obtain the default value if none of the conditions are met.
• Null values in a table cannot be checked using the CASE statement.
• It is advised to stay away from contradicting circumstances.
  

Final Words

With the help of the flexible SQL CASE statement, developers may incorporate conditional logic into SQL queries. Developers can build more effective and expressive SQL code by comprehending the syntax and many use cases of the SQL CASE statement, which can be applied to filtering or data manipulation. This article has offered a thorough tutorial for understanding the SQL CASE statement by integrating real-world examples. Begin utilizing its potential in your SQL queries right now!

FREQUENTLY ASKED QUESTIONS

1. What is the CASE statement in SQL for string?

Ans: The CASE statement in SQL for strings allows you to perform conditional logic based on string values. It evaluates a series of conditions and returns a result depending on which condition is true.

2. How do I run a query in a CASE statement in SQL?

Ans: To run a query using a CASE statement in SQL, you include the CASE statement within your SELECT query, specifying the conditions and corresponding results you want.

3. Can we write multiple conditions in a CASE statement in SQL?

Ans: Yes, you can write multiple conditions in a CASE statement in SQL by chaining together multiple WHEN clauses. Each WHEN clause represents a separate condition to evaluate.

4. How many types of CASE statements are there in SQL?

Ans: There are two types of CASE statements in SQL: simple CASE and searched CASE. Simple CASE compares an expression to a set of simple values, while searched CASE evaluates a set of Boolean conditions.

5. Can we use null in CASE statement SQL?

Ans: Yes, you can use NULL in a CASE statement in SQL. You can specify NULL as a condition or as a result value in a CASE statement.

6. How do you write two CASE statements in SQL?

Ans: To write two CASE statements in SQL, you include them within your SELECT query, each with its own set of conditions and results.

7. Is a CASE statement a SELECT query?

Ans: No, a CASE statement is not a SELECT query by itself. It is a conditional expression that can be used within a SELECT query to determine the value of a column or expression based on specified conditions.

8. Can you use it in a CASE statement?

Ans: Yes, you can use a CASE statement within another CASE statement in SQL, allowing for nested conditional logic if needed.

9. What are the limitations of CASE statements in SQL?

Ans: The limitations of CASE statements in SQL include their inability to perform complex procedural logic, such as loops or variable assignments. Additionally, CASE statements can become unwieldy and difficult to read if they contain too many conditions.

10. Can we use aggregate functions in CASE statements in SQL?

Ans: Yes, you can use aggregate functions within CASE statements in SQL. This allows you to perform conditional aggregation based on specified conditions. For example, you can use SUM, COUNT, AVG, etc., within a CASE statement to aggregate data conditionally.