CASE, COALESCE, NULLIF and Conversion Functions

Concept

SQL has no if statement inside a query. It has CASE, which is an expression - it produces a value, so it can appear anywhere a column can: the select list, WHERE, ORDER BY, GROUP BY, even inside an aggregate.

Syntax

-- Searched CASE: each branch is a full condition
CASE WHEN condition1 THEN result1
     WHEN condition2 THEN result2
     ELSE default_result
END

-- Simple CASE: one expression compared for equality
CASE expression
     WHEN value1 THEN result1
     WHEN value2 THEN result2
     ELSE default_result
END

Example

SELECT first_name,
       salary,
       CASE WHEN salary >= 100000 THEN 'senior'
            WHEN salary >=  70000 THEN 'mid'
            ELSE                       'junior'
       END AS band,
       CASE status
            WHEN 'active'   THEN 'Currently employed'
            WHEN 'inactive' THEN 'Left the company'
            ELSE 'Unknown'
       END AS status_label
FROM   employees
ORDER BY salary DESC;

Explanation

CASE branches are evaluated in order and the first TRUE one wins. That is why the salary bands are written high to low: reversing them would put everyone in the first matching band. The ELSE is optional, but without it an unmatched row silently returns NULL.

The simple form only ever tests equality, so it cannot express ranges - and it never matches NULL, because NULL = NULL is unknown. Use the searched form with IS NULL for that.

NULL handling functions

-- COALESCE: first non NULL argument. Standard, works everywhere.
SELECT first_name,
       COALESCE(email, 'no email on file') AS contact,
       COALESCE(dept_id, 0)                AS dept_or_zero
FROM   employees;

-- NULLIF: returns NULL when the two arguments are equal
SELECT NULLIF(10, 10) AS same,     -- NULL
       NULLIF(10, 5)  AS different; -- 10

-- The classic use: never divide by zero again
SELECT order_id,
       quantity,
       unit_price,
       (quantity * unit_price) / NULLIF(quantity, 0) AS price_check
FROM   order_items;

Conversion functions

-- CAST is standard and portable
SELECT CAST('2024-05-27' AS DATE)          AS as_date,
       CAST(salary AS SIGNED)               AS whole_rupees,   -- MySQL integer type name
       CAST(id AS CHAR)                     AS id_text
FROM   employees;

-- Implicit conversion happens silently and can defeat an index
SELECT * FROM employees WHERE id = '3';   -- works, but the string is converted

Important rules

• Every branch of a CASE must return a compatible type. Mixing a number and a string makes the engine pick one, often the string.
• Without ELSE, an unmatched CASE returns NULL.
• COALESCE takes any number of arguments and returns the first non NULL one; IFNULL, ISNULL and NVL take exactly two.
• NULLIF(a, b) is CASE WHEN a = b THEN NULL ELSE a END, nothing more.
• Type names inside CAST are dialect specific: MySQL uses SIGNED/UNSIGNED/CHAR, PostgreSQL uses INTEGER/TEXT.

Common mistakes

• Ordering CASE branches from low to high and putting everyone in the first bucket.
• Using the simple CASE form and expecting WHEN NULL to match.
• Forgetting ELSE and producing unexplained NULLs in a report.
• Using IFNULL or NVL in code that later has to run on another product.
• Wrapping an indexed column in CAST and losing the index.

Best practices

• Prefer COALESCE and CAST - both are standard - over the vendor shorthands.
• Always write an explicit ELSE, even if it is ELSE NULL, so the intent is visible.
• Use NULLIF for every division by a column value.
• Convert types on the constant side of a comparison, never on the indexed column.

Practice

1. Label each order as 'large', 'medium' or 'small' by total, using a searched CASE.
2. Return each employee's email, or their first name plus '@example.com' when the email is missing.
3. Rewrite IFNULL(dept_id, 0) and NVL(dept_id, 0) in standard SQL.