I'm a bit confused about the ANSI_NULL setting in SQL 2000 (sp3).
Given the following script:

-- Start script
SET ANSI_NULLS OFF

SELECT
a.c AS a
,b.c AS b
FROM       (SELECT NULL c ) a
INNER JOIN (SELECT NULL c ) b ON b.c = a.c
-- End script

I would expect this to return one row with two columns, both containing
the value NULL (because NULL=NULL should yield true with ANSI_NULLS set
to off). Appearantly it does not. Does anyone know how I could achieve
this result?

tc

tjibbech***@gmail.com wrote:
Show quoteHide quoteLeave ANSI_NULLS permanently ON unless you require compatibility with
legacy code. Nulls cause enough trouble even without the extra
non-standards quirks.

SELECT
a.c AS a
,b.c AS b
FROM       (SELECT NULL c ) a
INNER JOIN (SELECT NULL c ) b
ON COALESCE(b.c,'foobar') = COALESCE(a.c,'foobar');

This begs the question, why would it make logical sense to join nulls
in that way? With a better design it ought to be possible to avoid
doing so.

No - BOL is quite clear on the topic.  You made a bunch of extrapolations
and assumptions.  BOL says a comparison in the where clause involving a
column comparison with the NULL keyword will succeed.  It doesn't address
column to column comparison at all.  Below is the rather bizarre syntax you
would need to use - I can't think of any good reason to use it.

SET ANSI_NULLS OFF

SELECT
a.c AS a
,b.c AS b
FROM       (SELECT NULL c ) a
cross JOIN (SELECT NULL c ) b
where b.c = null
and a.c = null

SELECT a.*, b.*
FROM (SELECT NULL c ) a,
(SELECT NULL c ) b
where b.c = null
and a.c = null

Ever growing transaction log, using simple recovery?
Help with efficiently storing a variety nested queries
Max Row Date
EncryptByCert problem
The pains of XML programming