JOIN is used to combine rows from more than one table by using a common column name in tables. By using JOIN we can get required rows, CREATE view, DELETE, UPDATE, INSERT rows either in tables or views. There are various types of JOINs to combine rows from two tables are explained as follows –

• INNER JOIN – By using this statement we can get rows which are having matched values in the both tables.
• LEFT JOIN - By using this statement we can get whole rows from left side of the join table and matched rows in the right side of the table.
• RIGHT JOIN - By using this statement we can get whole rows from right side of the join table and matched rows in the left side of the table.
• FULL OUTER JOIN - By using this statement we can get rows which are present in the both tables.
• CROSS JOIN – By using this statement we can combine every single of one table with every single row of another table.

SELECT table1.column1,table1.column2, …,table2.column1,table2.column2,
 … FROM table1 INNER JOIN|
    LEFT JOIN | RIGHT JOIN| FULL OUTER JOIN table2 ON
    table1.column_name = table2.column_name;     

CREATE VIEW view_name AS table1.column1, table1.column2, …,
 table2.column1, table2.column2, …
   FROM table1 INNER JOIN|
   LEFT JOIN | RIGHT JOIN| FULL OUTER JOIN table2 ON
   table1.column_name = table2.column_name;     

DELETE table|view FROM table1 INNER JOIN|LEFT JOIN|RIGHT JOIN|
FULL OUTER JOIN 
 table2 ON table1.column_name = table2.column_name  [WHERE condition ]; 

UPDATE table|view SET table1.column1=value11, …,table2.column1=value21,
 …  FROM table1 INNER JOIN |
    LEFT JOIN | RIGHT JOIN|  FULL OUTER JOIN table2
    ON table1.column_name = table2.column_name [WHERE condition ]; 

consider employee_details and department_details tables are as follows -

Scenario – Getting rows from two tables by using JOIN.

Requirement – Getting rows from employee_details and department_details by using JOIN. The query was as follows -

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

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

Scenario – Creating view by using JOIN.

Requirement – Creating employee_info view by using JOIN. The query was as follows –

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

By executing above query we can CREATE employee_info view with matching rows from two tables. The output was as follows –

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

Requirement - Deleting details of employee whose dept_id is 2000 from employee_details which are matched in department_details table by using JOIN. The query was as follows -

DELETE employee_details FROM employee_details
INNER JOIN department_details ON 
employee_details.dept_id = department_details. dept_id
WHERE employee_details.dept_id = 2000; 

By executing above query we can DELETE details of employees whose dept_id is 2000 which are matched with the department_details table. the output was as follows –

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

Requirement - Updating salaries of employees whose dept_id is 2000 on employee_details table which are matched with department_details table by using JOIN. The query was as follows –

UPDATE employee_details SET salary=20000 FROM employee_details
INNER JOIN 
department_details ON employee_details.dept_id=department_details.dept_id
WHERE employee_details.dept_id = 2000; 

By executing above query we can update the salary of employees with 20000 whose dept_id is 2000 on employee_details which are matched with the department_details table by using JOIN. The output was as follows –