SQL CASE Expression

The CASE expression in SQL allows you to add conditional logic to your queries. It's similar to an if-then-else statement in programming languages, enabling you to return different values based on different conditions.

CASE Expression: Definition and Usage

The CASE expression evaluates a series of conditions sequentially. When it finds the first condition that's true, it returns the corresponding result and stops processing further conditions. If none of the conditions are met, it returns the value specified in the ELSE clause (if present); otherwise, it returns NULL.

Syntax

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

Example Database

Here's a sample from the 'OrderDetails' table:

Examples

Conditional Quantity Text

This example adds a text description ('QuantityText') based on the 'Quantity' value.

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 AS QuantityText
FROM OrderDetails;
      

OrderID | Quantity | QuantityText
----------------------------------
10248   | 12       | The quantity is under 30
10248   | 10       | The quantity is under 30
10248   | 5        | The quantity is under 30
10249   | 9        | The quantity is under 30
10249   | 40       | The quantity is greater than 30
      

Conditional Ordering

This example demonstrates conditional ordering; if 'City' is NULL, order by 'Country'; otherwise, order by 'City'. (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;
      

--Output will vary depending on the data in your 'Customers' table.