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.

[VolkerBarth]

(Preface: Surely a true SQL authority like Glenn Paulley will give the full answer...)

I would highly recommend to use the "col IS NULL" comparison predicate for WHERE and HAVING clauses as that truly fits SQL's three valued logic. An alternative would be to use "col = NULL IS UNKNOWN".

The "col = NULL" syntax may also return TRUE, particularly for Transact-SQL compatibility, e.g. when the ansinull option is set to off. However, then the evaluation may be dependent on the setting of such an option (which will be set automatically by certain client types..). That doesn't look too reliable in my book...

---

The "col = NULL" syntax in a UNION seems to be a totally different topic. IMHO, that might be the T-SQL syntax to use an alias in the select list, such as

select col1, col2, col3 = col1 + col2, col4 = null from dummy

which would be equivalent to the following Watcom-SQL syntax:

select col1, col2, col1 + col2 as col3, null as col4 from dummy

and therefore is just a way to specify NULL as a select list item and give that expression a name.

[Former Member]

Using col = NULL (and expecting TRUE) is Transact-SQL syntax. That predicate will return TRUE if and only if ANSINULL is set to OFF, and therefore I never recommend its use because changing SQL dialects now changes results. In ANSI-standard SQL, anything compared to NULL evaluates to UNKNOWN and in a false-interpreted situation such as a WHERE clause the predicate will be interpreted as FALSE.

Sybase ASE also supports the IS [NOT] NULL predicate so there is no reason to stick to the = NULL usage.

Volker's remarks about the use of = in a SELECT list, which is also a T-SQL construction, requires some clarification. SELECT in T-SQL is overloaded; it is (also) Sybase ASE's implementation of the SET statement. So one would specifiy

SELECT X = NULL

to set the variable X to NULL, rather than the Watcom syntax

SET X = NULL;

Yes, in Transact-SQL the = is ALSO overloaded for alias specification, ie

SELECT X = Y, B = G

adds aliases to each select list expression, equivalent to

SELECT X AS Y, B AS G

in the Watcom dialect. Assignment in T-SQL is only permitted when the SELECT statement has no FROM clause and there is a single select-list expression.

[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.

[VolkerBarth]

FWIW, the following FAQ might be helpful, too.

---

BTW: I have noticed that with the help of the "Related questions" bar on the right - a very useful feature as long as questions are somewhat reasonable tagged:) - Breck, so waddayathank?