--- title: "nullif" slug: "dev-database-reference-sql-functions-conditional-and-cast-nullif" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-conditional-and-cast-nullif" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # nullif *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Conditional and conversion functions](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast) › Replacing NULLs.* Returns NULL if its two arguments are equal, and the first argument otherwise. Use it to turn a placeholder, such as an empty string or a zero, into NULL. ## Syntax ``` nullif(a, b) ``` | Argument | Type | Description | | --- | --- | --- | | `a` | any | The value returned when it isn't equal to `b`. | | `b` | a type that compares with `a` | The placeholder to turn into NULL. | **Returns:** the type of `a`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_conditional_and_cast_nullif`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see. This block connects, defines the `run()` and `plan()` helpers, and creates a table of four survey answers. The form stored an empty comment and a count of `0` where nothing was entered, and the last row has no values: ``` import os import re import adbc_driver_manager import adbc_driver_manager.dbapi as dbapi import adbc_driver_vastdb BUCKET = os.environ["VASTDB_BUCKET"] SCHEMA = f'"{BUCKET}/docs_sql_fn_conditional_and_cast_nullif"' conn = dbapi.connect( driver=adbc_driver_vastdb.get_driver_path(), db_kwargs={ "vast.db.endpoint": os.environ["QE_ENDPOINT"], "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"], "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"], }, autocommit=True, ) def server_message(e): """The server's message in a driver error, without the request ID or the echoed statement.""" text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e)) found = (re.search(r'message: "(.*?)", request_id', text, re.S) or re.search(r"description: (.*)\)", text, re.S)) message = found.group(1) if found else text.strip().splitlines()[-1] message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S) message = re.split(r"\\n|\n", message)[0].replace('\\"', '"') return message.replace(BUCKET, "") def run(sql): """Run one statement. Print the rows it returns, or the error.""" try: with conn.cursor() as cur: cur.execute(sql) rows = cur.fetchall() if cur.description else [] except (adbc_driver_manager.Error, OSError) as e: print(f"{type(e).__name__}: {server_message(e)}") else: print(rows) def plan(sql): """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert.""" with conn.cursor() as cur: cur.execute("EXPLAIN " + sql) steps = cur.fetchall()[0][1] print("\n".join(line for line in steps.splitlines() if "Rows" not in line)) TABLE = f"{SCHEMA}.answers" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} " "(id BIGINT, comment VARCHAR, first_city VARCHAR, last_city VARCHAR, visits TINYINT, total INTEGER)") run(f"INSERT INTO {TABLE} VALUES " "(1, 'great', 'Paris', 'Rome', 4, 200), (2, '', 'Oslo', 'Oslo', 0, 0), " "(3, 'ok', 'Lima', 'lima', 2, 50), (4, NULL, NULL, NULL, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `nullif(a, b)` compares `a` and `b` with `=`. When they're equal it returns NULL; otherwise it returns `a`. It's the same as `CASE WHEN a = b THEN NULL ELSE a END`. The comparison follows the rules of `=`: - **Values of different types are converted first.** `nullif(1, 1.0)`, `nullif(1, '1')` and `nullif('1', 1)` are all NULL, and `nullif(DATE '2026-10-16', TIMESTAMP '2026-10-16')` is a NULL `DATE`. - **Text is compared exactly**, so `nullif('a', 'A')` returns `'a'`. With `COLLATE nocase` on the first argument, `nullif('a' COLLATE nocase, 'A')` is NULL. - **`NaN` equals `NaN`, and `0.0` equals `-0.0`,** so both pairs give NULL. - **Lists and structs** are equal when all their elements are: `nullif([1], [1])` and `nullif({'a': 1}, {'a': 1})` are NULL. The result keeps the type of `a`: `nullif(1::TINYINT, 300)` is the `TINYINT` `1`. ## NULLs and edge cases | Input | Result | | --- | --- | | `nullif(NULL, 1)` | `NULL` | | `nullif(1, NULL)` (nothing is equal to NULL) | `1` | | `nullif(NULL, NULL)` | `NULL` | | `nullif('', '')` | `NULL` | | `nullif(1, 2)` | `1` | | `10 / nullif(0, 0)` | `NULL` | | `nullif(1, 'x')` | Error: `Conversion Error: Could not convert string 'x' to INT32` | | One or three arguments | Error: `Parser Error: syntax error` | The same cases and the comparison rules, as the cluster returns them. They use the helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-nullif#set-up-the-examples): ``` run("SELECT nullif(NULL, 1), nullif(1, NULL), nullif(NULL, NULL), nullif('', ''), nullif(1, 2), " "10 / nullif(0, 0)") run("SELECT nullif(1, 1.0), typeof(nullif(1, 1.0)), nullif(1, '1'), nullif('1', 1), " "nullif(DATE '2026-10-16', TIMESTAMP '2026-10-16'), nullif('a', 'A'), nullif('a' COLLATE nocase, 'A')") run("SELECT nullif('nan'::DOUBLE, 'nan'::DOUBLE), nullif(0.0, -0.0), nullif([1], [1]), " "nullif({'a': 1}, {'a': 1}), nullif(1::TINYINT, 300), typeof(nullif(1::TINYINT, 300))") run("SELECT nullif(1, 'x')") run("SELECT nullif(1)") ``` ``` [(None, 1, None, None, 1, None)] [(None, 'INTEGER', None, None, None, 'a', None)] [(None, None, None, None, 1, 'TINYINT')] InternalError: Conversion Error: Could not convert string 'x' to INT32 InternalError: Parse query: 'SELECT nullif(1)' failed with: Parser Error: syntax error at or near ")" ``` ## Examples Turn the placeholders into NULL, and divide by a count that may be zero. `total / nullif(visits, 0)` is NULL for answer 2 instead of a division by zero: ``` run(f"SELECT id, nullif(comment, ''), nullif(visits, 0), total / nullif(visits, 0) " f"FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'great', 4, 50.0), (2, None, None, None), (3, 'ok', 2, 25.0), (4, None, None, None)] ``` Keep the last city only where it differs from the first. Answer 3's cities differ only in case, so compare them with `lower()` to treat them as the same: ``` run(f"SELECT id, nullif(last_city, first_city), nullif(lower(last_city), lower(first_city)) " f"FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'Rome', 'rome'), (2, None, None), (3, 'lima', None), (4, None, None)] ``` With [coalesce](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-coalesce), replace both the empty comments and the NULLs with one default: ``` run(f"SELECT id, coalesce(nullif(comment, ''), 'no comment') FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'great'), (2, 'no comment'), (3, 'ok'), (4, 'no comment')] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). VAST DataBase can evaluate a comparison of a column with a constant, but not `nullif()` of a column, so `nullif()` on a column is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the column and the Query Engine computes `nullif()` for each row it returns. - **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine computes `nullif()` and keeps the rows that match. No rows are skipped in storage. The plan of a condition on `nullif()` shows the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE nullif(comment, '') IS NULL") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (CASE WHEN ((comment = ''│ │ )) THEN (NULL) ELSE │ │ comment END IS NULL) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ comment │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Test the placeholder and the NULL separately.** `nullif(comment, '') IS NULL` keeps the same rows as `comment = '' OR comment IS NULL`. The second form tests one column against constants, which VAST DataBase can evaluate: ``` plan(f"SELECT id FROM {TABLE} WHERE comment = '' OR comment IS NULL") run(f"SELECT id FROM {TABLE} WHERE comment = '' OR comment IS NULL ORDER BY id LIMIT 10") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ [(2,), (4,)] ``` - **Store NULL instead of a placeholder.** If you control how the rows are written, write NULL for a missing value, and you won't need `nullif()` when you read them. ## Limitations and errors - **Exactly two arguments.** One or three fail with `Parser Error: syntax error`. - **The arguments must compare.** `nullif(1, 'x')` converts `'x'` to the first argument's type and fails with `Conversion Error: Could not convert string 'x' to INT32`. - **`nullifzero` isn't available.** It fails with `Catalog Error: Scalar Function with name nullifzero does not exist!`. Use `nullif(x, 0)`. ``` run("SELECT nullifzero(0)") run("SELECT nullif(0, 0)") ``` ``` InternalError: Catalog Error: Scalar Function with name nullifzero does not exist! [(None,)] ``` ## Similar functions | Function | Use it for | Example | Result | | --- | --- | --- | --- | | [coalesce](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-coalesce) | the reverse: a value in place of NULL | `coalesce(nullif('', ''), 'b')` | `'b'` | | [ifnull](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-ifnull) | a value in place of NULL, two arguments | `ifnull(NULL, 'b')` | `'b'` | | [CASE](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-case) | NULL for more than one placeholder | `CASE WHEN x IN ('', '-') THEN NULL ELSE x END` | `NULL` for `''` and `'-'` | ``` run("SELECT nullif('', ''), coalesce(nullif('', ''), 'b'), ifnull(NULL, 'b'), " "CASE WHEN '-' IN ('', '-') THEN NULL ELSE '-' END") ``` ``` [(None, 'b', 'b', None)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Conditional and conversion functions](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast): all the conditional and conversion functions. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.