لماذا هذا الكود لايعطى نتيجة

[mahmoud shoman]

select employee.ename
from emp employee
where employee.empno not in
(select manager.mgr from emp manager )

على الرغم ان الكود هذا يعطى نتيجة

select employee.ename
from emp employee
where employee.empno in
(select manager.mgr from emp manager )

[Dr.Lava]

Because all emp_id's are not in nothing

The above SQL statement 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. The NOT IN operator is equivalent to !=ALL.
Notice that the null value as part of the resultant set of a subquery will not be a problem if you are using the IN operator. The IN operator is equivalent to =ANY

[Amgad]

السلام عليكم ورحمة الله

بالاضافة الى ما ذكرة الأخ / DR.Lava

يمكنك استخدام ال nvl للتعامل مع ال null value

SQL> select employee.ename
 2  from emp employee
 3  where employee.empno not in
 4  (select nvl(manager.mgr,0) from emp manager )
 5  /

ENAME
----------
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER

8 rows selected.

[صدقة جارية]

كل الكلام السابقى كلام سليم 100%
ولكن الافضل والاصح واللى اوراكل نفسه بستعمله فى الابكليشن بتاعها
هو استخدام exist or no exist
وذلك فى الحقول التى يمكن ان تحتفظ بقيمة NULL
بمعنى الكود يكون

select employee.ename 
from emp employee 
where exist (selectmanager.mgr from emp manager  where emp_no =selectmanager.mgr ) 

[mahmoud shoman]

dr.lava, amgad ,صدقة جارية
شكرا جزيلا على ردودكم الاكثر من رائعة وبجد دة مش جديد على المنتدى الرائع بتاعنا