SQL CASE Expression

The SQL CASE expression is a control flow statement that allows you to add conditional logic to your SQL queries. It's similar to the if-else statements found in many programming languages. The CASE expression goes through conditions and returns a value when the first condition is met. If no conditions are true, it returns a default value.

Here’s the general syntax:

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

Let's look at some examples to better understand how the CASE expression works.

Example 1: Basic CASE Expression

Suppose you have a table Employees with columns EmployeeID, Name, and DepartmentID. You want to create a report that shows each employee's department name instead of the department ID.

SELECT 
    EmployeeID, 
    Name,
    DepartmentID,
    CASE 
        WHEN DepartmentID = 1 THEN 'HR'
        WHEN DepartmentID = 2 THEN 'Finance'
        WHEN DepartmentID = 3 THEN 'Engineering'
        ELSE 'Other'
    END AS DepartmentName
FROM Employees;

Output:

EmployeeID Name DepartmentID DepartmentName
1 Alice 1 HR
2 Bob 3 Engineering
3 Charlie 2 Finance
4 David 4 Other

Example 2: CASE Expression with Aggregate Functions

Suppose you have a table Orders with columns OrderID, CustomerID, OrderDate, and Amount. You want to categorize each order as 'Small', 'Medium', or 'Large' based on the Amount.

SELECT 
    OrderID,
    CustomerID,
    Amount,
    CASE 
        WHEN Amount < 100 THEN 'Small'
        WHEN Amount BETWEEN 100 AND 500 THEN 'Medium'
        ELSE 'Large'
    END AS OrderSize
FROM Orders;

Output:

OrderID CustomerID Amount OrderSize
101 1 50 Small
102 2 300 Medium
103 1 700 Large
104 3 150 Medium

Example 3: Nested CASE Expression

Suppose you have a table Sales with columns SaleID, ProductID, Quantity, and SaleDate. You want to determine the sales performance for each product based on the quantity sold.

SELECT 
    SaleID,
    ProductID,
    Quantity,
    CASE 
        WHEN Quantity < 10 THEN 'Low'
        WHEN Quantity BETWEEN 10 AND 50 THEN
            CASE 
                WHEN Quantity < 30 THEN 'Medium'
                ELSE 'High-Medium'
            END
        ELSE 'High'
    END AS Performance
FROM Sales;

Output:

SaleID ProductID Quantity Performance
1 101 5 Low
2 102 25 Medium
3 103 40 High-Medium
4 104 60 High

Example 4: CASE Expression with GROUP BY

Suppose you have a table Products with columns ProductID, ProductName, and Price. You want to categorize the products into different price ranges and get the count of products in each category.

SELECT 
    CASE 
        WHEN Price < 50 THEN 'Cheap'
        WHEN Price BETWEEN 50 AND 150 THEN 'Affordable'
        ELSE 'Expensive'
    END AS PriceRange,
    COUNT(*) AS ProductCount
FROM Products
GROUP BY 
    CASE 
        WHEN Price < 50 THEN 'Cheap'
        WHEN Price BETWEEN 50 AND 150 THEN 'Affordable'
        ELSE 'Expensive'
    END;

Output:

PriceRange ProductCount
Cheap 10
Affordable 20
Expensive 5

These examples demonstrate the versatility and power of the CASE expression in SQL for implementing conditional logic directly in your queries.



twitter-white sharing button
keyboard_arrow_leftPrevious
Nextkeyboard_arrow_right

Online Learner

At Online Learner, we're on a mission to ignite a passion for learning and empower individuals to reach their full potential. Founded by a team of dedicated educators and industry experts, our platform is designed to provide accessible and engaging educational resources for learners of all ages and backgrounds.

twitter-white sharing button

Disclaimer About Us

Copyright 2023-2024 © All rights reserved.

logo
Frontend
Backend