The INNER JOIN in MySQL is a type of join that retrieves rows from two or more tables based on a related column between them. It returns only the rows where there is a match in both joined tables. This is the most common type of join and is used to combine rows from multiple tables when there is a logical relationship between them.

Syntax

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

• columns: The columns you want to retrieve from the tables.
• table1 and table2: The tables to be joined.
• common_column: The column used to join the tables. This column should have matching values in both tables.

How INNER JOIN Works

• Matching Rows: The INNER JOIN operation matches rows from the first table with rows from the second table based on the condition specified in the ON clause.
• Non-Matching Rows: Rows from either table that do not meet the join condition are not included in the result set.

Example

Consider two tables: employees and departments.

• employees:

  employee_id	name	department_id
  1	John Smith	101
  2	Jane Doe	102
  3	Sam Brown	101

• departments:

  department_id	department_name
  101	Sales
  102	Marketing
  103	HR

Query: Get Employee Names with Department Names

To retrieve employee names along with their department names, you would use an INNER JOIN:

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

Result:

• Explanation:
  • The INNER JOIN matches rows from employees with rows from departments where employees.department_id equals departments.department_id.
  • Only employees with a corresponding department in the departments table are included in the result set.
  • Employees with department_id 101 and 102 are matched with their respective departments. Employees with department_id 103 (not present in the employees table) are excluded.

Using Multiple Joins

You can also use INNER JOIN with more than two tables. For example, if you have a projects table and want to find the project names along with the employee names and department names, you could extend the join:

• projects:

  project_id	project_name	department_id
  1	Project X	101
  2	Project Y	102

Query: Get Employee Names, Department Names, and Project Names

SELECT employees.name, departments.department_name, projects.project_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id
INNER JOIN projects ON departments.department_id = projects.department_id;

Result:

• Explanation:
  • The query joins employees with departments to get department names, and then joins departments with projects to get project names.
  • The result set includes only those employees and projects where there are matches across all three tables.

Performance Considerations

• Indexes: Indexes on the columns used in the join condition can improve performance.
• Data Volume: Large tables may lead to performance issues. Optimizing queries and using appropriate indexes can help mitigate these issues.