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.
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)