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

CustomerID	CustomerName
1	John
2	Sarah
3	Mike

Orders Table

OrderID	CustomerID	OrderAmount
101	1	100
102	2	200
103	4	150

Query

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

Result

CustomerName	OrderAmount
John	100
Sarah	200

• 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

CustomerName	OrderAmount
John	100
Sarah	200
Mike	NULL

• 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

CustomerName	OrderAmount
John	100
Sarah	200
NULL	150

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

---

Summary of Joins

Join Type	Behavior
INNER JOIN	Returns only rows with matching data in both tables.
LEFT JOIN	Returns all rows from the left table and matching rows from the right table. Non-matching rows = NULL.
RIGHT JOIN	Returns all rows from the right table and matching rows from the left table. Non-matching rows = NULL.

---

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.