SQL CASE WHEN Explained for Beginners (With Examples)

Searching for "SQL CASE WHEN explained for beginners"? Here is the short answer. CASE WHEN is how SQL does "if this, then that". It looks at each row, checks a condition, and gives back a value you choose. You can use it to turn numbers into labels like "cheap" or "expensive" without changing your data.

In this guide you will learn what CASE WHEN is, how to write it, and how to use it in real queries. We define every new word the first time we use it. You need no experience beyond a basic SELECT.

What is CASE WHEN in SQL?

SQL (Structured Query Language) is the language used to read and change data stored in a database. A CASE expression is a piece of a query that works like an if/else rule. An expression is just something that produces a value.

Think of a teacher giving grades. If the score is 90 or more, the grade is A. If it is 80 or more, the grade is B. Otherwise, the grade is C. CASE WHEN lets SQL follow the same kind of rule for every row in a table.

The sample table we will use

A table is a grid of data with columns and rows. Here is a small table called products. You can create it in any SQL database to follow along.

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  price INT,
  stock INT
);

INSERT INTO products (id, name, price, stock) VALUES
  (1, 'Notebook', 5, 120),
  (2, 'Backpack', 45, 0),
  (3, 'Headphones', 80, 15),
  (4, 'Pen', 2, 300),
  (5, 'Desk Lamp', 30, 4);

The first statement builds the table with four columns. The second statement adds five rows. The price and stock columns hold whole numbers, which is perfect for our examples.

The basic CASE WHEN syntax

Syntax means the exact way you must write something. A CASE expression has this shape:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

• CASE starts the expression.
• WHEN is followed by a condition, which is a test that is true or false.
• THEN is followed by the value to return when the test is true.
• ELSE is the fallback value when no WHEN matched. It is optional, but good to include.
• END closes the expression.

SQL checks the WHEN lines from top to bottom. The first one that is true wins, and SQL stops looking at the rest. That order matters, as you will see soon.

Example 1: Label products by price

Let us add a new column to our results that says whether each product is cheap, normal, or expensive.

SELECT
  name,
  price,
  CASE
    WHEN price < 10 THEN 'Cheap'
    WHEN price < 50 THEN 'Normal'
    ELSE 'Expensive'
  END AS price_label
FROM products;

Line by line: SELECT name, price shows two normal columns. Then the CASE block checks the price. If it is under 10, the label is Cheap. If not, SQL tries the next line: under 50 gives Normal. Anything left over gets Expensive from the ELSE. AS price_label gives the new column a friendly name, which is called an alias. FROM products says which table to read.

Notice that the Backpack costs 45, which is under 50, so it is Normal. We did not need to write "price >= 10 AND price < 50" because the Cheap line already caught everything below 10. This is why the order of WHEN lines matters.

Example 2: Show stock status

CASE WHEN works with any column. Let us tell customers if an item is available.

SELECT
  name,
  stock,
  CASE
    WHEN stock = 0 THEN 'Out of stock'
    WHEN stock <= 5 THEN 'Low stock'
    ELSE 'In stock'
  END AS stock_status
FROM products;

Here the first WHEN checks for exactly zero using =. The second catches small amounts of 5 or fewer using <=. Everything else is In stock. Our Backpack shows Out of stock, and the Desk Lamp shows Low stock.

The simple CASE form

When you are only checking one column for exact matches, there is a shorter style. You write the column once after CASE, and then list the values.

SELECT
  name,
  CASE id
    WHEN 1 THEN 'First item'
    WHEN 2 THEN 'Second item'
    ELSE 'Other item'
  END AS position
FROM products;

CASE id means "look at the id column". Each WHEN is then compared with = automatically. This form cannot use < or >, so for ranges use the first style from the earlier examples.

Example 3: Use CASE WHEN with GROUP BY and COUNT

CASE becomes very powerful when you combine it with grouping. GROUP BY puts rows with the same value together so you can count or add them. Let us count how many products are in each price label.

SELECT
  CASE
    WHEN price < 10 THEN 'Cheap'
    WHEN price < 50 THEN 'Normal'
    ELSE 'Expensive'
  END AS price_label,
  COUNT(*) AS total
FROM products
GROUP BY price_label;

The CASE creates the labels. GROUP BY gathers the rows by label. COUNT(*) counts how many rows are in each group. The result is Cheap 2, Normal 2, Expensive 1. Some databases, such as SQL Server, need you to repeat the whole CASE inside GROUP BY instead of using the alias. If you see an error, try that.

Example 4: Count only some rows

A popular trick is putting CASE inside a function like SUM. This lets you count rows that match a rule, all in one query.

SELECT
  SUM(CASE WHEN stock = 0 THEN 1 ELSE 0 END) AS sold_out,
  SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS available
FROM products;

For each row, the CASE returns 1 if the rule is true and 0 if not. SUM then adds up all those ones and zeros. So sold_out becomes 1 and available becomes 4. It is a neat way to build a small report.

Example 5: Use CASE in ORDER BY

You can also use CASE to sort in a custom order. Maybe you want out-of-stock products shown last.

SELECT name, stock
FROM products
ORDER BY
  CASE WHEN stock = 0 THEN 1 ELSE 0 END,
  name;

Rows with stock available get 0, and sold-out rows get 1. Sorting is from small to large, so 0 comes first and 1 comes last. After that, name sorts each group alphabetically.

Common mistakes to avoid

• Forgetting END. Every CASE must be closed with END.
• Forgetting ELSE. Without it, rows that match nothing get NULL, which means "no value".
• Wrong order of WHEN lines. Remember that the first true line wins.
• Mixing result types. If one THEN returns text, the others should return text too.
• Using = to test for NULL. Use IS NULL instead, for example WHEN stock IS NULL THEN 'Unknown'.

Quick summary