MySql - INNER JOIN

MySQL INNER JOIN - Joining Related Data From Two or More Tables

Introduction

In relational databases like MySQL, data is often normalized and stored across multiple tables to avoid redundancy and improve data integrity. When querying this distributed data, it is often necessary to combine rows from two or more tables based on a related column between them. This operation is commonly known as a JOIN.

MySQL provides several types of joins, with INNER JOIN being one of the most commonly used. This guide provides an in-depth explanation of the INNER JOIN operation in MySQL, complete with syntax, use cases, examples, and best practices.

What is INNER JOIN?

The INNER JOIN keyword in MySQL is used to select records that have matching values in both tables involved in the join. It returns only the rows where there is a match between the specified columns of the participating tables.

Why Use INNER JOIN?

• To combine data from multiple tables.
• To retrieve meaningful relationships across tables.
• To enhance the richness of the query output by merging relevant information.
• To enforce logical connections between datasets.

INNER JOIN Syntax

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

Explanation

• SELECT columns: Specifies the columns to be retrieved.
• FROM table1: The first table in the join.
• INNER JOIN table2: The second table to be joined with the first table.
• ON table1.common_column = table2.common_column: The condition that defines how the tables are related.

Example Tables

Let’s consider two tables:

Customers Table

+----+----------+-----------+
| ID | Name     | City      |
+----+----------+-----------+
| 1  | John     | New York  |
| 2  | Jane     | London    |
| 3  | Michael  | Paris     |
| 4  | Sophia   | Berlin    |
+----+----------+-----------+

Orders Table

+----+------------+----------+
| ID | CustomerID | Product  |
+----+------------+----------+
| 1  | 1          | Laptop   |
| 2  | 3          | Phone    |
| 3  | 4          | Tablet   |
| 4  | 2          | Monitor  |
| 5  | 2          | Mouse    |
+----+------------+----------+

Basic INNER JOIN Example

SELECT Customers.Name, Orders.Product
FROM Customers
INNER JOIN Orders
ON Customers.ID = Orders.CustomerID;

Output

+----------+----------+
| Name     | Product  |
+----------+----------+
| John     | Laptop   |
| Jane     | Monitor  |
| Jane     | Mouse    |
| Michael  | Phone    |
| Sophia   | Tablet   |
+----------+----------+

This output shows only the customers who have placed orders, excluding customers who do not have corresponding records in the Orders table.

INNER JOIN with Multiple Conditions

Sometimes, you may need to join tables using multiple conditions.

SELECT Customers.Name, Customers.City, Orders.Product
FROM Customers
INNER JOIN Orders
ON Customers.ID = Orders.CustomerID
AND Customers.City = 'London';

Output

+------+--------+---------+
| Name | City   | Product |
+------+--------+---------+
| Jane | London | Monitor |
| Jane | London | Mouse   |
+------+--------+---------+

Using Table Aliases in INNER JOIN

Aliases make queries shorter and easier to read, especially with multiple joins.

SELECT C.Name, O.Product
FROM Customers AS C
INNER JOIN Orders AS O
ON C.ID = O.CustomerID;

INNER JOIN with WHERE Clause

We can further filter INNER JOIN results using the WHERE clause.

SELECT C.Name, O.Product
FROM Customers AS C
INNER JOIN Orders AS O
ON C.ID = O.CustomerID
WHERE O.Product = 'Laptop';

Output

+------+---------+
| Name | Product |
+------+---------+
| John | Laptop  |
+------+---------+

Joining More Than Two Tables

MySQL allows joining multiple tables with INNER JOIN. Let's introduce another table:

Products Table

+----+------------+---------+
| ID | Product    | Price   |
+----+------------+---------+
| 1  | Laptop     | 1000    |
| 2  | Phone      | 500     |
| 3  | Tablet     | 300     |
| 4  | Monitor    | 200     |
| 5  | Mouse      | 50      |
+----+------------+---------+

Query to Join Three Tables

SELECT C.Name, O.Product, P.Price
FROM Customers C
INNER JOIN Orders O ON C.ID = O.CustomerID
INNER JOIN Products P ON O.Product = P.Product;

Output

+----------+----------+-------+
| Name     | Product  | Price |
+----------+----------+-------+
| John     | Laptop   | 1000  |
| Jane     | Monitor  | 200   |
| Jane     | Mouse    | 50    |
| Michael  | Phone    | 500   |
| Sophia   | Tablet   | 300   |
+----------+----------+-------+