full outer join issue?
- From: TESTAJ3@xxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 26 Apr 2010 16:06:29 -0400
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