Conditional Functions

This page documents conditional expressions.

Function: CASE (searched form)

Name & Aliases: CASE WHEN ... THEN ... ELSE ... END (searched CASE form)

Description:
Evaluates boolean WHEN expressions in order; returns the result expression corresponding to the first true condition; if none match, returns the ELSE expression (or NULL if ELSE omitted).

Syntax:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE default_result
END

Inputs:

One or more WHEN condition THEN result pairs. Optional ELSE result.

Output:

Type coerced from result expressions (THEN/ELSE).

Examples:

1. Salary banding:

SELECT 
  name,
  salary,
  CASE
    WHEN salary > 100000 THEN 'very_high'
    WHEN salary > 50000 THEN 'high'
    ELSE 'normal'
  END AS salary_band
FROM emp

2. Product status:

SELECT 
  title,
  stock,
  CASE
    WHEN stock = 0 THEN 'Out of Stock'
    WHEN stock < 10 THEN 'Low Stock'
    WHEN stock < 50 THEN 'In Stock'
    ELSE 'Well Stocked'
  END AS stock_status
FROM products

3. Discount calculation:

SELECT 
  title,
  price,
  CASE
    WHEN price > 1000 THEN price * 0.85  -- 15% off
    WHEN price > 500 THEN price * 0.90   -- 10% off
    WHEN price > 100 THEN price * 0.95   -- 5% off
    ELSE price
  END AS discounted_price
FROM products

4. Without ELSE (returns NULL if no match):

SELECT 
  name,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age < 65 THEN 'Adult'
  END AS age_group
FROM persons
-- Returns NULL for age >= 65

Function: CASE (simple / expression form)

Name & Aliases: CASE expr WHEN val1 THEN r1 WHEN val2 THEN r2 ... ELSE rN END (simple CASE)

Description:
Compare expr to valN sequentially using equality; returns corresponding rN for first match; else ELSE result or NULL.

Syntax:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE default_result
END

Inputs:

expr (any comparable type) and pairs WHEN value THEN result.

Output:

Type coerced from result expressions.

Implementation notes:
The simple form evaluates by comparing expr = value for each WHEN. Both CASE forms are parsed and translated into nested conditional Painless scripts for script_fields when used outside an aggregation push-down.

Examples:

1. Department categorization:

SELECT 
  name,
  department,
  CASE department
    WHEN 'IT' THEN 'tech'
    WHEN 'Sales' THEN 'revenue'
    WHEN 'Marketing' THEN 'revenue'
    WHEN 'Engineering' THEN 'tech'
    ELSE 'other'
  END AS dept_category
FROM emp

2. Status mapping:

SELECT 
  order_id,
  CASE status
    WHEN 'P' THEN 'Pending'
    WHEN 'S' THEN 'Shipped'
    WHEN 'D' THEN 'Delivered'
    WHEN 'C' THEN 'Cancelled'
    ELSE 'Unknown'
  END AS status_label
FROM orders

3. Priority levels:

SELECT 
  ticket_id,
  CASE priority
    WHEN 1 THEN 'Critical'
    WHEN 2 THEN 'High'
    WHEN 3 THEN 'Medium'
    WHEN 4 THEN 'Low'
    ELSE 'Undefined'
  END AS priority_name
FROM tickets

4. Numeric to text conversion:

SELECT 
  product_id,
  CASE rating
    WHEN 5 THEN '★★★★★'
    WHEN 4 THEN '★★★★☆'
    WHEN 3 THEN '★★★☆☆'
    WHEN 2 THEN '★★☆☆☆'
    WHEN 1 THEN '★☆☆☆☆'
    ELSE 'No rating'
  END AS star_display
FROM reviews

COALESCE

Returns the first non-null argument.

Syntax:

COALESCE(expr1, expr2, ...)

Inputs:

One or more expressions

Output:

Value of first non-null expression (coerced to common type)

Examples:

1. Display name fallback:

SELECT 
  COALESCE(nickname, firstname, 'N/A') AS display_name
FROM users

-- If nickname = 'Jo': returns 'Jo'
-- If nickname = NULL, firstname = 'John': returns 'John'
-- If both NULL: returns 'N/A'

2. Default values:

SELECT 
  title,
  COALESCE(discount_price, price) AS final_price
FROM products
-- Uses discount_price if available, otherwise regular price

3. Multiple fallbacks:

SELECT 
  COALESCE(mobile_phone, work_phone, home_phone, 'No phone') AS contact_phone
FROM customers

4. Handling missing data:

SELECT 
  name,
  COALESCE(email, 'no-email@example.com') AS email,
  COALESCE(country, 'Unknown') AS country
FROM users

NULLIF

Returns NULL if expr1 = expr2; otherwise returns expr1.

Syntax:

NULLIF(expr1, expr2)

Inputs:

expr1 - first expression
expr2 - second expression

Output:

Type of expr1 — a quoted literal first is the value it is read as beside expr2, and has its type: NULLIF('2024-01-31', ts) is a TIMESTAMP, NULLIF('3', n) a number — or NULL if equal

Examples:

1. Normalize unknown values:

SELECT 
  NULLIF(status, 'unknown') AS status_norm
FROM events

-- If status = 'unknown': returns NULL
-- If status = 'active': returns 'active'

2. Handle sentinel values:

SELECT 
  title,
  NULLIF(price, 0) AS valid_price
FROM products
-- Converts 0 prices to NULL

3. Guard a zero divisor — needed for %, not for /:

-- Since 0.24.0 the engine returns NULL for a zero divisor of `/` by itself, so write this:
SELECT 
  total_sales / total_orders AS avg_order_value
FROM sales_summary
-- NULL on the rows where total_orders = 0

-- `%` is not guarded by itself: NULLIF is the guard
SELECT 
  total_sales % NULLIF(total_orders, 0) AS remainder
FROM sales_summary
-- NULL on the rows where total_orders = 0 or is missing

total_sales / NULLIF(total_orders, 0) — the idiom this section used to recommend — answers NULL on the rows where total_orders is 0 or missing, in a search and in a computed column, and so does total_sales % NULLIF(total_orders, 0). Before 0.24.0 both threw a null_pointer_exception in a search on exactly those rows, and silently dropped the computed column in an ingest pipeline. See the / and % operators for the zero-divisor rules.

4. Clean data:

SELECT 
  name,
  NULLIF(TRIM(description), '') AS description
FROM products
-- Converts empty strings to NULL after trimming

Notes

The comparison follows what each argument renders, not the type of the column it came from. NULLIF(CAST(zip_code AS BIGINT), 0) compares two numbers even though zip_code is a KEYWORD. Before 0.24.0 it was compared as text and the generated script was rejected by Elasticsearch wherever it appeared — a computed column, a projection or a WHERE. A function argument is evaluated once from 0.24.0 as well; NULLIF(UPPER(name), 'X') used to call UPPER three times, and a CASE as the first argument could return the wrong value.
The two arguments must be comparable, and from 0.24.0 the engine says so itself. Two arguments are comparable when both render text, both render a temporal type, both render a number, both render a boolean, or one of them is NULL or a column whose type is not known. Any other pair is refused, because the engine has no way to compare them: NULLIF(status, 0) is refused with
```
NULLIF(status, 0) compares KEYWORD with BIGINT: NULLIF requires two arguments of comparable
types, so cast one of them
```
— at CREATE TABLE / ALTER TABLE … SET SCRIPT AS for a computed column, and before the query is sent for a projection or a WHERE. Earlier versions built a script around the mismatch instead: in a query Elasticsearch rejected it, and in a computed column the ingest processor's ignore_failure swallowed the failure, so the column was simply missing from every document and the CREATE TABLE still answered 200. Write the comparison in the type you mean: NULLIF(status, '0') or NULLIF(CAST(status AS BIGINT), 0).

A cast can create the mismatch as well as cure it: NULLIF(CAST(qty AS KEYWORD), 0) renders text against a number and is refused, even though qty is numeric.

The same rule refuses a date against a number (NULLIF(created_at, 0)) and a boolean against anything but a boolean (NULLIF(is_active, 0)). Before 0.24.0 these were accepted and compared with Painless ==, which answers false for a date against a number without ever failing — so the NULLIF returned its first argument for every row and the query answered 200 with the wrong values. If you relied on one of these, write the comparison in the type you mean.
A string literal against a date column is read by what it is compared with, as in every comparison: NULLIF(created_at, '2024-01-15') compares created_at with the date 2024-01-15, and NULLIF(qty, '3') compares qty with the number 3 — in a query and in a computed column alike (before 0.24.0 the first was refused, and a computed column compared a date with a string, which never matched). A literal that spells no date is refused, and the message depends on the literal: a malformed one is named as such — NULLIF(created_at, 'yesterday') reports that 'yesterday' is not a date for that field's format, checked against the column's mapping exactly as a WHERE comparison is — and Elasticsearch date math ('now-1d'), which no script can evaluate, is refused as not supported.
expr2 being NULL is not a match: SQL says the comparison is then UNKNOWN, so the answer is expr1, not NULL.

ISNULL

Tests if expression is NULL.

Syntax:

ISNULL(expr)

Inputs:

expr - expression to test

Output:

BOOLEAN - TRUE if NULL, FALSE otherwise

Examples:

1. Check for missing manager:

SELECT 
  name,
  ISNULL(manager) AS manager_missing
FROM emp

-- Result: TRUE if manager is NULL, else FALSE

2. Filter NULL values:

SELECT * 
FROM products
WHERE ISNULL(description)
-- Returns products without description

3. Count NULLs:

SELECT 
  COUNT(*) as total,
  SUM(CASE WHEN ISNULL(email) THEN 1 ELSE 0 END) as missing_emails
FROM users

4. Conditional logic:

SELECT 
  name,
  CASE
    WHEN ISNULL(last_login) THEN 'Never logged in'
    ELSE 'Active user'
  END AS user_status
FROM users

ISNOTNULL

Tests if expression is NOT NULL.

Syntax:

ISNOTNULL(expr)

Inputs:

expr - expression to test

Output:

BOOLEAN - TRUE if NOT NULL, FALSE if NULL

Examples:

1. Check for existing manager:

SELECT 
  name,
  ISNOTNULL(manager) AS has_manager
FROM emp

-- Result: TRUE if manager is NOT NULL, else FALSE

2. Filter non-NULL values:

SELECT * 
FROM products
WHERE ISNOTNULL(description)
-- Returns products with description

3. Count non-NULLs:

SELECT 
  COUNT(*) as total,
  SUM(CASE WHEN ISNOTNULL(email) THEN 1 ELSE 0 END) as with_emails
FROM users

4. Required fields validation:

SELECT 
  product_id,
  CASE
    WHEN ISNOTNULL(title) AND ISNOTNULL(price) AND ISNOTNULL(category) 
    THEN 'Valid'
    ELSE 'Incomplete'
  END AS validation_status
FROM products

GREATEST

Returns the largest value among the given expressions — numbers, or dates and timestamps. NULL arguments are ignored (PostgreSQL-style NULL handling); the result is NULL only if every argument is NULL.

Syntax:

GREATEST(expression1, expression2, ...)

Inputs:

One or more expressions of one kind: all numeric, or all DATE / TIMESTAMP / DATETIME

Output:

Over numbers, the common type of all the arguments, as PostgreSQL types it, and every row has it: the widest whole type when every argument is a whole number, a DOUBLE as soon as one is fractional (a REAL when the fractional ones are all REAL); over dates, a DATE when every argument is a DATE and a TIMESTAMP when they mix; NULL if every argument is NULL

Examples:

1. Highest of multiple prices:

SELECT GREATEST(price_us, price_eu, price_uk) AS max_price
FROM products

2. Floor on a computed value:

SELECT GREATEST(0, base_price - rebate) AS net
FROM orders
-- Never lets the net go below zero

3. The latest of two dates, and the days since the first:

SELECT GREATEST(ordered_on, shipped_on) AS last_step,
       GREATEST(ordered_on, shipped_on) - ordered_on AS days_to_last_step
FROM orders

4. Per group, over aggregates:

SELECT region, GREATEST(MAX(ordered_on), MAX(shipped_on)) AS last_activity
FROM orders
GROUP BY region

Notes:

Ignores NULL arguments (PostgreSQL-style NULL handling); returns NULL only if every argument is NULL.
The arguments are all numeric, or all dates and timestamps, as in every SQL engine; any other argument, or numbers mixed with dates, is refused by name once the column types are known.
Dates compare as instants, a DATE as the start of its day (UTC); the result is a date like any other: GREATEST(d, d2) - d is a number of days (see Date arithmetic).
Over aggregates it is computed per group. In a SELECT, a group missing one of the aggregates' values answers NULL: Elasticsearch does not run a per-group calculation there (its bucket_script skips a group with a missing metric), as for any per-group calculation. Written inline in HAVING, the group filter reads every aggregate and skips a NULL one.
See also: LEAST, COALESCE.

LEAST

Returns the smallest value among the given expressions — numbers, or dates and timestamps. NULL arguments are ignored (PostgreSQL-style NULL handling); the result is NULL only if every argument is NULL.

Syntax:

LEAST(expression1, expression2, ...)

Inputs:

One or more expressions of one kind: all numeric, or all DATE / TIMESTAMP / DATETIME

Output:

Over numbers, the common type of all the arguments, as PostgreSQL types it, and every row has it: the widest whole type when every argument is a whole number, a DOUBLE as soon as one is fractional (a REAL when the fractional ones are all REAL); over dates, a DATE when every argument is a DATE and a TIMESTAMP when they mix; NULL if every argument is NULL

Examples:

1. Lowest of multiple prices:

SELECT LEAST(price_us, price_eu, price_uk) AS min_price
FROM products

2. Cap on a computed value:

SELECT LEAST(rebate, base_price) AS applied_rebate
FROM orders
-- Never lets the rebate exceed the base price

3. The earlier of a date and a timestamp:

SELECT LEAST(due_on, delivered_at) AS first_event
FROM orders
-- a TIMESTAMP: the arguments mix a DATE and a TIMESTAMP

Notes:

Ignores NULL arguments (PostgreSQL-style NULL handling); returns NULL only if every argument is NULL.
The arguments are all numeric, or all dates and timestamps; see GREATEST for dates, and for LEAST over aggregates per group.
See also: GREATEST, COALESCE.

Back to index

Navigation Menu

Repository navigation

functions_conditional.md

functions_conditional.md

Conditional Functions

Function: CASE (searched form)

Function: CASE (simple / expression form)

COALESCE

NULLIF

ISNULL

ISNOTNULL

GREATEST

LEAST

FilesExpand file tree

functions_conditional.md

Latest commit

History

functions_conditional.md

File metadata and controls

Conditional Functions

Function: CASE (searched form)

Function: CASE (simple / expression form)

COALESCE

NULLIF

ISNULL

ISNOTNULL

GREATEST

LEAST