SybaseBlog

Here is where my story begins. For potential performance reasons, I was trying to change a "where" clause that looked like this:

ta.b & isnull (tb.b, ta.b) = isnull (tb.b, ta.b)

to one that looked like this:

ta.b & tb.b = tb.b

(ta and tb are different tables)

But I wasn't sure if this would work if tb.b was null. Would ta.b & null = null? So I did this test:

declare @a int
declare @b int
select @a = null
select @b = 1
select @b where (@b & @a = @a)
go
-- This returns 1 result.

This means @b & @a = @b is true if @a is null. So I thought I could go ahead. But I was wrong! Observe:

create table TableA (a int, b int null)
insert into TableA values (1, 1)

create table TableB (a int, b int null)
insert into TableB values (1, null)

select ta.b from TableA ta, TableB tb
ta.b & isnull (tb.b, ta.b) = isnull (tb.b, ta.b)
go
-- This returns 1 result

select ta.b from TableA ta, TableB tb
ta.b & tb.b = tb.b
go
-- This returns 0 results

What is the explanation for this?

The quick answer is that Sybase ASE treats NULLs differently in search clauses (the first case) than in join clauses (the second case, when you use a "where").

Search clauses: NULL = NULL
Join clauses: NULL = nothing, not even itself

Where does this come from? Oddly enough, from the ANSI SQL-92 Standard itself. The following quote is from Sybooks (look for the description of the "ansinull" configuration parameter):

"The SQL standard requires that if either one of the two operands of an equality comparison is NULL, the result is UNKNOWN. Transact-SQL treats NULL values differently. If one of the operands is a column, parameter, or variable, and the other operand is the NULL constant or a parameter or variable whose value is NULL, the result is either TRUE or FALSE."

Incidentally, "ansinull" is a Sybase ASE configuration parameter that will toggle the treatment of NULLs from ANSI to Sybase ASE.

ANSI: NULL = NULL is NULL, which is neither TRUE nor FALSE, it is UNKNOWN
Sybase ASE: NULL = NULL is TRUE

For example, consider TableB above and run this query:

select * from TableB where b <> "1"

ANSINULL = ON: This returns 0 results (ie. not the NULL row)
ANSINULL = OFF: This returns 1 result (ie. the NULL row)

If you want to get a result, but don't want to mess with ANSINULL, here are two approaches:

select * from TableB where b != 1 or b IS NULL
select * from TableB where isnull(b,0) != 1

Also make note that in Sybase ASE, an empty string doesn't exist, because that is the string representation of NULL.

So what is a guy to do?

The answer is to remember that NULLs don't always behave as you'd expect, and to follow these guidelines:
1. Use functions like ISNULL and NULLIF, and even COALESCE
2. Don't use = or != with NULL, use 'IS NULL' and 'IS NOT NULL' instead

ISNULL: Takes two values, using the second one if and only if the first one is NULL
NULLIF: Takes an expression and a value, returns NULL if the expression is true, otherwise the value
COALESCE: Takes a list of at least 2 values, and returns the first one that is NOT NULL