NULL usage in WHERE IN SQL statement

Question

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 ?

Answer 1

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

Answer 2

You can't use null values in a where clause using IN, so if you need to use it, use it like this:

q.HOLD_CODE in ('CAQ', ...) or q.hold_code is null

If you are looking for an null value you always have to use "is null" hence you can't use it in the IN statement