Summary -

In this topic, we described about the RIGHT OUTER JOIN with detailed example.

RIGHT OUTER JOIN is used to combine two tables rows that it get all rows from table which is right side of the JOIN and matched rows from table which is left side of the JOIN. RIGHT OUTER JOIN also known as RIGHT JOIN. While using RGHTT JOIN left side table rows which does not have matched values with right side table then in the result set we get NULL values.

Syntax for getting rows by using joins –

SELECT column1,column2,table2.column1,table2.column2,….
 FROM table1 RIGHT JOIN
table2 ON table1.column_name = table2.column_name;  

Syntax for creating view by using joins –

CREATE VIEW view_name AS column1,column2,coulumn3,…
FROM table1 RIGHT JOIN 
table2 ON table1.column_name = table2.column_name;    

Syntax for deleting table | view by using joins –

DELETE table | view FROM table1  RIGHTJOIN
table2 ON table1.column_name = table2.column_name  [WHERE condition ]; 

Syntax for updating table | view by using joins –

UPDATE table|view SET column_name = value FROM table1 RIGHT JOIN 
table2 ON table1.column_name = table2.column_name[WHERE condition ]; 

Example -

consider employee_details and department_details tables are as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000
010 Employee10 Analyst Employee5 2019-07-11 27000.00 4000
011 Employee11 Salesman Employee5 2019-09-09 10000.00 4000

department_details -

dept_id dept_name dept_location
1000 Office Location1
2000 Sales Location2
3000 Marketing Location3

Scenario – Creating view by using RIGHT JOIN.

Requirement – Creating employee_info view by using RIGHT JOIN ON employee_details and department_details tables. The query was as follows –

CREATE VIEW employee_info AS SELECT emp_id, emp_name,
department_details.dept_name FROM employee_details
RIGHT JOIN department_details ON 
employee_details.dept_id = department_details.dept_id; 

It is also written as follows and the output should be same in both cases.

CREATE VIEW employee_info AS SELECT emp_id, emp_name,
department_details.dept_name FROM employee_details
RIGHT OUTER JOIN department_details ON
employee_details.dept_id = department_details.dept_id; 

By executing above query we can get all rows of specific columns from department_details table and matched rows from employee_details table. The output was as follows -

emp_id emp_name dept_name
001 Employee1 Office
002 Employee2 Sales
003 Employee3 Office
004 Employee4 Sales
005 Employee5 Office
006 Employee6 Office
007 Employee7 Office
008 Employee8 Sales
009 Employee9 Sales
Marketing

Scenario – Creating view by using RIGHTT JOIN without matching values in another table.

Requirement – Creating employee_info view by using RIGHT JOIN ON employee_details and department_details tables. The query was as follows –

let us consider we have department table as follows –

department -

dept_id dept_name dept_location
5000 Service Location4

CREATE VIEW employee_info AS SELECT emp_id, emp_name, 
department.dept_name FROM employee_details
RIGHT  JOIN department ON
employee_details.dept_id = department.dept_id; 

By executing we can get all details from department table which is right side of the JOIN and matched values from left side table but There is no matched values in the employee_details table. So in the result set we can get NULL values in result set.

emp_id emp_name dept_name
Marketing

Scenario – Deleting table by using RIGHT JOIN.

Requirement – Deleting employee_details table by using RIGHT JOIN. The query was as follows–

DELETE employee_details FROM employee_details
RIGHT JOIN department_details ON 
employee_details.dept_id = department_details. dept_id; 

By executing above query we can delete all details of employees in employee_details table whose dept_id match with the department_details table dept_id by using RIGHT JOIN. The output was as follows –

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
010 Employee10 Analyst Employee5 2019-07-11 27000.00 4000
011 Employee11 Salesman Employee5 2019-09-09 10000.00 4000

Scenario – Updating table by using RIGHT JOIN.

Requirement – Updating date_of_hire of all employees in employee_details table by using RIGHT JOIN. The query was as follows –

UPDATE employee_details SET date_of_hire ='2019-09-09'
 FROM employee_details
RIGHT JOIN department_details ON
employee_details.dept_id = department_details. dept_id; 

By executing above query we can update all employees date_of_hire with 2019-09-09 on employee_details whose dept_id match with dept_id of department_details table by using RIGHT JOIN. The output was as follows -

employee_details

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-09-09 45000.00 1000
002 Employee2 Director 2019-09-09 40000.00 2000
003 Employee3 Manager Employee1 2019-09-09 27000.00 1000
004 Employee4 Manager Employee2 2019-09-09 25000.00 2000
005 Employee5 Analyst Employee3 2019-09-09 20000.00 1000
006 Employee6 Analyst Employee3 2019-09-09 18000.00 1000
007 Employee7 Clerk Employee3 2019-09-09 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-09-09 13000.00 2000
010 Employee10 Analyst Employee5 2019-07-11 27000.00 4000
011 Employee11 Salesman Employee5 2019-09-09 10000.00 4000

Scenario – Getting details from two tables by using RIGHT JOIN.

Requirement - Getting details from employee_details, department_details tables by using RIGHT JOIN. The query was as follows –

SELECT emp_id,emp_name,designation,salary,department_details.dept_name
FROM employee_details RIGHT JOIN department_details ON
employee_details.dept_id = department_details.dept_id; 

By executing above query we can get matching rows in employee_details with department_details table and all details from department_details table. The output was as follows -

emp_id emp_name designation salary dept_name
001 Employee1 Director 45000.00 Office
002 Employee2 Director 40000.00 Sales
003 Employee3 Manager 27000.00 Office
004 Employee4 Manager 25000.00 Sales
005 Employee5 Analyst 20000.00 Office
006 Employee6 Analyst 18000.00 Office
007 Employee7 Clerk 15000.00 Office
008 Employee8 Salesman 14000.00 Sales
009 Employee9 Salesman 13000.00 Sales
Marketing