Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

subquery returns 0 rows-----HELP

983397Jan 7 2013 — edited Jan 8 2013
I 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 5 2013
Added on Jan 7 2013
12 comments
2,957 views