Case When SQL

Galaxy Glossary

How can I conditionally return different values based on conditions in SQL?

The CASE WHEN statement in SQL allows you to perform conditional logic within a query. It's a powerful tool for returning different values based on various conditions. It's crucial for creating dynamic and flexible queries.

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

The CASE WHEN statement is a fundamental SQL construct for conditional logic. It enables you to return different values based on the evaluation of conditions within a query. This is particularly useful for transforming data or creating calculated fields based on specific criteria. Imagine you have a table of customer orders, and you want to categorize them as 'High-Value', 'Medium-Value', or 'Low-Value' based on the order total. The CASE WHEN statement makes this straightforward. It's a versatile tool that can be used in SELECT, UPDATE, and other SQL statements. The basic structure involves a series of WHEN conditions followed by a THEN clause specifying the value to return if the condition is true. A final ELSE clause (optional) handles cases where none of the WHEN conditions are met. This allows for complex conditional logic within a single query, enhancing the flexibility and power of your SQL operations.

Why Case When SQL is important

The CASE WHEN statement is crucial for data manipulation and analysis. It allows for dynamic data transformations, enabling complex queries that adapt to various conditions. This flexibility is essential for reporting, data analysis, and creating custom business logic within SQL.

Example Usage


-- Sample table: Customers
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    OrderTotal DECIMAL(10, 2)
);

INSERT INTO Customers (CustomerID, FirstName, LastName, OrderTotal)
VALUES
(1, 'John', 'Doe', 100.00),
(2, 'Jane', 'Smith', 50.00),
(3, 'Peter', 'Jones', 200.00),
(4, 'Mary', 'Brown', 25.00);

-- Query to categorize orders
SELECT
    CustomerID,
    FirstName,
    LastName,
    OrderTotal,
    CASE
        WHEN OrderTotal > 150 THEN 'High-Value'
        WHEN OrderTotal BETWEEN 50 AND 150 THEN 'Medium-Value'
        ELSE 'Low-Value'
    END AS OrderCategory
FROM
    Customers;

Common Mistakes

Forgetting the END keyword at the end of the CASE statement.
Using incorrect comparison operators (e.g., using = instead of <=> for NULL comparisons).
Omitting the ELSE clause, which can lead to unexpected results if no WHEN condition is met.

Case When SQL

How can I conditionally return different values based on conditions in SQL?

Description

Why Case When SQL is important

Example Usage

Common Mistakes

Want to learn about other SQL terms?

With Cte In SQL

Where Vs Having SQL

Windows Functions In SQL