How to Use DECODE in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
DECODE(expr, search1, result1, ..., default) is a switch-style compare. It treats two nulls as equal, unlike =; CASE is the modern replacement.
SELECT product_id,
DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') AS status_label
FROM products;NULL-equality, unlike =
DECODE matches a search value to expr using null-safe equality: NULL is considered equal to NULL. DECODE(commission_pct, NULL, 'no commission', 'has commission') correctly matches null rows, where the equivalent CASE needs an explicit IS NULL test, because CASE WHEN commission_pct = NULL never matches anything.
SELECT employee_id,
DECODE(commission_pct, NULL, 'no commission', 'has commission') AS comm_flag
FROM employees;DECODE is Oracle-proprietary syntax dating back to Oracle 6 and can only test equality, not ranges or inequalities. CASE is ANSI-standard, supports BETWEEN, >, and LIKE conditions that DECODE cannot express, and is what new code should use; keep DECODE knowledge for reading legacy PL/SQL and reports.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free