CASE in WHERE clause in SQL

Introduction

We use CASE in the WHERE clause to choose different filtering rules based on conditions. It helps us make flexible queries that change what rows to pick depending on data.

You want to filter rows differently based on a column's value.

You need to apply multiple conditions but only one should be active at a time.

You want to simplify complex filtering logic inside a single query.

You want to avoid writing multiple queries for different cases.

Syntax

SQL

SELECT columns
FROM table_name
WHERE CASE
  WHEN condition1 THEN filter_condition1
  WHEN condition2 THEN filter_condition2
  ELSE filter_condition_default
END = TRUE;

The CASE expression returns a value that the WHERE clause uses to filter rows.

Each THEN part should return a boolean condition (TRUE or FALSE) for filtering.

Examples

This filters employees: if they are in Sales, only those with salary > 50000 are shown; others need salary > 30000.

SQL

SELECT *
FROM employees
WHERE CASE
  WHEN department = 'Sales' THEN salary > 50000
  ELSE salary > 30000
END = TRUE;

This shows orders: if status is Pending, only recent orders after 2024-01-01 are shown; otherwise, all orders are included.

SQL

SELECT *
FROM orders
WHERE CASE
  WHEN status = 'Pending' THEN order_date > '2024-01-01'
  ELSE TRUE
END = TRUE;

Sample Program

This query selects products with different price filters based on their category. Electronics must cost more than 500, Clothing more than 100, and others more than 10.

SQL

CREATE TABLE products (
  id INT,
  category VARCHAR(20),
  price DECIMAL(10,2)
);

INSERT INTO products VALUES
(1, 'Electronics', 1200.00),
(2, 'Clothing', 80.00),
(3, 'Electronics', 300.00),
(4, 'Clothing', 150.00),
(5, 'Books', 20.00);

SELECT id, category, price
FROM products
WHERE CASE
  WHEN category = 'Electronics' THEN price > 500
  WHEN category = 'Clothing' THEN price > 100
  ELSE price > 10
END = TRUE;

Important Notes

CASE in WHERE helps avoid repeating similar queries with small differences.

Make sure each THEN returns a condition that evaluates to TRUE or FALSE.

Using ELSE TRUE means no filtering for rows that don't match earlier conditions.

Summary

CASE in WHERE lets you pick different filters based on data values.

It simplifies queries that need conditional filtering.

Each CASE branch returns a boolean condition for filtering rows.