null value in subqueries

[mjsoule]

Please help me understand the following from Oracle Introduction to SQL and PL/SQL, Lesson 7 subqueries.

select ename
from emp
where empno NOT IN
(select mgr
from emp);

no rows returned.

Returning Nulls in the Resulting Set of a Subquery
The SQL statement above attempts to display all the employees who do not have any subordinates. Logically, this SQL statement should have returned eight rows. However, the SQL statement does not return any rows. One of the values returned by the inner query is a null value and hence the entire query returns no rows. The reason is that all conditions that compare a null value result in a null. So whenever null values are likely to be part of the resultant set of a subquery, do not use the NOT IN operator.

I read
The reason is that all conditions that compare a null value result in a null...
but the words do not explain... please help me to explain this concept to my students.

thanks MJ

 

[MalcolmW]

Think of NULL as unknown.
The inner query
(select mgr
from emp)
returns a NULL value, according to the lesson.
So, the full query is like the follwoing
select ename
from emp
where empno NOT IN
(unknown);
Obviously it doesn't make sense to say the result is anything but uknown as well.

However, I would recommend reading an introductory text to SQL, such as SQL for Dummies, before attempting to teach a course in the subject. You have some fundamental gaps in your SQL knowledge that you need to address before you can honestly present yourself as knowledgable on the subject.
Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[mjsoule]

The inner query
(select mgr
from emp)
returns a NULL value, according to the lesson.
So, the full query is like the following
select ename
from emp
where empno NOT IN
(unknown);
Obviously it doesn't make sense to say the result is anything but uknown as well.

I almost understand what you are saying,

The missing piece is that NULL is not the only returned value, several other values are also returned.
So, it seems that it does not make sense to discount the entire result because one of the values is NULL.

 

[MalcolmW]

It is the same sort of thing when you add some numbers together, and only one of them is unknown:
3 + 56 + 89 + 23 + 45 + 3 + 56 + 89 + 23 + 45 + NULL = NULL
You aren't "discounting" the result - you are just describing what it is - "unknown" or NULL.
You need to get comfortable with NULLs - they are an integral part of defining database content. Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?