Self join in SQL Oracle: Why does the right outer join not retain all the values of the table

Why does the right outer join (of the example below) not give the full dataset of the table like the left outer join does, since outer joins always retain the rows of the corresponding table?

Create Table

create table join_test
    (id number unique not null, name varchar2(10), department varchar2(10));

Populate Table

select * from join_test;

ID NAME            DEPARTMENT   
1 stelios          sa 
2 andros           sa 
3 stav             ba  
4 mary             ba  
5 antonia          la  
6 lambros          ka  

Inner Join

select j1.name, j1.department
from join_test j1 join join_test j2
on (j1.department=j2.department and j1.name<>j2.name);

NAME         DEPARTMENT
andros          sa
steliso         sa
mary            ba
stav            ba

Left Outer Join

select j1.name, j1.department
from join_test j1 left join join_test j2
on (j1.department=j2.department and j1.name<>j2.name)

NAME       DEPARTMENT
andros     sa
steliso    sa
mary       ba
stav       ba
antonia    la
lambros    ka

Right Outer Join

select j1.name, j1.department
from join_test j1 right join join_test j2
on (j1.department=j2.department and j1.name<>j2.name)

NAME       DEPARTMENT
steliso    sa
andros     sa
stav       ba
mary       ba

Changing select list to j2

select j2.name, j2.department
from join_test j1 right join join_test j2
on (j1.department=j2.department and j1.name<>j2.name)

NAME       DEPARTMENT
andros     sa
steliso    sa
mary       ba
stav       ba
antonia    la
lambros    ka

Right and Left joins perform the same function. What's different in your examples is the tables that you are SELECTing from.

These two queries:

select j2.name,j2.department
from join_test j1 left join join_test j2
on(j1.department=j2.department and j1.name<>j2.name)

and

select j1.name,j1.department
from join_test j1 right join join_test j2
on(j1.department=j2.department and j1.name<>j2.name)

Produce the same result:

NAME    DEPARTMENT
stelios sa
andros  sa
stav    ba
mary    ba
(null)  (null)
(null)  (null)

The reason for the difference in results that you see between your left and right queries is that in the left example, you are SELECTing from the "driving" table (the left table, J1). The join is showing all rows from the driving table (J1), and matching rows (which are not displayed) from the right-hand, or non-driving table (J2).

In your right example, you are changing the join but still selecting from J1. Since J1 is now the non-driving table, you are only seeing the matched results from J1. If you add J2 columns to the select, you will see all of its rows:

NAME        DEPARTMENT   NAME       DEPT
stelios     sa           andros     sa
andros      sa           stelios    sa
stav        ba           mary       ba
mary        ba           stav       ba
(null)      (null)       antonia    la
(null)      (null)       lambros    ka

You will see this same result with a LEFT join, but the nulls would be no the other side.

In both cases, the (null) rows represent the rows from the driving table that are not matched in the non-driving table.