CASE in SELECT for computed columns in SQL

Introduction

Use CASE in SELECT to create new columns that change values based on conditions, making data easier to understand.

You want to label data rows based on their values, like marking scores as 'Pass' or 'Fail'.

You need to group ages into categories like 'Child', 'Adult', or 'Senior' in your results.

You want to show different text or numbers depending on a column's value without changing the original data.

You want to highlight special cases in your report, such as marking orders as 'Late' or 'On Time'.

Syntax

SQL

SELECT column1, column2,
       CASE
         WHEN condition1 THEN result1
         WHEN condition2 THEN result2
         ELSE default_result
       END AS new_column_name
FROM table_name;

The CASE expression checks conditions in order and returns the result for the first true condition.

The ELSE part is optional; if no conditions match and ELSE is missing, the result is NULL.

Examples

Labels each student as 'Pass' or 'Fail' based on their score.

SQL

SELECT name, score,
       CASE
         WHEN score >= 60 THEN 'Pass'
         ELSE 'Fail'
       END AS result
FROM students;

Shows stock status based on quantity.

SQL

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

Groups employees by age categories.

SQL

SELECT employee, age,
       CASE
         WHEN age < 18 THEN 'Minor'
         WHEN age BETWEEN 18 AND 64 THEN 'Adult'
         ELSE 'Senior'
       END AS age_group
FROM employees;

Sample Program

This creates a students table, adds three students with scores, and selects their names, scores, and a computed column 'result' showing 'Pass' or 'Fail'.

SQL

CREATE TABLE students (
  id INT,
  name VARCHAR(50),
  score INT
);

INSERT INTO students (id, name, score) VALUES
(1, 'Alice', 85),
(2, 'Bob', 55),
(3, 'Charlie', 70);

SELECT name, score,
       CASE
         WHEN score >= 60 THEN 'Pass'
         ELSE 'Fail'
       END AS result
FROM students;

Important Notes

CASE expressions can be used anywhere in SELECT, WHERE, ORDER BY, or GROUP BY clauses.

Always test your CASE logic with sample data to ensure it covers all cases.

Use meaningful names for computed columns to make results clear.

Summary

CASE lets you create new columns based on conditions in your SELECT query.

It works like an IF-THEN-ELSE to choose values for each row.

Use it to make your query results easier to read and understand.