CASE expression (conditional value)

Oracle

-- Searched CASE
SELECT name,
  CASE WHEN salary >= 20000 THEN 'high'
       WHEN salary >= 10000 THEN 'medium'
       ELSE 'low' END AS tier
FROM employee;

-- Oracle-specific shortcut: DECODE (equality comparison)
SELECT name, DECODE(status, 'A', 'Active', 'P', 'Passive', 'Unknown') FROM employee;

CASE maps an expression to different values according to conditions; it has both a simple form (CASE my_column WHEN value) and a searched form (CASE WHEN condition). Oracle's DECODE does a similar job but compares only for equality and treats NULLs as equal; prefer CASE for portability.

PostgreSQL

SELECT name,
  CASE WHEN salary >= 20000 THEN 'high'
       WHEN salary >= 10000 THEN 'medium'
       ELSE 'low' END AS tier
FROM employee;

PostgreSQL fully supports the standard CASE expression, usable anywhere a value is produced (SELECT, WHERE, ORDER BY, GROUP BY). It has no Oracle-style DECODE; use CASE instead, or COALESCE/NULLIF for short conditions.

MySQL

SELECT name,
  CASE WHEN salary >= 20000 THEN 'high'
       WHEN salary >= 10000 THEN 'medium'
       ELSE 'low' END AS tier
FROM employee;

-- IF() for a short condition:
SELECT name, IF(active = 1, 'Yes', 'No') FROM employee;

MySQL/MariaDB support the standard CASE expression; there is also the IF(condition, if_true, if_false) function for a quick two-branch condition (similar to SQL Server's IIF). CASE can be used in any expression context.

SQL Server

SELECT name,
  CASE WHEN salary >= 20000 THEN 'high'
       WHEN salary >= 10000 THEN 'medium'
       ELSE 'low' END AS tier
FROM employee;

-- Shortcut: IIF() (2012+)
SELECT name, IIF(active = 1, 'Yes', 'No') FROM employee;

SQL Server supports the standard CASE; since 2012 there are the IIF() shortcut for two-branch conditions and CHOOSE() for multiple matches. IIF is actually translated into CASE. CASE can also be used in ORDER BY and computed columns.

MongoDB

// Closest equivalent: $switch or $cond
db.employee.aggregate([
  { $project: { name: 1, tier: {
      $switch: { branches: [
        { case: { $gte: ["$salary", 20000] }, then: "high" },
        { case: { $gte: ["$salary", 10000] }, then: "medium" } ],
        default: "low" } } } }
])

In MongoDB aggregation, the CASE equivalents are the $switch (multi-branch) and $cond (three-argument if-then-else) operators. They are used inside $project or $addFields to create conditional derived fields.

Redis

# Not applicable: Redis has no in-query conditional expression.

Redis has no query expression like CASE. Conditional logic is written in application code or in a server-side Lua script (EVAL). Limited expression evaluation is possible with APPLY inside RediSearch FT.AGGREGATE.

Elasticsearch

# Closest equivalent: a condition inside a runtime field or script
GET employee/_search
{
  "runtime_mappings": { "tier": { "type": "keyword",
    "script": "if (doc['salary'].value >= 20000) emit('high'); else if (doc['salary'].value >= 10000) emit('medium'); else emit('low');" } },
  "fields": ["tier"]
}

In Elasticsearch, in-query CASE logic is written as a Painless script (runtime field, script_fields or scripted metric). The if/else flow is defined in the Painless language. The 'range' aggregation can also produce CASE-like buckets for categorization.