Re: full outer join issue?
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: TESTAJ3@xxxxxxxxxxxxxx
- Date: Mon, 26 Apr 2010 13:58:29 -0700
The logic of the 2nd outer join is going something like this
FOR EACH EMPLOYEES RECORD
FIND a departments record matching on (d.department_id =
e.department_id)
IF you found a matching departments record then
create a logical (internal) record that looks like:
[e.department_id, e.last_name,d.department_id, d.department_name]
ELSE if you did't find a matching departments record
create a logican (internal record that looks like:
[e.department_id, e.last_name,d.department_id = NULL,
d.department_name = NULL]
END IF
IF d.department_id is NULL then
show the output -- e.last_name, d.department_name
else
show nothing
END FOR
Mike
On Mon, Apr 26, 2010 at 1:06 PM, <TESTAJ3@xxxxxxxxxxxxxx> wrote:
>
> 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:
