MySQL INNER JOIN clause

MySQL INNER JOIN clause is a powerful tool that enables us to combine data from two or more tables based on a matching column between them. It is one of the most commonly used SQL clauses and is essential for many database applications. In this article, we will discuss what an INNER JOIN is and how to use it in MySQL.

What is INNER JOIN?

The INNER JOIN clause is used to retrieve data from two or more tables in MySQL that have a common column. It matches each row from one table with every matching row from another table and returns the result. The INNER JOIN clause works by comparing each row from the first table with every row from the second table and returning only those rows where there is a match.

Syntax

The syntax for the INNER JOIN clause is as follows:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

The INNER JOIN clause uses the ON keyword to specify the matching column(s) between the two tables. The column names must be specified in the format table_name.column_name to avoid ambiguity.

Example

Let’s consider two tables, customers and orders. The customers table contains information about customers, and the orders table contains information about their orders. Both tables have a common column, customer_id, which can be used to join the tables.

Here is an example of how to use INNER JOIN to retrieve information about customers and their orders:

SELECT customers.customer_name, orders.order_number, orders.order_date
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

This statement retrieves the customer name, order number, and order date for all customers who have placed an order. The INNER JOIN clause joins the two tables based on the customer_id column, which is common to both tables.

We specify the columns we want to retrieve in the SELECT statement, followed by the two tables we want to join, customers and orders, separated by the INNER JOIN keyword. We then use the ON keyword to specify the matching column between the two tables.

Inner Join Multiple Tables

We can also use INNER JOIN to join multiple tables in MySQL. Let’s consider three tables, customers, orders, and order_details. The customers table contains information about customers, the orders table contains information about their orders, and the order_details table contains information about the products ordered.

Here is an example of how to use INNER JOIN to retrieve information about customers, their orders, and the products they ordered:

SELECT customers.customer_name, orders.order_number, order_details.product_name
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN order_details ON orders.order_id = order_details.order_id;

This statement retrieves the customer name, order number, and product name for all customers who have placed an order and the products they ordered. The INNER JOIN clause joins the three tables based on the customer_id and order_id columns, which are common to all three tables.

We specify the columns we want to retrieve in the SELECT statement, followed by the three tables we want to join, customers, orders, and order_details, separated by two INNER JOIN keywords. We then use the ON keyword to specify the matching columns between the tables.

Conclusion

In conclusion, the INNER JOIN clause is a powerful tool in MySQL that allows us to combine data from two or more tables based on a common column. It is a fundamental concept in SQL and is essential for many database applications. By mastering the INNER JOIN clause, you can easily retrieve and analyze complex data from your MySQL database.

MYSQL INNER Joins work on two table where INNER JOIN clause matches rows in one table with rows in second tables and return result contain columns from both tables.
The inner join operation is characterized by the detail that its result contains only rows for which the join condition is satisfied

SYNTAX

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

Table structure Used for Example explanation

Tables

tablea
name varchar(30) NO NULL
n1 int(11) NO NULL
n2 int(11) NO NULL
color varchar(30) NO NULL
State varchar(30) NO NULL

tableb
s.name varchar(30) NO NULL
n3 int(11) NO NULL
n4 int(11) NO NULL

Example for MySQL INNER JOIN clause

SELECT name ,n1,n2,n4,n4 from tablea inner join tableb on name = `s.name`;

Output of Above Example

+——+—-+—-+—-+—-+
| name | n1 | n2 | n4 | n4 |
+——+—-+—-+—-+—-+
| Tom | 70 | 60 | 70 | 70 |
+——+—-+—-+—-+—-+
1 row in set (0.00 sec)

Blog

mysqlinnerjoin

MySQL INNER JOIN clause

SYNTAX