Conditional Functions

SQL offers several tools for expressing conditional logic inside a query — computing a value that depends on other values in the same row. This page pulls the main options together, recaps where each one fits, and flags which ones are portable across database systems and which are not.

CASE: the general-purpose tool

CASE is the standard, ANSI SQL way to express if/then/else logic, and it works identically across every major database. It comes in two forms: a searched CASE (a list of independent boolean conditions) and a simple CASE (comparing one expression against several possible values).

SQL

-- Searched CASE
SELECT
  order_id,
  amount,
  CASE
    WHEN amount >= 1000 THEN 'Large'
    WHEN amount >= 100  THEN 'Medium'
    ELSE 'Small'
  END AS order_size
FROM orders;

-- Simple CASE
SELECT
  status_code,
  CASE status_code
    WHEN 1 THEN 'Pending'
    WHEN 2 THEN 'Shipped'
    WHEN 3 THEN 'Delivered'
    ELSE 'Unknown'
  END AS status_label
FROM shipments;

Because CASE can express any conditional logic and runs the same way on every database, it should be the default choice whenever conditional logic is needed inside a SELECT list, an ORDER BY, or even a WHERE clause.

COALESCE and NULLIF

COALESCE(a, b, c, ...) returns the first non-NULL value from its argument list — commonly used to substitute a default when a column might be NULL. NULLIF(a, b) returns NULL if a equals b, and a otherwise — commonly used to turn a sentinel value (like an empty string or a placeholder 0) into a true NULL before further processing. Both are covered in depth on their own page; here they are worth remembering as siblings of CASE, since both can be rewritten using CASE if needed:

SQL

-- COALESCE(discount, 0) is equivalent to:
CASE WHEN discount IS NOT NULL THEN discount ELSE 0 END

-- NULLIF(count, 0) is equivalent to:
CASE WHEN count = 0 THEN NULL ELSE count END

Dialect-specific shortcuts

Several databases provide shorthand functions that compress a simple two-way CASE into a single call. These are convenient but not part of the ANSI SQL standard.

Function	Dialect	Equivalent CASE
IF(condition, true_val, false_val)	MySQL, MariaDB, SQLite (as a function)	CASE WHEN condition THEN true_val ELSE false_val END
IIF(condition, true_val, false_val)	SQL Server	CASE WHEN condition THEN true_val ELSE false_val END
DECODE(expr, val1, res1, ..., default)	Oracle	Simple CASE with an ELSE default

Note

IF() and IIF() are not portable. A query written for MySQL using IF() will fail outright on SQL Server and PostgreSQL, and a query written for SQL Server using IIF() will fail on MySQL. Neither is part of the SQL standard — they exist only in their respective vendor's dialect.

Tip

Prefer CASE over vendor-specific shortcuts, even when working exclusively on one database today. CASE is understood by every developer regardless of which system they usually work with, and it keeps queries portable if the project ever needs to migrate databases, adopt a query-building library, or run against multiple database backends.

Combining CASE with aggregates

A particularly powerful pattern is nesting CASE inside an aggregate function to build conditional counts and sums — effectively a manual pivot table:

SQL

SELECT
  department,
  COUNT(CASE WHEN status = 'active'   THEN 1 END) AS active_count,
  COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count,
  SUM(CASE WHEN status = 'active' THEN salary ELSE 0 END) AS active_payroll
FROM employees
GROUP BY department;

Here, CASE returns NULL for rows that don't match the condition, and COUNT() ignores NULLs, so each COUNT(CASE ...) only counts the rows that satisfy its own condition — all within a single pass over the table.