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 |