SQL Basics

SQL Functions

SQL Joins

SQL Advanced

SQL Database

SQL Tables

SQL Constraints

SQL Performance

SQL Data Types

SQL Security

SQL CASE Statement

❮ Previous Next ❯

The SQL CASE Statement

The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement).

So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

CASE Syntax

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

Demo Database

Orders Table:

OrderID CustomerID OrderDate
10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20

Products Table:

ProductID ProductName Price
1 Chai 18.00
2 Chang 19.00
3 Aniseed Syrup 10.00
4 Chef Anton's Cajun Seasoning 22.00

CASE Example 1

The following SQL goes through conditions and returns a value when the first condition is met:

CASE Example

SELECT OrderID, CustomerID,
CASE
    WHEN CustomerID > 50 THEN 'CustomerID is greater than 50'
    WHEN CustomerID = 50 THEN 'CustomerID is 50'
    ELSE 'CustomerID is under 50'
END AS CustomerID_Text
FROM Orders;

CASE Example 2: In ORDER BY

The following SQL will order the customers by ContactName. However, if ContactName is NULL, then it will order by Country.

CASE in ORDER BY Example

SELECT CustomerName, ContactName, Country
FROM Customers
ORDER BY
(CASE
    WHEN ContactName IS NULL THEN Country
    ELSE ContactName
END);
❮ Previous Next ❯