How to write IF/THEN in a SQL SELECT

1CASE WHEN … THEN … ELSE … ENDRecommended

The searched CASE lists conditions after WHEN and the value to return after each THEN. The database checks the branches from top to bottom and returns the value of the first one that is true; the rest are skipped. ELSE is the value when nothing matches, and END closes the expression. Give the result a column name with AS.

CREATE TABLE orders (id INTEGER PRIMARY KEY, total REAL);
INSERT INTO orders (id, total) VALUES
    (1, 12.5), (2, 80), (3, 250), (4, 40);

-- Searched CASE: the first WHEN that is true wins
SELECT id,
       total,
       CASE
           WHEN total >= 200 THEN 'large'
           WHEN total >= 50 THEN 'medium'
           ELSE 'small'
       END AS size
FROM orders;

-- Wrong order, and no ELSE
SELECT id,
       total,
       CASE
           WHEN total >= 50 THEN 'medium'
           WHEN total >= 200 THEN 'large'
       END AS size
FROM orders;

In the first query order 3 (250.0) passes both tests and gets large, because that branch comes first. The second query puts the wider test first, so 250.0 stops at medium and the large branch can never fire. Order the branches from the most specific to the most general. It also has no ELSE, so the two orders that match no branch get NULL. Write an ELSE whenever NULL isn't the value you want.

2Simple CASE: match one value against a list

When every branch compares the same column with a constant, the simple form is shorter: name the column once after CASE, then list the values after each WHEN. Each branch is an equality test, status = 0, status = 1 and so on, and that is where its limits come from.

CREATE TABLE tickets (id INTEGER PRIMARY KEY, status INTEGER);
INSERT INTO tickets (id, status) VALUES
    (1, 0), (2, 1), (3, 2), (4, NULL);

-- Simple CASE: compare one value against a list
SELECT id,
       status,
       CASE status
           WHEN 0 THEN 'open'
           WHEN 1 THEN 'closed'
           WHEN NULL THEN 'unset'
           ELSE 'other'
       END AS label
FROM tickets;

-- NULL needs the searched form and IS NULL
SELECT id,
       CASE
           WHEN status IS NULL THEN 'unset'
           WHEN status IN (0, 1) THEN 'known'
           ELSE 'other'
       END AS label
FROM tickets;

Ticket 4 gets other, not unset: WHEN NULL tests status = NULL, which is never true, not even when status is NULL. The second query switches to the searched form, where status IS NULL works and ticket 4 gets unset. Use the simple form for plain value lookups; anything else needs the searched form.

3OR, AND and IN inside a WHEN

A searched WHEN takes any condition you could write in a WHERE clause, so OR, AND, IN, LIKE and BETWEEN all work there. If you were told "OR is not supported with CASE", the query used the simple form, CASE country WHEN 'US' OR 'CA', where each WHEN holds a value, not a condition.

CREATE TABLE users (name TEXT, country TEXT, age INTEGER);
INSERT INTO users (name, country, age) VALUES
    ('Ana', 'US', 34), ('Ben', 'CA', 17),
    ('Cleo', 'UK', 52), ('Dev', 'IN', 29);

-- Any condition works after WHEN: IN, OR, AND
SELECT name,
       CASE
           WHEN country IN ('US', 'CA') AND age >= 18 THEN 'NA adult'
           WHEN country = 'US' OR country = 'CA' THEN 'NA minor'
           ELSE 'elsewhere'
       END AS grp
FROM users;

-- Simple CASE can't take OR: 'US' OR 'CA' is evaluated first
SELECT name,
       CASE country
           WHEN 'US' OR 'CA' THEN 'NA'
           ELSE 'elsewhere'
       END AS grp
FROM users;

The first query sorts Ana into NA adult, Ben (17) into NA minor and the others into elsewhere. In the second, 'US' OR 'CA' is worked out first, as one false value, and every country is compared with that, so even Ana lands in elsewhere. SQLite and MySQL run that query and return wrong rows without an error (MySQL only adds warnings); SQL Server stops with a syntax error near OR, and PostgreSQL rejects it with invalid input syntax for type boolean. For several values in the simple form, repeat the branch (WHEN 'US' THEN … WHEN 'CA' THEN …) or switch to WHEN country IN ('US', 'CA').

4CASE in ORDER BY and inside aggregates

Because CASE is just an expression, it works outside the column list too. In ORDER BY it maps text to numbers, which gives a custom sort order that alphabetical sorting can't. Inside SUM or COUNT it counts only the rows that meet a condition, so one query returns several counts side by side. Count rows with a condition covers that pattern in depth.

CREATE TABLE tasks (name TEXT, priority TEXT, done INTEGER);
INSERT INTO tasks (name, priority, done) VALUES
    ('deploy', 'high', 0), ('docs', 'low', 1),
    ('fix bug', 'high', 1), ('tests', 'medium', 0),
    ('cleanup', 'low', 0);

-- Custom sort order: high, medium, low
SELECT name, priority
FROM tasks
ORDER BY CASE priority
             WHEN 'high' THEN 1
             WHEN 'medium' THEN 2
             ELSE 3
         END,
         name;

-- Count and sum by condition in one pass
SELECT COUNT(*) AS total,
       SUM(CASE WHEN done = 1 THEN 1 ELSE 0 END) AS done,
       COUNT(CASE WHEN priority = 'high' THEN 1 END) AS high
FROM tasks;

The tasks come back high, medium, low, with name breaking ties inside each priority. The summary row shows 5 tasks, 2 done and 2 high. The two counting styles do the same job: SUM(CASE … THEN 1 ELSE 0 END) adds ones and zeros, while COUNT(CASE … THEN 1 END) relies on the missing ELSE giving NULL, which COUNT skips.

5Shortcuts: iif(), COALESCE and NULLIF

Three functions cover the most common two-way choices in less text. iif(cond, a, b) returns a when the condition is true and b otherwise; it exists in SQLite 3.32+ and SQL Server 2012+. COALESCE(a, b) returns the first argument that isn't NULL, and NULLIF(a, b) returns NULL when the two are equal. Both are standard SQL and run everywhere.

CREATE TABLE items (name TEXT, price REAL, qty INTEGER, note TEXT);
INSERT INTO items (name, price, qty, note) VALUES
    ('pen', 2.0, 10, NULL), ('ink', 9.0, 0, 'reorder'),
    ('pad', 4.5, 3, NULL);

SELECT name,
       iif(qty > 0, 'in stock', 'sold out') AS stock,
       COALESCE(note, '-') AS note,
       price / NULLIF(qty, 0) AS per_unit
FROM items;

iif marks the ink as sold out. COALESCE shows - for the two items with no note. NULLIF(qty, 0) turns the ink's zero quantity into NULL, so 9.0 / NULL gives NULL instead of a division by zero. Each is shorthand for a CASE: COALESCE(note, '-') means CASE WHEN note IS NOT NULL THEN note ELSE '-' END. Recent SQLite versions (3.48+) also accept if() as another name for iif().

6Other databases

CASE is the one form every database accepts, so it is the safe choice for code that has to move between them. The shorter functions are vendor extensions. PostgreSQL has neither IF() nor IIF() in a query, only CASE (its IF … THEN … END IF exists only inside PL/pgSQL functions). Oracle also uses CASE. Each snippet below was run on PostgreSQL 18, MySQL 8.4 and SQL Server (Babelfish).