Learn SQL CASE Statement: Simple Tutorial

By /

Learn SQL CASE Statement: A Simple Tutorial

The CASE statement in SQL is a powerful tool that allows you to implement conditional logic within your queries. Think of it as an if-then-else statement for your SQL code. It enables you to return different values based on specific conditions, making your queries more dynamic and flexible. This tutorial will guide you through the basics of the CASE statement, covering both its forms (simple and searched), and providing practical examples to solidify your understanding.

Two Forms of the CASE Statement

The CASE statement comes in two primary forms:

  1. Simple CASE: This form compares an expression to a set of simple values.
  2. Searched CASE: This form evaluates a set of Boolean expressions (conditions).

Let’s explore each in detail.

1. Simple CASE Statement

The simple CASE statement compares a single expression (the “case expression”) to multiple values. It’s best used when you’re checking for equality against a specific set of possibilities.

Syntax:

sql
CASE case_expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
[ELSE else_result]
END

  • case_expression: The expression whose value will be compared. This can be a column name, a literal value, or a more complex expression.
  • WHEN value1 THEN result1: If the case_expression is equal to value1, then result1 is returned. You can have multiple WHEN clauses.
  • ELSE else_result: (Optional) If none of the WHEN conditions match, the else_result is returned. If no ELSE clause is present and none of the WHEN conditions match, NULL is returned.
  • END: Marks the end of the CASE statement. It’s essential to include the END keyword.

Example: Categorizing Order Sizes

Suppose you have a table named Orders with columns like OrderID, CustomerID, and TotalAmount. You want to categorize orders as “Small”, “Medium”, or “Large” based on the TotalAmount.

sql
SELECT
OrderID,
TotalAmount,
CASE TotalAmount
WHEN 100 THEN 'Small'
WHEN 200 THEN 'Medium'
WHEN 300 THEN 'Large'
ELSE 'Unknown'
END AS OrderSizeCategory
FROM Orders;

In this example:

  • TotalAmount is the case_expression.
  • The WHEN clauses check if TotalAmount is equal to 100, 200, or 300.
  • If TotalAmount is 100, ‘Small’ is returned. If it’s 200, ‘Medium’ is returned, and so on.
  • If TotalAmount doesn’t match any of the WHEN values (e.g., it’s 150 or 400), ‘Unknown’ is returned due to the ELSE clause.
  • The result of the CASE statement is given the alias OrderSizeCategory.

Another Example: Mapping Numeric Codes to Text

sql
SELECT
ProductID,
ProductStatus, -- Assume ProductStatus is a numeric code (1=Active, 2=Discontinued, 3=Pending)
CASE ProductStatus
WHEN 1 THEN 'Active'
WHEN 2 THEN 'Discontinued'
WHEN 3 THEN 'Pending'
ELSE 'Unknown Status'
END AS StatusDescription
FROM Products;

This example translates numeric ProductStatus codes into more readable text descriptions.

2. Searched CASE Statement

The searched CASE statement is more flexible than the simple CASE. It allows you to evaluate different Boolean expressions (conditions) in each WHEN clause, rather than just checking for equality.

Syntax:

sql
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
[ELSE else_result]
END

  • WHEN condition1 THEN result1: If condition1 is TRUE, then result1 is returned. condition1 can be any valid SQL Boolean expression (e.g., TotalAmount > 100, CustomerID IS NOT NULL, ProductName LIKE '%Widget%').
  • ELSE else_result: (Optional) If none of the WHEN conditions are TRUE, the else_result is returned. As with the simple CASE, if no ELSE is present, NULL is returned.
  • END: Marks the end of the CASE statement.

Example: Categorizing Order Sizes (More Flexible)

Let’s revisit the order categorization example, but this time use the searched CASE to define ranges:

sql
SELECT
OrderID,
TotalAmount,
CASE
WHEN TotalAmount < 100 THEN 'Small'
WHEN TotalAmount >= 100 AND TotalAmount < 200 THEN 'Medium'
WHEN TotalAmount >= 200 THEN 'Large'
ELSE 'Unknown'
END AS OrderSizeCategory
FROM Orders;

Here:

  • Each WHEN clause has a different condition.
  • The first WHEN checks if TotalAmount is less than 100.
  • The second WHEN checks if TotalAmount is between 100 (inclusive) and 200 (exclusive). Note the use of AND to combine conditions.
  • The third WHEN checks if TotalAmount is 200 or greater.
  • The ELSE clause handles any cases not covered by the WHEN conditions.

Example: Applying Discounts Based on Customer Type and Order Total

sql
SELECT
CustomerID,
OrderTotal,
CustomerType, -- Assume CustomerType is 'Regular', 'VIP', or 'New'
CASE
WHEN CustomerType = 'VIP' THEN OrderTotal * 0.9 -- 10% discount for VIPs
WHEN CustomerType = 'New' AND OrderTotal > 50 THEN OrderTotal * 0.95 -- 5% discount for new customers on orders over $50
ELSE OrderTotal -- No discount
END AS DiscountedTotal
FROM Orders;

This example demonstrates applying different discounts based on customer type and order total, illustrating the power of combining conditions within the CASE statement.

CASE Statement within Other SQL Clauses

You can use the CASE statement not only in the SELECT list but also in other clauses like WHERE, ORDER BY, GROUP BY, and HAVING.

Example: Filtering with WHERE

sql
SELECT
ProductID,
ProductName,
UnitPrice
FROM Products
WHERE
CASE
WHEN UnitPrice > 100 THEN 1 -- Consider expensive products
WHEN UnitPrice <= 100 THEN 0 -- Consider non-expensive products
ELSE NULL -- Handle cases where UnitPrice is NULL (exclude these)
END = 1; -- Only select expensive products (where the CASE returns 1)
This filters the Products table to show only products with UnitPrice greater than 100, using a CASE statement within the WHERE clause.

Example: Ordering with ORDER BY

sql
SELECT
CustomerID,
CustomerName,
CustomerType
FROM Customers
ORDER BY
CASE CustomerType
WHEN 'VIP' THEN 1 -- VIPs first
WHEN 'New' THEN 2 -- New customers second
WHEN 'Regular' THEN 3 -- Regular customers last
ELSE 4 -- All other types last
END;

This example sorts the Customers table based on CustomerType, prioritizing VIPs, then New customers, then Regular customers.

Key Considerations and Best Practices

  • Readability: Use clear and concise conditions in your WHEN clauses. Proper indentation and comments can significantly improve readability, especially with complex CASE statements.
  • ELSE Clause: Always consider including an ELSE clause to handle unexpected or default cases. This prevents unexpected NULL values and makes your logic more robust.
  • Performance: While CASE statements are powerful, overly complex or deeply nested CASE statements can sometimes impact query performance. If performance is critical, consider alternatives like creating lookup tables or using stored procedures.
  • Data Types: Ensure that the results of all THEN and ELSE clauses have compatible data types. For example, you can’t return a string in one THEN clause and a number in another without explicit type conversion.
  • Order of WHEN: The order of when clauses matters, especially in the searched CASE Statement, the first condition met will be executed.

Conclusion

The SQL CASE statement is a versatile tool for adding conditional logic to your queries. By understanding its two forms (simple and searched) and how to use it within various SQL clauses, you can write more dynamic, flexible, and powerful queries. Mastering the CASE statement is a crucial step in becoming proficient with SQL. Remember to practice with different scenarios to solidify your understanding and apply it effectively in your database work.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top