MySQL INNER JOIN

MySQL INNER JOIN

The INNER JOIN clause in MySQL is used to combine 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 tables.

Syntax

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

• ON specifies the condition for joining the tables.
• If no matching rows exist, those rows are excluded from the result.

Example Tables

Table: employees

+----+--------+---------------+
| id | name   | department_id |
+----+--------+---------------+
| 1  | Alice  | 1             |
| 2  | Bob    | 2             |
| 3  | Carol  | 3             |
| 4  | Dave   | NULL          |
+----+--------+---------------+

Table: departments

+----+-----------+
| id | name      |
+----+-----------+
| 1  | HR        |
| 2  | IT        |
| 3  | Finance   |
| 4  | Marketing |
+----+-----------+

1. Basic INNER JOIN

Retrieve a list of employees along with their department names:

SELECT e.name AS employee_name, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Result

+----------------+------------------+
| employee_name  | department_name  |
+----------------+------------------+
| Alice          | HR               |
| Bob            | IT               |
| Carol          | Finance          |
+----------------+------------------+

• Explanation: Only rows with matching department_id in employees and id in departments are included.

2. INNER JOIN with Additional Conditions

Get employees from the "IT" department:

SELECT e.name AS employee_name, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE d.name = 'IT';

Result

+----------------+------------------+
| employee_name  | department_name  |
+----------------+------------------+
| Bob            | IT               |
+----------------+------------------+

3. INNER JOIN Across Multiple Tables

Consider another table, projects:

Table: projects

+----+------------+-------------+
| id | name       | employee_id |
+----+------------+-------------+
| 1  | Project A  | 1           |
| 2  | Project B  | 2           |
| 3  | Project C  | 3           |
+----+------------+-------------+

Retrieve employees, their department names, and the projects they are working on:

SELECT e.name AS employee_name, d.name AS department_name, p.name AS project_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN projects p ON e.id = p.employee_id;

Result

+----------------+------------------+----------------+
| employee_name  | department_name  | project_name   |
+----------------+------------------+----------------+
| Alice          | HR               | Project A      |
| Bob            | IT               | Project B      |
| Carol          | Finance          | Project C      |
+----------------+------------------+----------------+

4. INNER JOIN with Aggregate Functions

Count the number of employees in each department:

SELECT d.name AS department_name, COUNT(e.id) AS employee_count
FROM departments d
INNER JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

Result

+------------------+----------------+
| department_name  | employee_count|
+------------------+----------------+
| HR               | 1             |
| IT               | 1             |
| Finance          | 1             |
+------------------+----------------+

5. INNER JOIN with Aliases

For better readability, table aliases are often used.

Example

SELECT e.name AS employee_name, d.name AS department_name
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id;

This produces the same result as the basic example but improves query clarity.

6. Combining INNER JOIN with ON and USING

Using ON

SELECT e.name, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Using USING

If the columns have the same name, you can use USING:

SELECT e.name, d.name AS department_name
FROM employees e
INNER JOIN departments d USING (department_id);

7. INNER JOIN vs. Other Joins

• INNER JOIN: Returns rows with matching data in both tables.
• LEFT JOIN: Includes all rows from the left table, even if there's no match in the right table.
• RIGHT JOIN: Includes all rows from the right table, even if there's no match in the left table.

Best Practices for INNER JOIN

1. Use Indexing:

   • Index the columns used in the ON condition for faster query performance.

2. Use Aliases:

   • Use shorter aliases (e.g., e, d) for tables in complex queries.

3. Avoid Unnecessary Columns:

   • Select only the columns you need to improve performance.

Let me know if you'd like further assistance or additional examples!