How does ANSI_NULLS work in TSQL?

Question

SET ANSI_NULLS OFF seems to give different results in TSQL depending on whether you're comparing a field from a table or a value. Can anyone help me understand why the last 2 of my queries give no results? I'm not looking for a solution, just an explanation.

select 1 as 'Col' into #a
select NULL as 'Col' into #b

--This query gives results, as expected.  
SET ANSI_NULLS OFF
select * from #b
where NULL = Col

--This query gives results, as expected.
SET ANSI_NULLS OFF
select * from #a
where NULL != Col

--This workaround gives results, too.
select * from #a a, #b b
where isnull(a.Col, '') != isnull(b.Col, '')

--This query gives no results, why?
SET ANSI_NULLS OFF
select * from #a a, #b b
where a.Col != b.Col

--This query gives no results, why?
SET ANSI_NULLS OFF
select * from #a a, #b b
where b.Col != a.Col

Answer 1

Anything compared to a null value fails. Even comparing two null values will fail. Even the != will fail because of the (IMHO) stupid handling of NULL.

That said, the != queries could be rewritten to say:

select * from #a a where a.Col not in (select b.Col from #b b)

The last query is identical to the second to last query as the order of the comparison doesn't matter.

Incidentally, your workaround works simply because you are testing for a null value in the #b.Col column and explicitly converting it to a '' which then allows your query to do a string compare between them. An alternative way of writing that would be:

select * from #a a, #b b
where a.Col != COALESCE(b.Col, '')

Answer 2

The reason the last two queries fail is that SET ANSI_NULLS ON/OFF only applies when you are comparing against a variable or the NULL value. It does not apply when you are comparing column values. From the BOL:

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.