CASE

Every query so far handed back columns as they already were. CASE builds a new one, working out each row's value from a chain of conditions.

name             station  price
Bifteki          grill    18
Baklava          pastry   7
Melitzanosalata  salad    16
Loukoumades      pastry   9
Souvlaki         grill    11
Dakos            salad    14

You list WHEN a condition THEN a value, as many times as you need. SQL reads them top to bottom and stops at the first one that is true. ELSE covers everything that matched nothing, and END closes it off.

SELECT name,
  CASE
    WHEN price < 10 THEN 'cheap'
    WHEN price < 16 THEN 'mid'
    ELSE 'pricey'
  END
FROM dishes;

name             band
Bifteki          pricey
Baklava          cheap
Melitzanosalata  pricey
Loukoumades      cheap
Souvlaki         mid
Dakos            mid

Because it stops at the first hit, order matters: price < 16 only ever sees the dishes that already failed price < 10, so it really means “10 to 15”.

Leave off ELSE and any row that matches no WHEN comes back as NULL, SQL's word for “no value”. In lesson 10, we will learn more about NULL.

SELECT name,
  CASE WHEN station = 'grill' THEN 'grill' END
FROM dishes;

name             tag
Bifteki          grill
Baklava          NULL
Melitzanosalata  NULL
Loukoumades      NULL
Souvlaki         grill
Dakos            NULL

When every branch just compares one column to a value, there is a shorter form: name the column once, right after CASE, then list the values.

SELECT name,
  CASE station
    WHEN 'pastry' THEN 'cold line'
    WHEN 'salad' THEN 'cold line'
    ELSE 'hot line'
  END
FROM dishes;

name             line
Bifteki          hot line
Baklava          cold line
Melitzanosalata  cold line
Loukoumades      cold line
Souvlaki         hot line
Dakos            cold line

A CASE can go anywhere a value can, not just in the SELECT. Drop one into ORDER BY to sort by a rule of your own. Front of house's floor plan works the same way: turn each table's status into a number, then sort on that.

number  seats  section      status
2       4      dining room  free
4       2      patio        occupied
6       6      dining room  occupied
9       2      bar          reserved
11      8      dining room  free
14      4      patio        occupied

SELECT number, status
FROM seating
ORDER BY
  CASE status
    WHEN 'reserved' THEN 1
    WHEN 'occupied' THEN 2
    ELSE 3
  END;

number  status
9       reserved
4       occupied
6       occupied
14      occupied
2       free
11      free

Put a CASE inside an aggregate and you count or add only the rows that match. Each SUM here adds a 1 for every customer in its band and a 0 for the rest, which turns two conditions into two tallies on one line.

SELECT
  SUM(CASE WHEN visits < 10 THEN 1 ELSE 0 END),
  SUM(CASE WHEN visits >= 10 THEN 1 ELSE 0 END)
FROM customers;

occasional  regular_plus
3           3

CASE reshapes values inside the rows you have already got. Next you will pull in columns that live in a different table altogether using JOIN.