Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Database configuration » Database options » Alphabetical list of database options

ansinull option

Controls the interpretation of NULL values.

Allowed values

On, Off



A temporary setting for the current user is established by SAP Open Client and jConnect TDS connections ('Off').

  PUBLIC role For current user For other users
Allowed to set permanently? Yes, with SET ANY PUBLIC OPTION Yes Yes, with SET ANY PUBLIC OPTION
Allowed to set temporarily? Yes, with SET ANY PUBLIC OPTION Yes (current connection only) No

This option is implemented primarily for Transact-SQL (Adaptive Server Enterprise) compatibility. The ansinull option affects the results of comparison predicates with NULL constants, and also affects warnings issued for grouped queries over NULL values.

With ansinull set to On, ANSI three-valued logic is used for all comparison predicates in a WHERE or HAVING clause, or in an On condition. Any comparisons with NULL using = or != evaluate to unknown.

Setting ansinull to Off means that the database server uses two-valued logic for the following four conditions:

expr = NULL

expr != NULL

expr = @var // @var is a procedure variable, or a host variable

expr != @var

In each case, the predicate evaluates to either true or false and never to unknown. In such comparisons, the NULL value is treated as a special value in each domain, and an equality (=) comparison of two NULL values yields true. The expression expr must be a relatively simple expression, referencing only columns, variables, and literals; subqueries and functions are not permitted.

With ansinull set to On, the evaluation of any aggregate function, except COUNT(*), on an expression that contains at least one NULL value, may generate a warning (SQLSTATE '01003').

With ansinull set to Off, this warning does not appear.

  • Any SQL statement that contains a WHERE, HAVING, or ON clause is affected by the ansinull option. Also, any expression inside such a statement is affected by the ansinull option.

  • Adaptive Server Enterprise 12.5 introduced a change in the behavior of LIKE predicates with a NULL pattern string when ansinull is set to Off. In SQL Anywhere, LIKE predicates remain unaffected by the setting of ansinull.