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.

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

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

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

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

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

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 -

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 –

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 -

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 –

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 -

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 -