Let's build a simple test case:
create table test1 (a number, d varchar2(30))result:
create table test2 (b number)
insert into test1 values(null, 'a is null ')
insert into test1 values(1, 'a = 1')
insert into test2 values(null)
insert into test2 values(1)
select * from test1
where a in (select b from test2);
A DIf for some reason you're assuming that null=null should return TRUE, well, you're taking a wrong assumption for two simple reasons:
1 a = 1
- NULL = NULL always returns FALSE.
- NULL must be dealt with using the IS operator, not the equal (=) sign.
If you imagine that the IN clause could be regarded as a series of equality comparisons, you can easily make out why it can fail if values on either side of the comparison are NULLs.
In case for some reason you want to force a = b to return TRUE even in case of NULLs, then you can try this sub-optimal technique:
select * from test1applying NVL to column a however causes the optimizer to rule out any index on table test1, which will probably result in a full table scan and the consequent poor performance of the query if table test1 is big.
where nvl(a,-1) in (select nvl(b,-1) from test2);
a is null
1 a = 1
These types of problems can be further complicated when you use n-tuples of values.
create table test3 (a number, b number, d varchar2(30))Result:
create table test4 (c number, d number)
insert into test3 values(null, null, 'a and b are null ')
insert into test3 values(1, null, 'a = 1 and b is null')
insert into test3 values(1, 1, 'a = 1 and b = 1')
insert into test4 values(null, null)
insert into test4 values(1, null)
insert into test4 values(1, 1)
select * from test3
where (a,b) in (select c,d from test4);
A B D
- - ---------------
1 1 a = 1 and b = 1
having NULLs in the columns you retrieve may lead to unexpected results when using the IN clause, so you need to know very well the data you are working on.
You may think that this sort of tongue-in-cheek advice, however when you're called on to explain why a query doesn't work as expected on a table populated by someone else in a foreign application and without much possibility of inspecting the data yourself, you need to be prepared for the worst.