sql - Comparison with NULL behavior the same even when setting ANSI_NULLS -

some_column (table abc) ___ 123     456     null    789 

when run select * abc returns rows. however, when run

select * abc some_column <> ''

it returns values null. whether ansi_nulls on or off.

can explain peculiar behavior me?

it's design. the doc (emphasis mine):

when set ansi_nulls off, equals (=) , not equal (<>) comparison operators not follow iso standard. select statement uses column_name = null returns rows have null values in column_name. select statement uses column_name <> null returns rows have nonnull values in column. also, a select statement uses column_name <> xyz_value returns rows not xyz_value , not null.

so, where some_column <> '' returns rows not '' , not null.

this query ansi_nulls off:

select *  abc  some_column <> ''; 

is equivalent query:

select *  abc  some_column <> ''     , some_column not null; 

when ansi_nulls on, of course, normal ansi three valued logic applies. null never equal anything, including null. null never not equal anything, including null.

either way, should expect use some_column not null or some_column null , explicitly handle null values.

select *  abc  some_column <> ''     or some_column null