How does ANSI_NULLS work in TSQL?
null, sql-server-2008, t-sql
Solution
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.
Problem
`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 ```