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.
Regarding NULLs, you need to consider three-valued logic. The following page in the documentation helps explain this:
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.
(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.
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?
answered 10 Jan '12, 17:52