MYSQL Inner Join

The INNER JOIN is used to return all the records that matches in both the tables.

Consider the employees table

empno name age department_id location salary emp_type_id
1 Andrew 30 4 India 100000 2
2 Beslin 29 3 India 90000 1
3 Joanna 23 2 USA 500000 1
4 Tianna 22 4 Canada 500000 1

Departments table

department_id department_name
1 HR
2 Software Developer
3 Tester
4 Architect

EmployeeType table

emp_type_id emp_type
1 Full-Time
2 Contract

Syntax for INNER JOIN

SELECT * FROM table_name_1
INNER JOIN table_name_2
ON table_name_1.column_name = table_name_2.column_name;

Example

select * from employees 
inner join department
on employees.department_id = department.department_id;

Output

name location department_name
Andrew India Architect
Beslin India Tester
Joanna USA Software Developer
Tianna Canada Architect

It is also possible to join multiple tables with inner join. Now we shall combine all the 3 table listed above
Example

select employees.name,employees.location,department.department_name,employeetype.emp_type from employees 
inner join department
on employees.department_id = department.id
inner join employeetype
on employees.emp_type_id= employeetype.emp_type_id;

Output

name location department_name emp_type
Andrew India Architect Contract
Beslin India Tester Full-Time
Joanna USA Software Developer Full-Time
Tianna Canada Architect Full-Time

Most Read