How to compare for the null-ness - Zester

Is there a way to compare 2 columns if they are both null or both non-null
besides the following way?

select *
from MyTable
where ( Column1 is null AND Column2 is null ) or (Column1 is not null and
Column2 is not null )

thanks!

How to compare for the null-ness - Russell Fields

Zester,

Yes, but I don't know that it performs any better.  The following assumes a
numeric, but you can think of variants for other data types. Here is for a
numeric:

SELECT *
FROM MYTABLE
WHERE COALESCE(SIGN(Column1-Column1),1) =
COALESCE(SIGN(Column2-Column2), 1)

Since SIGN of a number subtracted from itself will return 0, coalescing to 1
will correctly represent NULL state.  Of course, this all blows out
intelligent use of indexes on these columns, in case that mattered.

For character strings try subtracting the length of the string from itself
and do the above trick.  Etc, etc.

RLF

How to compare for the null-ness - Zester

Hi Russell,

It doesn't look that much better than what I already have. Did i miss
anything? It might be worse because it calls 4 functions for each row. I
assume that there is overhead cost for function calls in sql statement as
well.

How to compare for the null-ness - Russell Fields

Zester,

The function calls are quite quick, so they don't add much overhead.  But
the intent behind the query is much less clear than your original version.
Once upon a time these were the thing for performance, but SQL Server has
moved on in most cases.

The only thing that might make a difference is that you are using an OR
which also interferes with index use. So, this is a >> trick <<, not a
coding standard, that may improve performance slightly at the cost of
looking strange.   However, the SQL optimizer is a lot smarter than it once
was.

My sample was of a "characteristic function"   One place to read just a
little about them is from a 1999 book: Transact-SQL Programming By Kevin E.
Kline, Lee Gould, Andrew Zanevsky
http://books.google.com/books?id=CWf1Pv6OeAcC&pg=PA668&lpg=PA668&dq=%22characteristic+function%22+%22sql+server%22&source=web&ots=8-T2ukKu5_&sig=ibgxxNfWuzX4wr75iIlnqrEyYVs#PPA668,M1

FWIW,
RLF

How to compare for the null-ness - Tom Cooper

Your original question was "is there a way ...?"  The answer is obviously
yes.  In addition to Russell's answer, you could use the following which
will work no matter what datatype the columns are:

Where Case When Column1 Is Null Then 1 Else 0 End
+ Case When Column2Is Null Then 1 Else 0 End
In (0,2)

But if your question was meant to be "is there a BETTER way ...?", then I
suspect the answer is no.

Tom

How to compare for the null-ness - Zester

thanks for pointing that out. Yes, I already had a way, so I was looking for
a better way.

How to compare for the null-ness - Zester

I've been always wondering why doesn't it allow us to do this?
where ( col1 is null ) = ( col2 is null )

( col1 is null ) returns bool, ( col2 is null ) returns bool, why can't we
compare 2 boolean values?

How to compare for the null-ness - Hugo Kornelis

Hi Zester,

Because SQL Server does not implement a boolean data type.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

How to compare for the null-ness - Russell Fields

Hugo, I hope this does not start another Great Boolean Debate. - RLF