SQL: Right outer join

Right outer join retrieved all data from the source table and extract only required columns or data from the target table.

Syntax:

Select columns... from target_table right outer join Source_table on(expression);

To explain the Working of right outer join we create tables .

Note::: I am working on hr schema.

Query:
create table emp
(
emp_id number(3) constraints emp_id_pk primary key,
emp_name varchar2(30),
emp_salary number(4)

);
Result:
table emp created.

Now create a dept table  with the constraint foreign key.

Query:
create table dept
(
dept_id number(3) constraints dept_did_pk primary key,
dept_name varchar2(30) ,
emp_id constraint dept_eid_fk references emp(emp_id)
);

Result:
table created.

Insert data into the table as follows

Queries:
insert into hr.emp (emp_id,emp_name, emp_salary) values (1,'Talha','2000');
insert into hr.emp (emp_id,emp_name, emp_salary) values (2,'Zubair','20000');
insert into hr.emp (emp_id,emp_name, emp_salary) values (3,'salman','25000');
insert into hr.emp (emp_id,emp_name, emp_salary) values (4,'shani',30000);
insert into hr.emp (emp_id,emp_name, emp_salary) values (5,'Waqass',22000);

Result:
5 rows inserted.

Inserting data into the dept table.

Quries:
insert into hr.dept (dept_id,dept_name, emp_id) values (1,'IT',1);
insert into hr.dept (dept_id,dept_name, emp_id) values (2,'Oracle',2);
insert into hr.dept (dept_id,dept_name, emp_id) values (3,'Finance',3);
insert into hr.dept (dept_id,dept_name) values (4,'Accounts');

insert into hr.dept (dept_id,dept_name) values (5,'Marketing');

Result:

5 rows inserted.

Right Outer Join With the On clause:
Query:
select emp_name, dept_name from dept right outer join emp on(emp.emp_id = dept.emp_id);

Result:
Talha IT
Zubair Oracle
Salman Finance
shani

Waqass

In the above query emp_name belongs to source table, and dept_name is the coulumn of target table.
Result shows that right outer join retrive all data of the column from the souce table and only required data from the target table.

Right Outer Join With the Using clause:

Right outer join with using clause is used when the data type and the name of column is the same.Or when we are performing equie join, thats means that whenever we use equal to comparing on columns over which we want to put join.

Query:
select emp_name, dept_name from dept right outer join emp using(emp_id);

Result:
Talha IT
Zubair Oracle
Salman Finance
shani

Waqass

Right Outer Join With the Where clause:
we use where clause when we want to filter the result return by or query. In previous clause we extract the name of employees thats are working. now if we want to retrieve the name of employees whos salary is greater than twenty thousand.
      It will be the same as previous query but editional with where clause.

Query:
select emp_name, dept_name from dept right outer join emp using(emp_id) where(emp.emp_salary>20000);

Result:
Salman Finance
shani

Waqass