SQL CASE Statement

The CASE statement in SQL is a versatile conditional expression that enables us to incorporate conditional logic directly within our queries. It allows you to return specific results based on certain conditions, enabling dynamic query outputs. Whether you need to create new columns, modify existing ones, or customize the output of your queries, the CASE statement can handle it all.

In this article, we'll learn the SQL CASE statement in detail, with clear examples and use cases that show how to leverage this feature to improve your SQL queries.

CASE Statement in SQL

• The CASE statement in SQL is a conditional expression that allows you to perform conditional logic within a query.
  
• It is commonly used to create new columns based on conditional logic, provide custom values, or control query outputs based on certain conditions.
  
• If no condition is true then the ELSE part will be executed. If there is no ELSE part then it returns NULL.

Syntax:

To use CASE Statement in SQL, use the following syntax:

CASE case_value
WHEN condition THEN result1
WHEN condition THEN result2
...
Else result
END CASE;

Example of SQL CASE Statement

Let's look at some examples of the CASE statement in SQL to understand it better.

Let's create a demo SQL table, which will be used in examples.

Demo SQL Database

We will be using this sample SQL table for our examples on SQL CASE statement:

You can create the same Database in your system, by writing the following MySQL query:

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');

Example 1: Simple CASE Expression

In this example, we use CASE statement

Query:

SELECT CustomerName, Age,
CASE
    WHEN Country = "India" THEN 'Indian'
    ELSE 'Foreign'
END AS Nationality
FROM Customer;

Output:

Example 2: SQL CASE When Multiple Conditions

We can add multiple conditions in the CASE statement by using multiple WHEN clauses.

Query:

SELECT CustomerName, Age,
CASE
    WHEN Age> 22 THEN 'The Age is greater than 22'
    WHEN Age = 21 THEN 'The Age is 21'
    ELSE 'The Age is over 30'
END AS QuantityText
FROM Customer;

Output:

Example 3: CASE Statement With ORDER BY Clause

Let's take the Customer Table which contains CustomerID, CustomerName, LastName, Country, Age, and Phone. We can check the data of the Customer table by using the ORDER BY clause with the CASE statement.

Query:

SELECT CustomerName, Country
FROM Customer
ORDER BY
(CASE
    WHEN Country  IS 'India' THEN Country
    ELSE Age
END);

Output:

Important Points About CASE Statement

• The SQL CASE statement is a conditional expression that allows for the execution of different queries based on specified conditions.
  
• There should always be a SELECT in the CASE statement.
• END ELSE is an optional component but WHEN THEN these cases must be included in the CASE statement.
  
• We can make any conditional statement using any conditional operator (like WHERE ) between WHEN and THEN. This includes stringing together multiple conditional statements using AND and OR.
  
• We can include multiple WHEN statements and an ELSE statement to counter with unaddressed conditions.

Conclusion

The CASE statement provides a robust mechanism for incorporating conditional logic in SQL queries. By using this statement, you can handle various conditions and customize the output of your queries effectively. Understanding how to implement CASE expressions allows you to perform more sophisticated data manipulation and reporting, making your SQL queries more dynamic and responsive to different scenarios.