We have a system which builds T-SQL queries from objects. This system deliberately throws exception if trying to use empty lists for IN() condition.

I am thinking of replacing this behaviour with IN (NULL) operation. From what I tried, this returns no rows, even for values which are null.

This is ideal behavior for my situation.

But in MS documentation, there is this remark:

Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results.

Can IN (NULL) be used to consistently return no rows? Is this considered a bad practice?

CodePudding user response：

[Column] IN (NULL) is equivalent to [Column] = NULL

In the default setting of SET ANSI_NULLS ON, [Column] = NULL always returns an empty result set.

So, as long as you are interested in

it returns no records even for values which are null [sic]

, it will work.

An argument is that this is obscure code, and anyone without that knowledge (and possibly even you after a few months) will possibly deem the non-returning of NULL valued rows as "unexpected", and might try to "fix" it.

CodePudding user response：

Technically null cannot be compared with any other value(even not with null itself). From the second line of TSQL doc what you have stated clearly explains that is is a bad practice("Using null values in together with IN or NOT IN can produce unexpected results")