Summary -

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

OUTER JOIN statement is used to get all rows which are present in both tables. OUTER JOIN is the combination of LEFT JOIN and RIGHT JOIN. OUTER JOIN fetch rows which are matched or not matched from both tables. The keyword FULL OUTER JOIN is used in the query.

Syntax for getting rows by using joins – 

SELECT column1,column2,table2.column1,table2.column2,….
FROM table1 FULL 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 FULL OUTER
JOIN table2 ON table1.column_name = table2.column_name;

Syntax for deleting table | view by using joins – 

DELETE table | view FROM table1   FULL OUTER JOIN
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 FULL OUTER JOIN
table2 ON table1.column_name = table2.column_name[WHERE condition ] ;

Example –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

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 – Getting rows from two tables by using FULL JOIN.

Requirement – Getting all rows present in both employee_details and department_details by using FULL JOIN. The query was as follows - 

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

By executing above query we can get all rows present in both employee_details and department_details tables. The output was as follows –

emp_id emp_name designation dept_name
001 Employee1 Director Office
002 Employee2 Director Sales
003 Employee3 Manager Office
004 Employee4 Manager Sales
005 Employee5 Analyst Office
006 Employee6 Analyst Office
007 Employee7 Clerk Office
008 Employee8 Salesman Sales
009 Employee9 Salesman Sales
010 Employee10 Analyst
011 Employee11 Salesman
Marketing

Scenario – Creating view by using FILL OUTER JOIN.

Requirement – Creating view employee_info with emp_id, emp_name, dept_name details from employee_details and department_details tables by using FULL OUTER JOIN. The query was as follows – 

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

By executing above query we can create view named as employee_info ang get rows from emp_id, emp_name, dept_name, dept_location columns present in both employee_details, department_details tables. The output was as follows -

emp_id emp_name designation dept_name dept_location
001 Employee1 Director Office Location1
003 Employee3 Manager Office Location1
005 Employee5 Analyst Office Location1
006 Employee6 Analyst Office Location1
007 Employee7 Clerk Office Location1
002 Employee2 Director Sales Location2
004 Employee4 Manager Sales Location2
008 Employee8 Salesman Sales Location2
009 Employee9 Salesman Sales Location2
010 Employee10 Analyst
011 Employee11 Salesman
Marketing Location3

Scenario – Updating rows or values from one table which are matched with another table by using INNER JOIN.

Requirement - Updating salaries of employees on employee_details table which are matched with department_details table by using INNER JOIN. The query was as follows -

UPDATE employee_details SET salary = 20000 FROM employee_details
FULL OUTER 
JOIN department_details ON employee_details.dept_id =department_details
.dept_id;

By executing above query we can update the salaries of all employee to 47000 on employee_details table. The output was as follows –

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

Scenario – Deleting rows from one table which are matched in another table by using INNER JOIN.

Requirement - Deleting details of employees from employee_details which are matched in department_details table by using INNER JOIN. The query was as follows – 

DELETE employee_details FROM employee_details
FULL OUTER JOIN department_details ON 
employee_details.dept_id = department_details. dept_id;

By executing above query we can delete details of employees on employee_details which are matched with department_details table. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id