MySQL Joins — Introduction

In a relational database, data is intentionally spread across multiple tables. A customers table holds customer information. An orders table holds order information. They are separate — but related. A JOIN is the SQL mechanism that brings related rows from two or more tables together into a single result set, based on a matching column.

Without joins, you would have to run multiple separate queries and combine the results manually in your application code. With joins, MySQL does the work for you in one query.

---

Why Joins Exist

Good database design avoids storing the same information in multiple places. Instead of repeating a customer's name and address inside every order row, you store the customer once in a customers table and reference them by ID in the orders table. When you need a report that shows both the order details and the customer name, a JOIN reunites that data.

---

The Sample Tables

All JOIN lessons in this section use the following two tables:

customers

id	name	city
1	Rahim Uddin	Dhaka
2	Sara Begum	Chittagong
3	Karim Ali	Sylhet
4	Nila Akter	Dhaka

orders

id	customer_id	amount	order_date
1	1	2500.00	2025-06-01
2	1	1200.00	2025-06-05
3	3	4800.00	2025-06-08
4	6	900.00	2025-06-10

Notice that:

• Customer id=1 (Rahim) has two orders.
• Customer id=3 (Karim) has one order.
• Customers id=2 and id=4 have no orders.
• Order id=4 references customer_id=6, which does not exist in the customers table.

These gaps and mismatches are exactly what makes the different join types behave differently.

---

The JOIN Syntax

The general pattern for any join is:

SELECT columns
FROM table_a
[JOIN TYPE] JOIN table_b ON table_a.column = table_b.column;

The ON clause defines the relationship — which column in each table should be matched. Usually this is a primary key from one table and a foreign key from the other.

---

Types of Joins

---

Using Table Aliases in Joins

When joining tables, always use short aliases — they make your query much easier to write and read:

-- Without aliases: verbose
SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

-- With aliases: clean
SELECT c.name, o.amount
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id;

---

Key Points to Remember

• A JOIN combines columns from two or more tables into a single result set based on a matching condition.
• The ON clause specifies which columns to match — typically a primary key and a foreign key.
• The four main join types are INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN.
• Always use table aliases when writing joins — it keeps the query concise.
• The type of join you choose determines which rows appear when there is no match on one side.

---

Common Mistakes to Avoid

---

Try It: Spotting an Unwanted Cartesian Product

A second example — comparing a correct join against the mistake of a missing ON clause, using the sample tables from this page:

-- Correct: matched pairs only
SELECT c.name, o.amount
FROM customers AS c
JOIN orders AS o ON c.id = o.customer_id;

-- Mistake: no ON clause -- returns every customer paired with every order
SELECT c.name, o.amount
FROM customers AS c, orders AS o;

---

Frequently Asked Questions