MySQL INNER JOIN

In MySQL, an INNER JOIN is a type of join operation that combines rows from two or more tables based on a related column between them. The result set includes only the rows that have matching values in the specified columns.

Syntax

The basic syntax of an INNER JOIN is as follows:

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

Here’s a breakdown of the components:

SELECT columns: Specify the columns you want to retrieve from the tables.
FROM table1: Specify the first table.
INNER JOIN table2: Declare that you’re performing an INNER JOIN with the second table.
ON table1.column_name = table2.column_name: Specify the condition for the join, indicating the columns from each table that should match for the join to occur.

Example

Now, let’s consider a practical example. Suppose you have two tables, “employees” and “departments,” and you want to retrieve information about employees along with their corresponding department details:

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

In this example:

The employees table contains information about each employee, including their ID (employee_id), name (employee_name), and the ID of the department they belong to (department_id).
The departments table contains information about each department, including the department ID (department_id) and the department name (department_name).
The INNER JOIN is performed on the department_id column, linking the two tables based on this common attribute. The result set will include only those rows where there is a match in the department_id column between the “employees” and “departments” tables.

INNER JOINs are useful when you want to retrieve data that exists in both tables, and they play a crucial role in querying and analyzing data across related tables in a relational database system like MySQL.