Inner Join in MySql
INNER JOIN in MySQL
The INNER JOIN in MySQL is used to combine records from two or more tables based on a matching condition. It returns only the rows where there is a match in both tables.
1. Syntax
SELECT table1.column1, table2.column2, ...FROM table1INNER JOIN table2 ON table1.common_column = table2.common_column;
✅ It fetches only records that have matching values in both tables.
2. Example: Using INNER JOIN
Tables: employees & departments
employees Table
| id | name | department_id |
|---|---|---|
| 1 | Alice | 1 |
| 2 | Bob | 2 |
| 3 | Charlie | 3 |
| 4 | David | 4 |
departments Table
| id | department_name |
|---|---|
| 1 | IT |
| 2 | HR |
| 3 | Sales |
Query: Get employee names along with their department names
SELECT employees.name, departments.department_nameFROM employeesINNER JOIN departments ON employees.department_id = departments.id;
✅ Output:
+---------+---------------+| name | department_name |+---------+---------------+| Alice | IT || Bob | HR || Charlie | Sales |+---------+---------------+
🚀 Explanation:
- Matches
department_idinemployeeswithidindepartments. - Employees without a matching department (like "David" with
department_id = 4) are not included.
3. INNER JOIN with Multiple Tables
You can join more than two tables using INNER JOIN.
SELECT employees.name, departments.department_name, salaries.salaryFROM employeesINNER JOIN departments ON employees.department_id = departments.idINNER JOIN salaries ON employees.id = salaries.employee_id;
✅ Joins employees, departments, and salaries tables.
4. INNER JOIN with WHERE Clause
You can filter records after the join.
SELECT employees.name, departments.department_nameFROM employeesINNER JOIN departments ON employees.department_id = departments.idWHERE departments.department_name = 'IT';
✅ Gets only employees in the IT department.
5. INNER JOIN vs. Other Joins
| Join Type | Returns |
|---|---|
| INNER JOIN | Only matching records from both tables. |
| LEFT JOIN | All records from the left table + matching ones from the right. |
| RIGHT JOIN | All records from the right table + matching ones from the left. |
| FULL JOIN | All records from both tables (not supported in MySQL directly). |
6. Performance Tips
⚡ Use Indexed Columns – Index the columns used in JOIN (department_id, id).
⚡ Avoid Joining Large Unfiltered Tables – Use WHERE to filter unnecessary data.
⚡ Use EXPLAIN for Query Optimization – Run EXPLAIN SELECT ... to analyze performance.
7. Key Takeaways
✅ INNER JOIN returns only matching rows from both tables.
✅ Use it when you need data that exists in both tables.
✅ Can be combined with WHERE, GROUP BY, and ORDER BY for advanced queries.
✅ Use indexes for better performance.
