Is Null vs IsNull vs = Null - correct usage

[glenn_barber]

While debugging some SA V11 SQL Queries I found 'where colname = Null' sometimes used interchangeably with 'where colname Is Null' as a test for Null values in a column. I am not sure that they produce the correct result yet the SA will accept the syntax.

Sometimes when creating unions I also see 'colname = Null' used to create a dummy null column used to make sure the union queries are aligned. And of course there is the IsNull() function which can be used to assign values to Nulls in result sets.

What is the correct syntax , are 'where colname = Null' and 'where colname IS Null' equivalent

If not what is the difference in the behavior.

[Former Member]

Regarding NULLs, you need to consider three-valued logic. The following page in the documentation helps explain this:

NULL value

What is important to note is that equality with NULL can not be evaluated. For this reason NULL = NULL is not TRUE or FALSE but instead "evaluates" to UNKNOWN. Rows with which a WHERE clause evaluates to UNKNOWN are excluded from the result set. If you wish to compare a value with NULL you must use IS NULL or IS NOT NULL. *You may also use the less common IS UNKNOWN predicates for some tasks.

[Former Member]

To compare nullable columns or expressions, as of SQL Anywhere 12 one can use the IS NOT DISTINCT FROM search condition, which has the advantage of being sargable.