SQL CASE Statement

The CASE statement in SQL allows you to add conditional logic to your queries. It acts like an if-then-else statement in programming, enabling you to create different outputs based on various conditions.

CASE Statement: Definition and Usage

The CASE statement evaluates conditions sequentially. When it finds a condition that's true, it returns the corresponding result and stops evaluating further conditions. If none of the conditions are true, it returns the value specified in the ELSE clause (if you've included one). Otherwise, it returns NULL.

Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    WHEN conditionN THEN resultN
    ELSE result
END;
      

Examples

Conditional Output Based on Quantity

This example adds a text description to each row in the 'OrderDetails' table based on the 'Quantity'. (Assumes an 'OrderDetails' table with 'OrderID' and 'Quantity' columns.)

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END
FROM OrderDetails;
      

OrderID | Quantity | Result
--------------------------
(Example data from OrderDetails table)
      

Conditional Ordering

This example orders customers by 'City', but if 'City' is NULL, it orders by 'Country'. (Assumes a 'Customers' table with 'CustomerName', 'City', and 'Country' columns.)

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);
      

CustomerName | City        | Country
----------------------------------
(Customers sorted by City; if City is NULL, sorted by Country)