Conditional and NULL functions

Choose a value with CASE, replace NULL with coalesce, and turn a value into NULL with nullif.

• Category: Functions — conditional and NULL
• Keywords: if, if then else, else, switch, decode, iif, ifnull, nvl, isnull, zeroifnull, default value, empty text, missing value, three-valued logic, unknown, NULL
• See also: WHERE, Operators, Conversion functions, Aggregate functions (sum), GROUP BY and HAVING

These tools choose a value for each row.

• CASE is SQL's "if ... then ... else". It gives the value of the first condition that is true.
• coalesce gives the first value that is not NULL. Use it to show 0 or '-' instead of an empty cell.
• nullif does the opposite: it turns one value (like 0 or '') into NULL.

NULL means "no value" or "not known". The last entry, NULL rules, lists how every part of KDB treats it.

Example: Label each CITY day as up, down or flat (sample data: sample)

SELECT time, open, close,
       CASE WHEN close > open THEN 'up'
            WHEN close < open THEN 'down'
            ELSE 'flat' END AS direction
FROM ochl_day
WHERE symbol = 'CITY'
LIMIT 5;

Result:

(5 rows)

The examples below also use two small tables: ledger (an accounting book with money in DECIMAL(2)) and app_users (people who signed up in an app).

Setup used by the examples below:

CREATE TABLE ledger (
  id      INT PRIMARY KEY,
  day     DATE,
  account STRING,
  debit   DECIMAL(2),
  credit  DECIMAL(2),
  note    STRING
);
INSERT INTO ledger VALUES
  (1, '2026-08-05', 'Cash', 5000.00, NULL, 'Opening balance'),
  (2, '2026-08-20', 'Rent', 1200.50, NULL, NULL),
  (3, '2026-09-01', 'Cash', NULL, 750.25, 'Shop sale'),
  (4, '2026-09-03', 'Bank', 300.00, NULL, '');

CREATE TABLE app_users (
  id        INT PRIMARY KEY,
  name      STRING NOT NULL,
  phone     STRING,
  email     STRING,
  plan      STRING,
  signed_up TIMESTAMP
);
INSERT INTO app_users VALUES
  (1, 'Sita', '9841000001', 'sita@example.com', 'pro', '2026-09-08T10:00:00+05:45'),
  (2, 'Ram', NULL, 'ram@example.com', 'free', '2026-09-08T18:30:00+05:45'),
  (3, 'Hari', '9851000003', NULL, NULL, '2026-09-09T09:15:00+05:45'),
  (4, 'Gita', NULL, NULL, 'trial', '2026-09-10T12:00:00+05:45');

CASE WHEN

Syntax:

CASE WHEN condition THEN result
     [WHEN condition THEN result ...]
     [ELSE other_result]
END

KDB checks the conditions from top to bottom. The result of the first true condition is the answer. When no condition is true, the answer is the ELSE value, or NULL when there is no ELSE.

• Returns: the value of the chosen branch.
• NULL: a condition that is NULL counts as not true, so KDB goes on to the next WHEN. Without ELSE, no match gives NULL.
• Notes:
  • A condition must be true, false or NULL. CASE WHEN 1 THEN ... is an error (write WHEN x = 1).
  • Only the chosen branch is worked out. The other results are not computed.
  • You can use CASE everywhere a value can go: SELECT, WHERE, ORDER BY, GROUP BY, inside sum(...), and inside another CASE.
  • KDB does not make the branches one type. CASE WHEN ... THEN 1 ELSE 'low' END gives a number in some rows and text in others. Give every branch the same type.
  • GROUP BY cannot use the name after AS. Repeat the whole CASE in GROUP BY (see the example).

Example: Live price moves in five groups (sample data: sample)

SELECT symbol, ltp, pct_change,
       CASE WHEN pct_change >= 2  THEN 'strong up'
            WHEN pct_change > 0   THEN 'up'
            WHEN pct_change = 0   THEN 'flat'
            WHEN pct_change > -2  THEN 'down'
            ELSE 'strong down' END AS move
FROM live_data
LATEST ON timestamp PARTITION BY symbol;

Result:

(4 rows)

Example: Count up days and down days per symbol (CASE inside sum) (sample data: sample)

SELECT symbol,
       sum(CASE WHEN close > open THEN 1 ELSE 0 END) AS up_days,
       sum(CASE WHEN close < open THEN 1 ELSE 0 END) AS down_days,
       count(*) AS days
FROM ochl_day
GROUP BY symbol
ORDER BY symbol;

Result:

(3 rows)

Example: Group rows by a CASE: repeat it in GROUP BY

SELECT CASE WHEN phone IS NULL THEN 'no phone' ELSE 'has phone' END AS phone_status,
       count(*) AS users
FROM app_users
GROUP BY CASE WHEN phone IS NULL THEN 'no phone' ELSE 'has phone' END
ORDER BY phone_status;

Result:

(2 rows)

Example: Or group by the AS name, which is shorter

SELECT CASE WHEN phone IS NULL THEN 'no phone' ELSE 'has phone' END AS phone_status,
       count(*) AS users
FROM app_users
GROUP BY phone_status
ORDER BY phone_status;

Result:

(2 rows)

Example: The condition must be true or false (shows an error)

SELECT CASE WHEN 1 THEN 'yes' ELSE 'no' END;

Result:

Error: line 1, column 18: a condition must be true or false, got '1'

CASE value WHEN

Syntax:

CASE value
     WHEN match THEN result
     [WHEN match THEN result ...]
     [ELSE other_result]
END

The short form. KDB compares value with each match, like value = match, and gives the result of the first one that is equal.

• Returns: the value of the chosen branch.
• NULL: NULL never matches, not even WHEN NULL. Use CASE WHEN value IS NULL THEN ... for that.
• Notes:
  • Numbers match numeric text: CASE 5 WHEN '5' THEN ... matches.
  • Values that cannot be compared (a number and 'abc') simply do not match. There is no error, unlike =.

Example

SELECT CASE 'COAST' WHEN 'CITY' THEN 1 WHEN 'COAST' THEN 2 ELSE 0 END AS a,
       CASE 5 WHEN '5' THEN 'text five matches' END AS b,
       CASE NULL WHEN NULL THEN 'matched' ELSE 'no match' END AS c;

Result:

(1 row)

Example: Sort sectors in your own order (sample data: sample)

SELECT symbol, sector
FROM securities
ORDER BY CASE sector WHEN 'Banking' THEN 1
                     WHEN 'Energy' THEN 2
                     ELSE 3 END,
         symbol;

Result:

(5 rows)

Example: Plan names for an app report

SELECT name,
       CASE plan WHEN 'pro' THEN 'Paid'
                 WHEN 'trial' THEN 'Trial (14 days)'
                 WHEN 'free' THEN 'Free'
                 ELSE 'Not chosen' END AS plan_name
FROM app_users
ORDER BY id;

Result:

(4 rows)

Example: END is missing (shows an error)

SELECT CASE plan WHEN 'pro' THEN 'Paid' FROM app_users;

Result:

Error: line 1, column 41: expected an operator (AND, OR, =, +, ...), WHEN, ELSE or END to close the CASE that starts at line 1, column 8 but found 'FROM'

coalesce

Syntax:

coalesce(value, value, ...)

Gives the first value that is not NULL. coalesce(phone, 'no phone') shows the phone, or 'no phone' when there is none.

• Returns: the first value that is not NULL, with its own type.
• NULL: NULL only when every value is NULL.
• Notes:
  • KDB stops at the first value that is not NULL. The values after it are not worked out.
  • The values keep their types. coalesce(debit, 0) gives a DECIMAL(2) in some rows and the whole number 0 in others. Then sum of it becomes a DOUBLE. For exact money write coalesce(debit, 0::DECIMAL(2)).
  • Other databases call this ifnull, nvl or isnull. KDB has only coalesce.

Example

SELECT coalesce(NULL, 'n/a') AS a, coalesce(NULL, NULL, 3) AS b, coalesce(7, 8) AS c, coalesce(NULL, NULL) AS d;

Result:

(1 row)

Example: Show something instead of an empty cell

SELECT name,
       coalesce(phone, email, 'no contact') AS contact,
       coalesce(plan, 'free') AS plan
FROM app_users
ORDER BY id;

Result:

(4 rows)

Example: Ledger totals per account: keep DECIMAL(2) exact

SELECT account,
       sum(coalesce(debit, 0::DECIMAL(2)))  AS total_debit,
       sum(coalesce(credit, 0::DECIMAL(2))) AS total_credit,
       sum(coalesce(debit, 0)) AS mixed_types
FROM ledger
GROUP BY account
ORDER BY account;

Result:

(3 rows)

In the Cash row, mixed_types is 5000 (a DOUBLE), not 5000.00: one row added the DECIMAL(2) value 5000.00 and the other row added the whole number 0.

Example: coalesce needs at least one value (shows an error)

SELECT coalesce();

Result:

Error: line 1, column 8: function 'coalesce' takes 1 to 64 argument(s), got 0

nullif

Syntax:

nullif(value, other)

Gives NULL when value equals other. Otherwise it gives value. It is a short way to write CASE WHEN value = other THEN NULL ELSE value END.

• Returns: value (with its type) or NULL.
• NULL: nullif(NULL, x) is NULL. nullif(x, NULL) is x.
• Notes:
  • Equal means the same as =: nullif(5, 5.0) and nullif(5, '5') are NULL. Text is compared exactly ('a' is not 'A').
  • Values that cannot be compared give back value, without an error.
  • Division by zero is an error, as in PostgreSQL, so x / nullif(y, 0) is the way to get NULL for it. nullif is also useful for '' or a "no data" marker like -1.

Example

SELECT nullif(5, 5) AS a, nullif(10, 0) AS b, nullif(5, '5') AS c, nullif('a', 'A') AS d, nullif(NULL, 1) AS e;

Result:

(1 row)

Example: Empty notes count as no note

SELECT id, note,
       nullif(note, '') AS clean_note,
       coalesce(nullif(note, ''), '(no note)') AS shown
FROM ledger
ORDER BY id;

Result:

(4 rows)

Example: Average only over rows that really have a value (sample data: sample)

SELECT symbol,
       round(avg(quantity), 1) AS avg_all,
       round(avg(nullif(quantity, 1000)), 1) AS avg_without_1000_lots,
       count(nullif(quantity, 1000)) AS trades_not_1000
FROM trade_log
GROUP BY symbol
ORDER BY symbol;

Result:

(4 rows)

NULL rules

Syntax:

value IS NULL      value IS NOT NULL
coalesce(value, default)      nullif(value, other)

NULL means "no value". Almost everything that touches NULL gives NULL, because the answer is not known. Use IS NULL to test for it: x = NULL is never true.

• Notes: 3 NOT IN (1, NULL) is NULL, not true. A NOT IN list with a NULL in it never keeps a row. See also aggregates.

Example

SELECT NULL + 1 AS plus, NULL = NULL AS equal, NULL IS NULL AS is_null,
       FALSE AND NULL AS and_false, TRUE OR NULL AS or_true,
       3 IN (1, NULL) AS in_list, 3 NOT IN (1, NULL) AS not_in_list, 'a' || NULL AS joined;

Result:

(1 row)

Example: = NULL finds nothing; IS NULL finds the rows

SELECT (SELECT count(*) FROM app_users WHERE phone = NULL) AS with_equals_null,
       (SELECT count(*) FROM app_users WHERE phone IS NULL) AS with_is_null;

Result:

(1 row)

Example: count(*) counts rows, count(x) skips NULL

SELECT count(*) AS users, count(phone) AS with_phone, count(email) AS with_email, count(plan) AS with_plan
FROM app_users;

Result:

(1 row)