Summary -

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

LEFT OUTER JOIN is used to combine two tables rows that it gets all rows from table which is left side of the JOIN and matched rows from table which is right side of the JOIN. LEFT OUTER JOIN also known as LEFT JOIN. While using LEFT JOIN right side table rows which does not have matched values with left 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 LEFT 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 LEFT JOIN
table2 ON table1.column_name = table2.column_name;     

Syntax for deleting table | view by using joins –

DELETE table | view FROM table1  LEFTJOIN 
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 LEFT 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 – Creating view by using LEFT JOIN.

Requirement – Creating employee_info view by using LEFT 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
LEFT 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
LEFT OUTER JOIN department_details ON
employee_details.dept_id = department_details.dept_id; 

By executing above query we can get all rows with specific columns from employee_details table and matched rows from department_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
010 Employee10
011 Employee11

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

Requirement – Creating employee_info view by using LEFT 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
LEFT JOIN department ON
employee_details.dept_id = department.dept_id; 

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

emp_id emp_name dept_name
001 Employee1
002 Employee2
003 Employee3
004 Employee4
005 Employee5
006 Employee6
007 Employee7
008 Employee8
009 Employee9
010 Employee10
011 Employee11

Scenario – Deleting table by using LEFT JOIN.

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

DELETE employee_details FROM employee_details
LEFT 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 by using LEFT JOIN. The output was as follows –

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id

Scenario – Updating table by using LEFT JOIN.

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

UPDATE employee_details SET date_of_hire ='2019-09-09'FROM 
employee_details 
LEFT 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 table by using LEFT 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-09-09 27000.00 4000
011 Employee11 Salesman Employee5 2019-09-09 10000.00 4000

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

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

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

By executing above query we can get all details from employee_details table and matched rows 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
010 Employee10 Analyst 27000.00
011 Employee11 Salesman 10000.00