NULL usage in WHERE IN SQL statement

sql, sql-server, t-sql, where-in

Solution

If you set ANSI_NULLS OFF first, you can use IN (Null) Fine.

Comparisons to NULL can't be performed (=, >, < etc) with ANSI_NULLS ON

SET ANSI_NULLS OFF
Select 
RPAD(x.QUOTE_ID,20,' ')
from csa_sli_all.T_CONV_XREF_CUST_QUOTE x ,
        csa_sli_all.T_CONV_quote q
where   q.select_indicator is null and 
    q.QUOTE_ID = X.QUOTE_ID and 
    q.HOLD_CODE IN ('CAQ' , NULL )

Should work fine

Problem

I have below query ``` Select RPAD(x.QUOTE_ID,20,' ') from csa_sli_all.T_CONV_XREF_CUST_QUOTE x , csa_sli_all.T_CONV_quote q where q.select_indicator is null and q.QUOTE_ID = X.QUOTE_ID and q.HOLD_CODE IN ('CAQ' , NULL ) ``` it doesnt give me required result. I changed last statement to ``` where q.select_indicator is null and q.QUOTE_ID = X.QUOTE_ID and (q.HOLD_CODE = 'CAQ' or q.hold_code is null) ``` Now its giving me desired result. My question is 1 can't we use `NULL` in `WHERE IN` clause ? 2 If yes HOW 3 Going by the logic ( not syntax : I know 1st syntax is wrong ) both scenarios will give same answers ?

Original source