CASE

Labels with CASE WHEN

Module 6 of 9 SQLite in your browser

Lesson concept What you'll do

1 / 4

Give each book a label, like Long or Short.

CASE WHEN test THEN label works like IF in Excel. Check the biggest band first.

Next: A label from a test

On your own New data, same idea

Show each book's title, year and an era column: Classic if it was published before 1900, otherwise Modern.

The database

10 rows

book_id	title	author	genre	year	pages
1	The Time Machine	H.G. Wells	Science Fiction	1895	118
2	Pride and Prejudice	Jane Austen	Romance	1813	432
3	Mockingjay	Suzanne Collins	Science Fiction	2010	390
4	The Great Gatsby	F. Scott Fitzgerald	Fiction	1925	180
5	Angels & Demons	Dan Brown	Thriller	2000	616
6	Dune	Frank Herbert	Science Fiction	1965	412
7	Charlotte's Web	E.B. White	Children	1952	184
8	The Fellowship of the Ring	J.R.R. Tolkien	Fantasy	1954	423
9	Frankenstein	Mary Shelley	Horror	1818	280
10	The Fault in Our Stars	John Green	Romance	2012	313

Your query Ctrl + Enter runs it

Hint: click the green edit button on the board, or type it yourself

Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How CASE works

CASE checks the WHENs from the top and stops at the first one that is true.

pick a book

What does CASE do?

CASE WHEN pages >= 400 THEN 'Long' ELSE 'Short' END
It works out a value for each row from tests, like IF or IFS in Excel. The whole CASE ... END is one column.
What order are the WHENs checked in?

Top to bottom, and the first true one wins. Put the strictest test first: >= 400 before >= 250.
What if no WHEN is true?

The row gets the ELSE value. With no ELSE, it gets NULL.
Where can CASE go?

Anywhere a value can: in SELECT, in ORDER BY, even inside SUM: SUM(CASE WHEN genre = 'Horror' THEN 1 ELSE 0 END) counts Horror books.
How is this like Excel?

It's IFS: =IFS(F2>=400, "Long", F2>=250, "Medium", TRUE, "Short"), with ELSE as the final TRUE.

Other databases

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

A shorter way for a two-way choice
Database	How	Note
SQLite (these lessons)	`IIF(year < 1900, 'Classic', 'Modern')`	Since SQLite 3.32 (2020).
PostgreSQL	`CASE only`
MySQL / MariaDB	`IF(year < 1900, 'Classic', 'Modern')`
SQL Server	`IIF(year < 1900, 'Classic', 'Modern')`	Since SQL Server 2012.
Oracle	`CASE only`	DECODE(x, a, b, c) is an older shortcut, but only for "equals".
Sybase ASE	`CASE only`

Know someone fighting with a spreadsheet? Send them this page.