full outer join issue?

Ok i'm evidently an idiot, i can't seem to get full outer join to work the 
way I'd expect it to:

select e.last_name, d.department_name
from departments d full outer join employees e
on (e.department_id = d.department_id)
where e.department_id is null;

select e.last_name, d.department_name
from employees e full outer join  departments d
on (d.department_id = e.department_id)
where d.department_id is null;


based on the data, the 1st query returns me the one row where an employee 
doesn't have a dept AND all of the departments that do NOT have employees. 
 so far so good.

I've changed the 2nd query every which way possible, fully expecting the 
same result set, moved the FROM tables back and forth expecting to get the 
same results, yet all i get with query #2 is the some employee who is not 
in a dept and NO dept records.

11gr2, on linux, if that matters.

if someone wants the data, i'll be glad to export it out and send the .dmp 
file to ya.

thanks, joe



_______________________________________
Joe Testa, Oracle Certified Professional 
Senior Engineering & Administration Lead
(Work) 614-677-1668
(Cell) 614-312-6715

Other related posts: