MySQL: The Complete Guide to MySQL Joins
Last updated: 2026-08-26
JOIN is a core capability of relational databases—it only truly demonstrates its power when multiple tables are joined.
This lesson provides a systematic overview of all JOIN types and their practical applications.
1. What You'll Learn
- INNER JOIN (inner join)
- LEFT JOIN / RIGHT JOIN (outer join)
- CROSS JOIN
- Self-linking
- USING Syntax
2. A True Story About Multi-Table Queries
(1) Pain Point: Data is scattered across multiple tables
Order data is stored in the orders table, and customer names are stored in the customers table.
To find the "customer name associated with each order," you'll need to perform a join query.
(2) Solutions for JOINs
SQL
SELECT
o.id AS order_id,
c.name AS customer_name,
o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
3. Overview of JOIN Types
graph LR
A[JOIN Type] --> B[INNER JOIN]
A --> C[LEFT JOIN]
A --> D[RIGHT JOIN]
A --> E[CROSS JOIN]
B --> F[Return only matching rows]
C --> G[Full Refund on the Left Table+Right Table Match]
D --> H[Full refund as shown in the table on the right+Left Table Match]
E --> I[Cartesian product]
| JOIN Type | Description | Number of Rows Returned |
|---|---|---|
| INNER JOIN | Rows that match in both tables | Intersection |
| LEFT JOIN | Entire left table + matching rows from the right table | Entire left table |
| RIGHT JOIN | Entire right table + matching rows from the left table | Entire right table |
| CROSS JOIN | Cartesian product | m × n |
4. INNER JOIN
Return only the rows that match in both tables.
▶ Example: Basic Inner Join
Output:
TEXT
📖 Display only
---------+---------------+--------+--------------------
order_id | customer_name | amount | order_date
---------+---------------+--------+--------------------
1 | Alice | 25.00 | 2024-01-15 10:30:00
2 | Bob | 50.00 | 2024-01-15 10:30:00
3 | Charlie | 75.00 | 2024-01-15 10:30:00
---------+---------------+--------+--------------------
3 rows in set
---+---------+--------------+---------
id | name | product_name | quantity
---+---------+--------------+---------
1 | Alice | Alice | 10
2 | Bob | Bob | 15
3 | Charlie | Charlie | 20
---+---------+--------------+---------
3 rows in set
SQL
-- Check Order and Customer Information
SELECT
o.id AS order_id,
c.name AS customer_name,
o.amount,
o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
-- Connection of the Three Tables
SELECT
o.id,
c.name,
p.product_name,
oi.quantity
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;
Output:
TEXT
📖 Display only
Output displayed
5. LEFT JOIN (Left Outer Join)
Returns all rows from the left table; returns NULL if there are no matches in the right table.
▶ Example: Left Join
SQL
-- Query all customers and their orders（Including customers with no orders）
SELECT
c.name,
o.id AS order_id,
o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
Output:
TEXT
📖 Display only
+----------+----------+--------+
| name | order_id | amount |
+----------+----------+--------+
| Alice | 1 | 100.00 |
| Alice | 2 | 200.00 |
| Bob | 3 | 150.00 |
| Charlie | NULL | NULL | -- No orders
+----------+----------+--------+
▶ Example: Find customers with no orders
SQL
SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;
Output:
TEXT
📖 Display only
Output displayed
6. RIGHT JOIN (Right Outer Join)
Returns all rows from the right table; returns NULL if there are no matches in the left table.
▶ Example: Right Join
SQL
-- Query all orders and their customers（Including invalid orders）
SELECT
c.name,
o.id AS order_id,
o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;
Output:
TEXT
📖 Display only
Output displayed
💡 Tip: RIGHT JOIN is rarely used; it can usually be replaced with a LEFT JOIN (by swapping the order of the tables).
7. CROSS JOIN
Return the Cartesian product (row by row).
▶ Example: Cross-connection
SQL
-- Generate Colors×Size Combinations
SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;
Output (3 colors × 3 sizes = 9 rows):
TEXT
📖 Display only
+-------+------+
| color | size |
+-------+------+
| Red | S |
| Red | M |
| Red | L |
| Blue | S |
| Blue | M |
| Blue | L |
| Green | S |
| Green | M |
| Green | L |
+-------+------+
8. USING Syntax
When the join field names are the same, you can use the USING clause to simplify the query.
9. Self-linking
A table is associated with itself.
▶ Example: Finding an Employee and Their Supervisor
SQL
-- The employee roster includes manager_id A field pointing to itself id
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
Output:
TEXT
📖 Display only
+----------+---------+
| employee | manager |
+----------+---------+
| Alice | NULL | -- CEO，No superior
| Bob | Alice |
| Charlie | Alice |
| Dave | Bob |
+----------+---------+
10. Basics of JOIN Optimization
| Optimization Strategy | Description |
|---|---|
| Index the join field | The field in ON o.customer_id = c.id must be indexed |
| Small Table Drives Large Table | Place the table with the small result set on the left |
Avoid SELECT * |
Search only the required fields |
| WHERE filtering | Reduces the number of rows involved in the JOIN |
❓ FAQ
Q Is there a difference between INNER JOIN and JOIN?
A No.
JOIN is the default for INNER JOIN.Q Can LEFT JOIN and RIGHT JOIN be used interchangeably?
A Yes.
A LEFT JOIN B is equivalent to B RIGHT JOIN A. It is recommended to consistently use LEFT JOIN.Q Is a JOIN operation slow?
A It depends on the data volume and indexes. When the join fields are indexed, even JOIN operations on tables with millions of rows can return results in seconds.
Q How many tables can be joined?
A In theory, there is no limit in MySQL, but in practice, it is recommended not to exceed 5–6 tables. If there are too many tables, consider denormalizing the design.
📖 Summary
- INNER JOIN returns the matching rows from the two tables
- LEFT JOIN returns all rows from the left table plus the matching rows from the right table (commonly used)
- RIGHT JOIN returns all rows from the right table plus the matching rows from the left table
- CROSS JOIN returns the Cartesian product
- Self-join: A table is joined with itself
- USING simplifies joins involving fields with the same name
- Indexing join fields is a key optimization technique
📝 Exercises
-
Basic Question (Difficulty: ⭐): Use an INNER JOIN to retrieve orders and their corresponding customer names.
-
Advanced Problem (Difficulty: ⭐⭐): Use a LEFT JOIN to identify customers who have never placed an order.
-
Challenge Question (Difficulty: ⭐⭐⭐): Use a self-join to query employees and their supervisors, and count the number of subordinates managed by each supervisor.
