sql server - SET ANSI_NULL Behavior -

set ansi_nulls on; -- query1 select 'stackoverflow' statement11 'name' in ('s','q', 'l', 'name', null); -- query 2 select 'stackoverflow' statement12 'name' not in ('s','q', 'l', null); 

why query 1 return results query 2 not return result?

from set ansi_nulls (transact-sql)

when set ansi_nulls on, select statement uses column_name = null returns 0 rows if there null values in column_name. select statement uses column_name <> null returns 0 rows if there nonnull values in column_name.

now, if use

set ansi_nulls off; -- query1 select 'stackoverflow' statement11 'name' in ('s','q', 'l', 'authority', null); -- query 2 select 'stackoverflow' statement12 'name' not in ('s','q', 'l', null); 

the second query return result.

sql fiddle demo

when set ansi_nulls on, comparisons against null value evaluate unknown. when set ansi_nulls off, comparisons of data against null value evaluate true if data value null.

edit

as mentioned @damien_the_unveliever

from in (transact-sql)

any null values returned subquery or expression compared test_expression using in or not in return unknown. using null values in in or not in can produce unexpected results.