Nullable functions help you handle NULL values effectively in your queries. These functions enable you to:

  1. Check for NULL values
  2. Convert between Nullable and non-Nullable types
  3. Perform operations involving potential NULL values

Understanding and using these functions can significantly improve your data processing capabilities, especially when dealing with:

  • Missing data
  • Optional fields
  • Complex data transformations

​
ClickHouse function reference

​
isNull

Checks if the argument is NULL.

Syntax:

isNull(x)

Arguments:

  • x (any non-compound data type): A value of any non-compound data type.

Returns:

  • 1 if x is NULL. (UInt8)
  • 0 if x is not NULL. (UInt8)

Example:

SELECT
  taco_id,
  taco_name,
  isNull(special_sauce) AS is_sauce_null
FROM
  taco_menu;

Result:

| taco_id | taco_name      | is_sauce_null |
|---------|----------------|---------------|
| 1       | Classic Beef   | 0             |
| 2       | Veggie Delight | 1             |
| 3       | Spicy Chicken  | 0             |

In this example, isNull checks if the special_sauce column contains NULL values. The result is 1 for the Veggie Delight taco, indicating it has no special sauce.

This function is equivalent to the IS NULL operator in SQL.

​
isNullable

Checks if a column allows NULL values.

Syntax:

isNullable(x)

Arguments:

  • x (Column): A column.

Returns:

  • 1 if x allows NULL values. (UInt8)
  • 0 if x does not allow NULL values. (UInt8)

Example:

SELECT
	isNullable(order_id) AS id_nullable,
	isNullable(customer_name) AS name_nullable,
	isNullable(special_request) AS request_nullable
FROM (
	SELECT
		toUInt32(1) AS order_id,
		'John Doe' AS customer_name,
		CAST(NULL AS Nullable(String)) AS special_request
) AS taco_orders
LIMIT 1;

Result:

| id_nullable | name_nullable | request_nullable |
|-------------|---------------|------------------|
| 0           | 0             | 1                |

In this example, isNullable checks whether each column in the taco_orders table allows NULL values. The special_request column is defined as Nullable(String), so it returns 1, while the other columns return 0.

This function is useful for inspecting table schemas and understanding which columns can contain NULL values.

​
isNotNull

Checks if the argument is not NULL.

Syntax:

isNotNull(x)

Arguments:

  • x: A value of any non-compound data type.

Returns:

  • 1 if x is not NULL. (UInt8)
  • 0 if x is NULL. (UInt8)

Example:

SELECT
  taco_id,
  taco_name,
  isNotNull(special_sauce) AS has_special_sauce
FROM
  taco_menu;

Result:

| taco_id | taco_name      | has_special_sauce |
|---------|----------------|-------------------|
| 1       | Classic Beef   | 1                 |
| 2       | Veggie Delight | 0                 |
| 3       | Spicy Chicken  | 1                 |

In this example, isNotNull checks if each taco has a special sauce. The result 1 indicates the presence of a special sauce, while 0 indicates its absence (NULL value).

This function is often used in WHERE clauses to filter out NULL values or in conditional expressions where NULL handling is important.

See Also:

  • isNull function
  • IS NOT NULL operator

​
isNotDistinctFrom

Performs a null-safe comparison. This function is primarily used internally for comparing JOIN keys that may contain NULL values in the JOIN ON section.

Syntax:

isNotDistinctFrom(x, y)

Arguments:

  • x (any type): First JOIN key.
  • y (any type): Second JOIN key.

Returns:

  • 1 (true) when x and y are both NULL or when they are equal.
  • 0 (false) otherwise.

Example:

SELECT
  isNotDistinctFrom(NULL, NULL) AS null_null,
  isNotDistinctFrom(1, NULL) AS one_null,
  isNotDistinctFrom(1, 1) AS one_one,
  isNotDistinctFrom('salsa', 'salsa') AS salsa_salsa
FROM
  taco_orders
LIMIT 1;

Result:

| null_null | one_null | one_one | salsa_salsa |
|-----------|----------|---------|-------------|
| 1         | 0        | 1       | 1           |

This example demonstrates how isNotDistinctFrom handles various comparisons:

  • NULL values are considered equal to each other.
  • A non-NULL value is not equal to NULL.
  • Equal non-NULL values return true.
  • Equal strings also return true.

This function is primarily used internally for JOIN operations. It’s not recommended to use it directly in queries unless you have a specific need for null-safe comparisons.

​
isZeroOrNull

Checks if a value is zero or NULL.

Syntax:

isZeroOrNull(x)

Arguments:

  • x (any non-compound data type): The value to check.

Returns:

  • 1 if x is zero or NULL.
  • 0 otherwise.

Example:

SELECT
  taco_id,
  taco_price,
  isZeroOrNull(taco_price) AS is_free_or_unknown
FROM
  taco_menu;

Result:

| taco_id | taco_price | is_free_or_unknown  |
|---------|------------|---------------------|
| 1       | 5.99       | 0                   |
| 2       | 0.00       | 1                   |
| 3       | NULL       | 1                   |
| 4       | 7.50       | 0                   |

In this example, isZeroOrNull checks if each taco_price is either zero (free) or NULL (unknown). The result 1 indicates a free taco or unknown price, while 0 indicates a non-zero, known price.

This function is useful for identifying items that are either free or have missing price information in a single operation.

​
coalesce

Returns the first non-NULL argument.

Syntax:

coalesce(x, ...)

Arguments:

  • x, ...: Any number of parameters of non-compound type. All parameters must be of mutually compatible data types.

Returns:

  • The first non-NULL argument.
  • NULL, if all arguments are NULL.

Example:

SELECT
  taco_order_id,
  coalesce(preferred_salsa, default_salsa, 'Mild') AS selected_salsa
FROM
  taco_orders;

Result:

| taco_order_id | selected_salsa |
|---------------|----------------|
| 1             | Spicy          |
| 2             | Medium         |
| 3             | Mild           |

In this example:

  • For order 1, preferred_salsa is ‘Spicy’, so it’s returned.
  • For order 2, preferred_salsa is NULL, so default_salsa (‘Medium’) is returned.
  • For order 3, both preferred_salsa and default_salsa are NULL, so the fallback value ‘Mild’ is returned.

This function is particularly useful for providing default values or fallbacks when dealing with potentially NULL fields in your data.

​
ifNull

Returns an alternative value if the argument is NULL.

Syntax:

ifNull(x, alt)

Arguments:

  • x: The value to check for NULL.
  • alt: The value that the function returns if x is NULL.

Returns:

  • x if x is not NULL.
  • alt if x is NULL.

Example:

SELECT
  order_id,
  ifNull(special_instructions, 'No special instructions') AS instructions
FROM
  taco_orders;

Result:

| order_id | instructions              |
|----------|---------------------------|
| 1        | Extra salsa               |
| 2        | No special instructions   |
| 3        | Hold the onions           |

In this example, ifNull checks if special_instructions is NULL. If it is, it returns the default text ‘No special instructions’. This is useful for providing default values or handling missing data in taco orders.

​
nullIf

Returns NULL if both arguments are equal.

Syntax:

nullIf(x, y)

Arguments:

  • x (Any type): First value to compare.
  • y (Any type): Second value to compare.

Both arguments must be of compatible types.

Returns:

  • NULL if the arguments are equal.
  • x if the arguments are not equal.

Example:

SELECT
  nullIf(taco_price, 5.99) AS discounted_price,
  taco_name
FROM
  taco_menu;

Result:

| discounted_price | taco_name      |
|------------------|----------------|
| NULL             | Classic Beef   |
| 6.99             | Spicy Chicken  |
| 7.50             | Veggie Delight |
| NULL             | Fish Taco      |

In this example, nullIf returns NULL for tacos priced at $5.99, effectively marking them as discounted items. For tacos with different prices, it returns the original price.

This function is useful for handling special cases in data processing, such as identifying default or standard values that need to be treated differently.

​
assumeNotNull

Returns the corresponding non-Nullable value for a value of Nullable type. If the original value is NULL, an arbitrary result can be returned.

Syntax:

assumeNotNull(x)

Arguments:

  • x (Nullable): The original value of Nullable type.

Returns:

  • The input value as non-Nullable type, if it is not NULL.
  • An arbitrary value, if the input value is NULL.

Example:

SELECT
  taco_id,
  taco_name,
  assumeNotNull(spice_level) AS assumed_spice_level
FROM
  taco_menu
WHERE
  assumeNotNull(spice_level) > 3;

Result:

| taco_id | taco_name     | assumed_spice_level |
|---------|---------------|---------------------|
| 1       | Inferno Taco  | 5                   |
| 2       | Volcano Wrap  | 4                   |
| 3       | Mild Surprise | 0                   |

In this example:

  • assumeNotNull(spice_level) converts the Nullable spice level to a non-Nullable value.
  • For the ‘Mild Surprise’ taco, if the original spice_level was NULL, an arbitrary value (0 in this case) is returned.

Use assumeNotNull with caution. It’s designed for optimizing performance in cases where you’re certain that a value isn’t NULL. Incorrect use can lead to unexpected results.

​
toNullable

Converts the argument type to Nullable.

Syntax:

toNullable(x)

Arguments:

  • x (non-compound type): A value of non-compound type.

Returns:

The input value but of Nullable type.

Example:

SELECT
  toTypeName(taco_price) AS original_type,
  toTypeName(toNullable(taco_price)) AS nullable_type
FROM
  taco_menu;

Result:

| original_type | nullable_type     |
|---------------|-------------------|
| Float64       | Nullable(Float64) |
| Float64       | Nullable(Float64) |

In this example, we convert the taco_price column to a Nullable type. This allows the column to contain NULL values, which can be useful when dealing with missing or unknown prices in the taco menu.

The toNullable function is particularly useful when you need to combine non-Nullable and Nullable columns in operations or when preparing data for joins where one side might contain NULL values.

Was this page helpful?

MathematicalOther