How to Perform Inner Join in MySQL

· 567 words · 3 minute read

Inner Join in MySQL: A Comprehensive Tutorial 🔗

Are you struggling to understand how inner joins work in MySQL? This tutorial will guide you through the process of performing an inner join in MySQL, with clear explanations, examples, and exercises to help you practice.

What is an Inner Join? 🔗

An inner join is a type of SQL join that returns only the rows that have a match in both tables. It combines rows from two or more tables based on a related column between them.

Syntax and Explanation 🔗

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
  • The SELECT clause specifies the columns you want to retrieve.
  • The FROM clause specifies the first table to join, often referred to as the “left” table.
  • The INNER JOIN clause specifies the second table to join, often referred to as the “right” table.
  • The ON clause specifies the condition for the join, i.e., the column(s) that need to match between the two tables.

Example: Employees and Departments 🔗

employees table:

id name dept_id
1 John 1
2 Jane 2
3 Bob 1
4 Alice 3

departments table:

id name
1 Sales
2 Marketing
3 IT

To perform an inner join on the dept_id column in employees and the id column in departments, we would use the following query:

SELECT employees.id, employees.name, departments.name AS department
FROM employees
INNER JOIN departments
ON employees.dept_id = departments.id;

This would return the following result:

id name department
1 John Sales
2 Jane Marketing
3 Bob Sales
4 Alice IT

Exercises 🔗

Instructions: Write the SQL queries to answer the following questions. You can use the employees and departments tables provided above.

  1. Using the same employees and departments tables, write a query to retrieve the names of employees and their corresponding department names, but only for employees in the Sales department.
/* Your answer here */
  1. Suppose we have another table projects with the following columns: id, name, and employee_id. Write a query to retrieve the names of projects and the names of employees who are working on each project.
/* Your answer here */
  1. Write a query to retrieve the names of departments and the number of employees in each department.
/* Your answer here */

Solutions 🔗

SELECT employees.name, departments.name AS department
FROM employees
INNER JOIN departments
ON employees.dept_id = departments.id
WHERE departments.name = 'Sales';

SELECT projects.name, employees.name
FROM projects
INNER JOIN employees
ON projects.employee_id = employees.id;

SELECT departments.name, COUNT(employees.id) AS num_employees
FROM employees
INNER JOIN departments
ON employees.dept_id = departments.id
GROUP BY departments.name;

Bonus: Use Table Aliases with Inner Join Query 🔗

You can use aliases to shorten the table names and make the query more readable. In the query below, e is used as an alias for the employees table, and d is used as an alias for the departments table.

SELECT e.id, e.name, d.name AS department
FROM employees as e
INNER JOIN departments as d
ON e.dept_id = d.id;

Best Practices 🔗

  • Use indexes on the columns used in the join condition to improve performance.
  • Avoid using SELECT * and instead specify only the columns you need.
  • Use meaningful column names and aliases to make the query easier to read.

Conclusion 🔗

In this tutorial, we covered the basics of the MySQL INNER JOIN, including its syntax and examples to help you master this essential skill. Practice the exercises and experiment with different join scenarios to solidify your understanding of the MySQL INNER JOIN.

MySQL