MySQL joins are used to retrieve data from multiple related tables. Each type of join determines how the rows from these tables are combined. The most commonly used joins are:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN

1. INNER JOIN

• Definition: Combines rows from two tables where there is a matching value in both tables.
• Excludes non-matching rows.

Syntax

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

Example

Suppose we have the following tables:

Customers Table

Orders Table

Query

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result

• Explanation: Only rows where CustomerID exists in both tables are included.

2. LEFT JOIN (LEFT OUTER JOIN)

• Definition: Returns all rows from the left table and matching rows from the right table. Non-matching rows from the right table are filled with NULL.

Syntax

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

Example

Using the same tables:

Query

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result

• Explanation: All rows from Customers are included. For Mike, there is no matching Order, so OrderAmount is NULL.

3. RIGHT JOIN (RIGHT OUTER JOIN)

• Definition: Returns all rows from the right table and matching rows from the left table. Non-matching rows from the left table are filled with NULL.

Syntax

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

Example

Using the same tables:

Query

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result

• Explanation: All rows from Orders are included. For OrderID = 103, there is no matching Customer, so CustomerName is NULL.

Summary of Joins

When to Use?

• INNER JOIN: Use when you need data that exists in both tables.
• LEFT JOIN: Use when you want all data from the left table, even if there’s no match in the right table.
• RIGHT JOIN: Use when you want all data from the right table, even if there’s no match in the left table.