Operators

Can be optimized by enabling the [optimize\_functions\_to\_subcolumns](/docs/operations/settings/settings#optimize_functions_to_subcolumns) setting. With `optimize_functions_to_subcolumns = 1` the function reads only [null](/docs/sql-reference/data-types/nullable#finding-null) subcolumn instead of reading and processing the whole column data. The query `SELECT n IS NULL FROM table` transforms to `SELECT n.null FROM TABLE`.

```
SELECT x+100 FROM t_null WHERE y IS NULL
```

```
┌─plus(x, 100)─┐
│          101 │
└──────────────┘
```

### IS NOT NULL[​](#is_not_null "Direct link to IS NOT NULL")

- For [Nullable](/docs/sql-reference/data-types/nullable) type values, the `IS NOT NULL` operator returns:
  - `0`, if the value is `NULL`.
  - `1` otherwise.
- For other values, the `IS NOT NULL` operator always returns `1`.

```
SELECT * FROM t_null WHERE y IS NOT NULL
```

```
┌─x─┬─y─┐
│ 2 │ 3 │
└───┴───┘
```

Can be optimized by enabling the [optimize\_functions\_to\_subcolumns](/docs/operations/settings/settings#optimize_functions_to_subcolumns) setting. With `optimize_functions_to_subcolumns = 1` the function reads only [null](/docs/sql-reference/data-types/nullable#finding-null) subcolumn instead of reading and processing the whole column data. The query `SELECT n IS NOT NULL FROM table` transforms to `SELECT NOT n.null FROM TABLE`.

- [Access Operators](#access-operators)- [Numeric Negation Operator](#numeric-negation-operator)- [Multiplication and Division Operators](#multiplication-and-division-operators)- [Addition and Subtraction Operators](#addition-and-subtraction-operators)- [Comparison Operators](#comparison-operators)
          - [equals function](#equals-function)- [notEquals function](#notequals-function)- [lessOrEquals function](#lessorequals-function)- [greaterOrEquals function](#greaterorequals-function)- [less function](#less-function)- [greater function](#greater-function)- [like function](#like-function)- [notLike function](#notlike-function)- [ilike function](#ilike-function)- [BETWEEN function](#between-function)- [is not distinct from operator (`<=>`)](#is-not-distinct-from)- [Operators for Working with Data Sets](#operators-for-working-with-data-sets)
            - [in function](#in-function)- [notIn function](#notin-function)- [globalIn function](#globalin-function)- [globalNotIn function](#globalnotin-function)- [in subquery function](#in-subquery-function)- [notIn subquery function](#notin-subquery-function)- [in subquery function](#in-subquery-function-1)- [notIn subquery function](#notin-subquery-function-1)- [Operators for Working with Dates and Times](#operators-for-working-with-dates-and-times)
              - [EXTRACT](#extract)- [INTERVAL](#interval)- [Logical AND Operator](#logical-and-operator)- [Logical OR Operator](#logical-or-operator)- [Logical Negation Operator](#logical-negation-operator)- [Conditional Operator](#conditional-operator)- [Conditional Expression](#conditional-expression)- [Concatenation Operator](#concatenation-operator)- [Lambda Creation Operator](#lambda-creation-operator)- [Array Creation Operator](#array-creation-operator)- [Tuple Creation Operator](#tuple-creation-operator)- [Associativity](#associativity)- [Checking for `NULL`](#checking-for-null)
                                    - [IS NULL](#is_null)- [IS NOT NULL](#is_not_null)

Was this page helpful?