CASE WHEN (if else in sql)

CASE is how you write if/else logic inside a query. You'll use it for labels, buckets, custom sorting, and later for really useful stuff like conditional counting.

the shape

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

It goes top to bottom and stops at the first WHEN that's true. If nothing matches and there's no ELSE, you get NULL.

labelling stuff

SELECT
  product_name,
  price,
  CASE
    WHEN price >= 3000 THEN 'expensive'
    WHEN price >= 500  THEN 'mid'
    ELSE 'cheap'
  END AS price_band
FROM products;

Order matters here. If you put price >= 500 first, the monitor (10999) would be "mid" because that check passes first and it stops.

the short version

When you're just comparing one column to fixed values:

SELECT order_id,
  CASE status
    WHEN 'delivered' THEN 'done'
    WHEN 'cancelled' THEN 'done'
    ELSE 'in progress'
  END AS stage
FROM orders;

conditional counting (this is the good stuff)

Say you want, per customer, how many orders were delivered and how many weren't, in one row. CASE inside an aggregate:

SELECT
  customer_id,
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status <> 'delivered' THEN 1 ELSE 0 END) AS not_delivered
FROM orders
GROUP BY customer_id;

This "pivot" trick shows up in a lot of interview questions. Postgres also has a cleaner way:

COUNT(*) FILTER (WHERE status = 'delivered')

Same result, but FILTER is postgres only, CASE works everywhere.

in ORDER BY

SELECT order_id, status
FROM orders
ORDER BY CASE WHEN status = 'pending' THEN 0 ELSE 1 END, order_date;

Pending ones on top, rest after.

in WHERE (usually don't)

You can, but there's almost always a simpler way with AND/OR. If you find yourself writing CASE in WHERE, step back and rethink.

stuff that trips people up

• forgetting END. Every CASE needs one.
• wrong order of WHENs, broader condition first swallows everything.
• no ELSE, then the unmatched rows silently become NULL.
• mixing types, THEN 'high' ELSE 0 errors because one's text and one's a number.

practice

Check queries.sql.

-- CASE WHEN
-- run sample-data.sql first

-- examples ---------------------------------------------------------

SELECT product_name, price,
  CASE
    WHEN price >= 3000 THEN 'expensive'
    WHEN price >= 500  THEN 'mid'
    ELSE 'cheap'
  END AS price_band
FROM products;

SELECT customer_id,
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status <> 'delivered' THEN 1 ELSE 0 END) AS not_delivered
FROM orders
GROUP BY customer_id
ORDER BY customer_id;


-- practice ---------------------------------------------------------

-- 1. label employees as 'senior' if salary >= 100000, otherwise 'junior'
-- 2. show each customer's first_name and 'has phone' / 'no phone'
-- 3. count how many products fall in each price band (cheap/mid/expensive from above)
-- 4. one row, three columns: number of delivered, pending and cancelled orders
-- 5. per department: total employees, and how many were hired in 2022 or later


-- answers ----------------------------------------------------------

-- 1
SELECT name, salary,
  CASE WHEN salary >= 100000 THEN 'senior' ELSE 'junior' END AS level
FROM employees;

-- 2
SELECT first_name,
  CASE WHEN phone IS NULL THEN 'no phone' ELSE 'has phone' END AS phone_status
FROM customers;

-- 3
SELECT
  CASE
    WHEN price >= 3000 THEN 'expensive'
    WHEN price >= 500  THEN 'mid'
    ELSE 'cheap'
  END AS price_band,
  COUNT(*)
FROM products
GROUP BY 1;
-- GROUP BY 1 means group by the first column in SELECT, saves repeating the whole CASE

-- 4
SELECT
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

-- 5
SELECT department,
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE hire_date >= '2022-01-01') AS hired_2022_plus
FROM employees
GROUP BY department;