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 ?

score 4 · Accepted Answer · answered Apr 19 '12 at 11:22

4

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

answered Apr 19 '12 at 11:22

JamesT

2,988
23
29

Ok, that sounds like something I would highly discourage anyone from doing. Ignoring the standard just for the sake of being able to compare `NULL`s? This could lead to big confusions while developing, maintaining and deploying your application. – Dmytro Shevchenko Apr 19 '12 at 12:57

score 3 · Answer 2 · answered Apr 19 '12 at 11:17

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

score 2 · Answer 3 · answered Apr 19 '12 at 11:16

2

You cannot use NULL in IN statements, because IN applies the standard = operator. This one, of course, fails when comparing NULLs.

It's like writing SELECT NULL = NULL. Returned value would be NULL.

answered Apr 19 '12 at 11:16

Dmytro Shevchenko

33,431
6
51
67

score 2 · Answer 4 · answered Apr 19 '12 at 11:19

2

Not sure, but I dont think you can use NULL in where clause.

I always get confused with =NULL and IS NULL

why dont you use ISNULL ie ...

 WHERE ISNULL(q.HOD_CODE,'CAQ') = 'CAQ'

answered Apr 19 '12 at 11:19

Doctor Chris Chris

211
1
3
13

score 2 · Answer 5 · edited Jan 18 '21 at 12:30

2

NULL IN (NULL) evaluates to UNKNOWN. To compare null values you should use is null operator. Null has an specific treatment.

Sample:

select case when null in (null) then 1 else 0 end

returns 0

Remember Codd rule 3:

Rule 3: Systematic treatment of null values: The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number", in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.

edited Jan 18 '21 at 12:30

Community

1
1

answered Apr 19 '12 at 11:23

dani herrera

48,760
8
117
177

Remember nulls weren't in Codd's original relational model? And that he later rejected the rule you quote in favour of two kinds of null, a proposal which was almost universally rejected? See: Wikipedia [Null (SQL)](http://en.wikipedia.org/wiki/Sql_null): "In The Relational Model for Database Management: Version 2, Codd suggested that the SQL implementation of Null was flawed and should be replaced by two distinct Null-type markers..." – onedaywhen Apr 19 '12 at 12:32
@onedaywhen, oh! thanks about ilustrate post with this quote. I thing that at this time this codd sugestion is deserted: "... Because of this additional complexity, the idea of multiple Null-type values has not gained widespread acceptance." – dani herrera Apr 19 '12 at 12:44
Bottom line: I don't think you can use Codd in defence of SQL nulls because he rejected SQL nulls and everyone else rejected Codd's nulls! – onedaywhen Apr 19 '12 at 13:51
@onedaywhen, what is for you the major author reference for Null concept in databases? – dani herrera Apr 21 '12 at 18:00
The major author would IMO be Chris Date. There is an interesting article by both Codd and Date, [Much Ado About Nothing](http://www.dbdebunk.com/page/page/1706814.htm). If you are looking for a critique of nulls on SQL, consider this article by Hugh Darwen: [On the Nothing That's Wrong With Nulls](http://www.dbdebunk.com/page/page/2928212.htm). – onedaywhen Apr 23 '12 at 07:26

score 2 · Answer 6 · answered Apr 19 '12 at 11:25

You will have to use is null instead = null.

While comparing a field to NULL, a SQL query should use the IS operator in place of the "=" operator. The reason is that if the ANSI_NULLS option is On for a connection, expressions being compared to a NULL value will not return a true or false value but rather they will return an unknown value. To make it simpler, if a table xxx has a column by name col1 that contains two NULL values then this query would result in fetching zero records:

Select * from xxx where col1 = NULL

Meanwhile, this query would result in fetching two records:

Select * from xxx where col1 IS NULL

However, if you set ANSI_NULLS off then both of these queries would return two records.

score 1 · Answer 7 · answered Apr 19 '12 at 11:20

Instead of using a subquery, you'll have to use a join. Say for argument's sake you're using SQL Server, do something like (obviously filling in your fields):

SELECT yourField, yourField2 
FROM yourTable t1
INNER JOIN yourTable2 t2 ON t1.Id = t2.Id
WHERE t1.field is null
AND (t1.code = 'CAQ' OR t1.code IS NULL)

NULL usage in WHERE IN SQL statement

7 Answers7