CASE Statement in SQL: Enhance Your Database Queries

The SQL CASE Expression

SQL uses the CASE statement to handle IF/THEN logic. At least one set of WHEN and THEN statements—SQL's version of IF/THEN in Excel—come after the CASE statement. Although the traditional phrase is CASE, you could be tempted to call this SQL CASE WHEN because of this association.

The END statement must follow each CASE statement. The ELSE statement is optional for collecting values not provided in the WHEN/THEN commands.

SQL's CASE statement, a flexible conditional expression, helps us include conditional logic in our queries. It is frequently used to supply custom values, manage the output of our queries, and create new columns based on specific criteria.

Syntax

SELECT CASE
WHEN expression1 THEN Result1
WHEN expression2 THEN Result2
...ELSE Result
END

Discover SQL and Database Management

SQL Certification CourseExplore Program

SQL CASE Statement: Examples

Here are a few examples of SQL CASE statements:

Example 1:

Consider the following scenario: For a Christmas sale, you would like to offer a 30% discount on all orders if the total is more than 1,000 or more.

SELECT order_id, customer_id,
CASE
WHEN amount >= 1000 THEN amount - (amount * 30/100)
END AS offer_price
FROM Orders;

In this case, the CASE statement determines whether the sum equals or exceeds 1000. If these criteria are met, the value amount—(amount * 30/100) will appear in a new column offering price.

Also Read: Conditional Statements in SQL

Example 2:

Consider the following scenario: You want to categorize your workforce according to their salaries - Low, Medium, and High.

SELECT
employee_id,
name,
salary,
department,
CASE
WHEN salary < 25000 THEN 'Low'
WHEN salary BETWEEN 25000 AND 60000 THEN 'Medium'
ELSE 'High'
END AS salary_category
FROM Employees;

In this case, if the employee's salary is less than 25,000, the category is ‘Low’; if it is between 25,000 and 60,000, the category is ‘Medium’; and if it is higher than 60,000, the category is ‘High’.

Example 3:

Consider the scenario: You want to assign the value of CA as Chartered Accountant and CTO as Chief Technology Officer.

SELECT customer_id, first_name,
CASE
WHEN occupation = 'CA' THEN 'Chartered Accountant'
WHEN occupation = 'CTO' THEN 'Chief Technology Officer'
ELSE 'Unknown Occupation'
END AS occupation_name
FROM Customers;

In this case, The value of occupation_name becomes Chartered Accountant if the occupation is CA, Chief Technology Officer if the occupation is CTO and Unknown Occupation if the occupation is neither CA nor CTO (because of the ELSE clause).

CASE Statement Formats

In SQL, the CASE statement yields a value under certain conditions. You can combine a Where, Order By, and Group By clause with a CASE statement in specific queries. It is also applicable to the Insert statement.

Simple CASE Statement Expression

You compare one expression to several values in this format. It assesses each condition individually in a straightforward CASE statement. The expression specified in the THEN clause is returned once the condition and expression match.

Syntax

CASE
WHEN ComparsionCondition THEN result
WHEN ComparsionCondition THEN result
ELSE other
END

Obtain Your SQL Certification

SQL Certification CourseExplore Program

CASE Statement and Comparison Operator

You can use comparison operators to evaluate a condition in this syntax of a CASE in SQL. After this condition is met, you receive an expression from matching THEN in the output. Several conditions can be assessed using comparison operators in a CASE statement. The CASE statement can be used with comparison operators such as =, \, >, \=, >=, and \> (not equal).

Syntax

CASE
WHEN ComparsionCondition THEN result
WHEN ComparsionCondition THEN result
ELSE other
END

CASE Statement with ORDER BY Clause

The result can be sorted in ascending or descending order using the ORDER BY clause. You can improve your ORDER BY by adding a CASE statement, enabling you to sort the results according to additional (many) criteria.

It can be used, for instance, to sort string values based on factors other than alphabetical order, like the hierarchical positions of job titles within an organization. You can also sort data based on several criteria using a CASE statement.

Syntax

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    department_id
FROM 
    employees
ORDER BY 
    CASE 
        WHEN department_id = 1 THEN 'A'  -- Highest priority sorting
        WHEN department_id = 2 THEN 'B'  -- Second priority sorting
        ELSE 'C'  -- Default sorting for all other departments
    END,
    last_name ASC;  -- Secondary sorting by last name within the same department

CASE Statement in SQL with GROUP BY Clause

The GROUP BY clause with a CASE statement can aggregate and group data according to conditional expressions. You can divide a table into sets using the GROUP BY clause. The most common combination for this clause is with aggregate functions that generate summary values for each set.

Syntax

SELECT
    CASE
        WHEN region IN ('New York', 'Boston', 'Chicago') THEN 'North'
        WHEN region IN ('Dallas', 'Houston', 'Miami') THEN 'South'
        ELSE 'Other'
    END AS region_group,
    COUNT(*) AS total_sales,
    SUM(sale_amount) AS total_amount
FROM
    sales
GROUP BY
    CASE
        WHEN region IN ('New York', 'Boston', 'Chicago') THEN 'North'
        WHEN region IN ('Dallas', 'Houston', 'Miami') THEN 'South'
        ELSE 'Other'
    END;

Ready to level up your career in database management? Our SQL Certification Course is your gateway to mastering essential skills. Enroll today and join thousands of professionals who have elevated their careers!

UPDATE Statement with CASE Statement

The SQL UPDATE statement can change the column value or combination of column values of a specific row or row. The SQL UPDATE command modifies the records already in a table.

A SQL UPDATE statement can change a table's data in one of two ways: it can update the value of the selected row or the value of all rows.

Syntax

UPDATE employees
SET salary = salary * 
    CASE 
        WHEN department_id = 1 THEN 1.10  -- 10% raise
        WHEN department_id = 2 THEN 1.05  -- 5% raise
        ELSE 1.02  -- 2% raise for all others
    END;

INSERT Statement with CASE statement

Data can be inserted into a SQL Server table using the CASE expression. When the necessary values are obtained, the INSERT statement with CASE will be inserted from the THEN expression. After searching for the required values, the INSERT statement using CASE will insert the values from the THEN expression.

Syntax

INSERT INTO orders (order_id, customer_id, order_date, order_priority)
VALUES 
(
    101, 
    5, 
    '2024-09-03', 
    CASE 
        WHEN 1200 > 1000 THEN 'High'  -- Example order_amount is 1200
        WHEN 1200 BETWEEN 500 AND 1000 THEN 'Medium'
        ELSE 'Low'
    END
);

CASE Statement Limitations

• The CASE statement is limited to returning a single value or expression. It cannot return multiple columns or perform various operations within a single CASE block.
• CASE statements are purely for conditional evaluation and cannot be used to perform procedural logic like loops or complex conditional flows. It's not a substitute for programming constructs found in procedural languages.
• While CASE can be used within SELECT, UPDATE, DELETE, and INSERT statements, it cannot perform complex operations like subqueries, joins, or calls to stored procedures within the CASE logic.
• A CASE statement containing many complex conditions can negatively impact query performance, especially in large datasets. Overuse of CASE within complex queries can lead to slower execution times.
• Unlike some programming languages, SQL CASE statements do not support short-circuit evaluation. All conditions are evaluated, even if an earlier condition in the CASE block is valid. This can result in unnecessary computation.

Start Your Journey to Data Mastery

SQL Certification CourseExplore Program

Conclusion

The CASE statement on the SQL adds versatility to the SQL queries. We implemented the CASE statement with several other keywords. To learn more about SQL, check out the SQL Certification Course. This certification helps you get the basics right and the practical training you need to work with SQL databases and use them in the applications you use or create.

On completing the course, you will have a work-ready understanding of how to structure your database correctly, create error-free and efficient SQL statements and clauses, and design and manage your SQL databases for scalable growth.

FAQs

1. How to check multiple conditions in CASE statement in SQL?

Logical operators such as AND, OR, and WHERE in SQL allow you to use a CASE statement to verify multiple conditions. This enables you to manage more complicated situations where determining the outcome requires analyzing multiple conditions.

2. Why is the CASE statement useful in SQL?

In SQL, the CASE expression evaluates conditions and returns a value when the first condition is met. It enables you to apply various logic depending on particular query circumstances.

3. Is the CASE statement available in all SQL databases?

The CASE statement is available in all SQL databases, including MySQL, PostgreSQL, SQL Server, SQLite and Oracle.

4. How does the CASE statement differ from IF statements in SQL?

If you want to assess something to a TRUE/FALSE condition, the IF statement can be helpful. You utilize the CASE statement when you have more than one possible condition. Although it becomes complex, you can achieve the same result with nested IF statements.

5. Can the CASE statement be nested in SQL?

CASE statements in SQL can be nested within another CASE statement, which is beneficial when multiple sub-conditions need to be examined. Initially, the database verifies the state column in a nested CASE statement.