MySQL - Inner Join Queries

Inner join is one of the most commonly used types of joins in SQL. An inner join returns only the rows that have matching values in both tables being joined.

To perform an inner join in MySQL, you need to specify the tables to join and the columns to use for the join condition using the "ON" clause.

Let's say you have two tables in your database: "employees" and "departments". The "employees" table contains information about employees, including their name, department ID, and salary. The "departments" table contains information about the various departments in your organization, including their name and ID.

You want to create a report that lists all employees and their corresponding department names. To do this, you need to use an inner join to combine data from both tables based on the department ID column, which is common to both tables.

Here's the SQL code for the inner join:

SELECT employees.name, departments.department_name

FROM employees

INNER JOIN departments

ON employees.department_id = departments.department_id;

In this example, we're selecting the "name" column from the "employees" table and the "department_name" column from the "departments" table. We're using the "ON" clause to specify the join condition, which is that the "department_id" column in both tables must match.

The result of this query will be a table that lists each employee's name and their corresponding department name. If there are any employees who do not have a corresponding department in the "departments" table, they will not appear in the result set.

Another example of an inner join in MySQL.

Let's say you have two tables in your database: "products" and "orders". The "products" table contains information about each product, including the product ID and the product name. The "orders" table contains information about each order, including the order ID, the product ID, and the quantity of each product ordered.

You want to create a report that shows the total number of each product that has been ordered. To do this, you need to use an inner join to combine data from both tables based on the product ID column.

Here's the SQL code for the inner join:

SELECT products.product_name, SUM(orders.quantity) as total_ordered

FROM products

INNER JOIN orders

ON products.product_id = orders.product_id

GROUP BY products.product_name;

In this example, we're selecting the "product_name" column from the "products" table and using the SUM function to calculate the total quantity of each product that has been ordered from the "orders" table. We're grouping the results by product name using the GROUP BY clause.

The result of this query will be a table that lists each product name and the total quantity ordered. If there are any products that have not been ordered, they will not appear in the result set.