Summary -

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

INNER JOIN statement is used to get the matching rows which have matched values from both tables. By using INNER JOIN statement we can CREATE view, DELETE, UPDATE, and INSERT rows on either table or view. It is also known as EQUI JOIN.

Syntax for getting rows by using joins – 

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

Syntax for deleting table | view by using joins – 

DELETE table | view FROM table1  INNER 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 INNER 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
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 INNER JOIN.

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

SELECT emp_id,emp_name,designation,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 designation dept_name
001 Employee1 Director Office
003 Employee3 Manager Office
005 Employee5 Analyst Office
006 Employee6 Analyst Office
007 Employee7 Clerk Office
002 Employee2 Director Sales
004 Employee4 Manager Sales
008 Employee8 Salesman Sales
009 Employee9 Salesman Sales

Scenario – Creating view by using INNER JOIN.

Requirement – Creating view employee_info with emp_id, emp_name, dept_name details from employee_details and department_details tables by using INNER 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 INNER 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 which have matched values 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

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 INNER JOIN department_details 
ON employee_details.dept_id = department_details.dept_id;

By executing above query we can update the salaries of employee to 47000 on employee_details table 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
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 27000.00 4000
011 Employee11 Salesman Employee5 2019-09-09 10000.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 INNER 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
010 Employee10 Analyst Employee5 2019-07-11 27000.00 4000
011 Employee11 Salesman Employee5 2019-09-09 10000.00 4000