subquery returns 0 rows-----HELP
983397Jan 7 2013 — edited Jan 8 2013I need to understand something.
please follow my points:-
1-If we try to make a NOT IN condition, and one value is NULL, the main query return no result, because the NOT IN condition evaluates to FALSE "NULL" "UNKNOWN".
example:-
SELECT 'True' FROM employees
WHERE department_id NOT IN (10, 20, NULL);
/*
this query returns no rows, simply because the condition is parsed like this
department_id != 10 AND department_id != 20 AND department_id != null
*/
^^^I have no question regarding this point and it is quite obvious.
2-If the subquery returns 0 rows, then the value of the scalar subquery expression is NULL.
example:-
create table subq_null_test
(
num number(4),
val varchar2(7)
);
insert into subq_null_test
values (1,'one');
insert into subq_null_test
values (2, (select 'two' from dual where 2=1));
insert into subq_null_test
values (3, 'three');
commit;
and by
select * from subq_null_test;
we would see a NULL inserted as value for number 2
NUM VAL
1 one
2 (null)
3 three
/*
so far so good, indeed the 0 row subquery returned NULL
*/
^^^Also I CAN'T have a point here.
============================================
but lets look at these 3 queries
-------->FIRST
select department_id, last_name
from employees
where department_id not in (10,20,null)
;
/*no rows selected*/
--------->SECOND
select department_id, last_name
from employees
where department_id not in (10,20,(select 10 from dual where 2=1))
;
/*no rows selected*/
-------->THIRD
select department_id, last_name
from employees
where department_id not in (select 10 from dual where 2=1)
;
/*ROWS returned*/
my question is:-
WHY FIRST and SECOND queries behaved as expected, while the THIRD didn't ???
-I had a look at the execution plan, and it didn't helped me "am a beginner anyways"
-I know its something related to the process or parsing the conditions, but am totally unable to locate it...
Any help would be so much appreciated,
Thanks for all.
Ghazal.