Summary -

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

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.

Syntax for getting rows by using joins –

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;     

Syntax for creating view by using joins –

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;     

Syntax for deleting rows by using joins –

DELETE table|view FROM table1 INNER JOIN|LEFT JOIN|RIGHT JOIN|
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 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 ]; 

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

department_details -

dept_id dept_name dept_location
1000 Office Location1
2000 Sales Location2

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 –

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

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 –

employee_info -

emp_id emp_name dept_name

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

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 –

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
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

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 –

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 20000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 20000.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 20000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 20000.00 2000