How MySQL Joins Work: Matching Rows Across Tables With Keys

by Souy Soeng /November 09, 2026

Diagram of a customers table and an orders table joined on customer id, with INNER, LEFT, RIGHT JOIN and UNION labels

MySQL joins combine rows from two tables into one result by matching a column the tables share, usually a key. If your customers live in one table and their orders in another, a join is how you ask a single question across both.

This guide follows one small shop database from start to finish. You'll see how INNER JOIN, LEFT JOIN and RIGHT JOIN decide which rows survive, why MySQL needs UNION to build a full outer join, and which quiet mistakes return wrong or slow results.

In short

A join pairs rows from two tables wherever a related column, usually a primary key and foreign key, matches.
INNER JOIN keeps only matching rows; LEFT JOIN keeps every row from the first table and fills gaps with NULL.
MySQL has no FULL OUTER JOIN, so you combine a LEFT JOIN and a RIGHT JOIN with UNION.
Put filters on the right-hand table inside ON for outer joins, never forget the ON clause, and index join columns.

What is a join in MySQL?

Picture an online shop. Orders sit in one table and customers in another. Open the orders table and you find a customer_id, which is just a number. Open the customers table and you find names and emails, but no orders. Each table holds exactly half of the answer to a simple question like "which customer placed this order?"

One tempting fix is to fetch the orders first, then look up each customer one at a time. That works on a handful of rows, but it means many round trips to the database and slow pages. What you really want is one query that reads both tables together.

That is what a join does. It compares a column in one table with a column in another, here customers.id and orders.customer_id, and wherever the values are equal it places the two rows side by side in one result. Think of matching name tags at an event: every order carries a tag, and the join finds the customer wearing the same one.

Related column: both tables store the same kind of value, such as a customer's id.
Usually a key: a primary key on one side, a foreign key on the other.
A join is only as good as the column you match on.

Why is data split into separate tables?

If joins add work, why not keep everything in one big table? Because that table would repeat the customer's name on every order. Changing one email would mean editing many rows, and sooner or later those copies drift out of sync.

The alternative is normalization: giving each fact exactly one home and linking those homes with keys. Each customer is stored once, orders simply point to customer_id, and a single update keeps everything consistent. Split data is not a design flaw; it is a deliberate choice.

The link is a foreign key. orders.customer_id refers to customers.id, and MySQL can enforce that every order points to a customer that really exists. The trade-off is easy to remember: you split to store and join to read. Writes stay clean, and joins are the price of putting the picture back together.

One big table: repeated names, many-row updates, copies that drift.
Normalized tables: each customer once, orders point to customer_id, one update stays consistent.

How does INNER JOIN work?

INNER JOIN is the most common join. It returns only rows that have matching values in both tables, and everything else is left out. Take three customers, Ana (id 1), Ben (id 2) and Chen (id 3), and three orders: o1 and o2 belong to customer 1, and o3 belongs to customer 3.

Run an INNER JOIN and the result contains Ana · o1, Ana · o2 and Chen · o3. Ana appears twice because she has two orders. Ben is simply gone, because no order points to id 2. In SQL you select the columns you want from customers (aliased c), INNER JOIN orders (aliased o), and the ON line states exactly which values must be equal.

The rule to remember is "matches only". Unmatched rows don't raise an error; they quietly disappear. That is perfect when you only care about customers who bought something, and a hidden bug when you expected everyone to be listed.

SELECT c.name, o.id
FROM customers c
INNER JOIN orders o
  ON o.customer_id = c.id;

How does LEFT JOIN keep rows without a match?

What if Ben needs to appear in the report anyway? LEFT JOIN keeps every row from the left table, even when nothing on the right matches. The left table is the one written first, right after FROM; that side is guaranteed to stay.

Follow Ben through it. MySQL reads his row, id 2, and looks for orders where customer_id equals 2. It finds zero rows. An inner join would drop him here, but LEFT JOIN instead returns Ben with NULL in every order column. Customers with orders still get one row per order; customers without orders get a single row padded with NULLs.

This unlocks a classic pattern: LEFT JOIN, then WHERE o.id IS NULL. Because only unmatched customers have a NULL order id, that query returns exactly the customers who never ordered. INNER JOIN cannot answer that question at all, since it throws those customers away before you can look for them.

Every left-table row appears at least once.
Missing matches become NULL, not missing rows.
LEFT JOIN plus WHERE o.id IS NULL finds the gaps.

RIGHT JOIN and FULL OUTER JOIN in MySQL

RIGHT JOIN is the mirror image of LEFT JOIN: it keeps every row from the right table instead. In practice, most people just swap the table order and write a LEFT JOIN, because it reads more naturally from the table you care about.

A full outer join keeps every row from both sides, matched where possible and NULL where not. Other databases have FULL OUTER JOIN built in, but MySQL does not, so a query copied from elsewhere will fail here with a syntax error. Instead, you build it yourself.

The first SELECT is a LEFT JOIN that keeps every customer. UNION stacks a second result underneath: a RIGHT JOIN that keeps every order, even one with no valid customer. Because UNION removes duplicates, rows matched by both halves appear only once. Two partial joins become one full one.

SELECT c.name, o.id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.id FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

Where are joins used in real applications?

The join you'll write most often looks like an order history page: every order alongside the customer who placed it. You ask for every order with its customer, MySQL reads each order row, finds the customer by id, and returns o1 · Ana, o2 · Ana, o3 · Chen with names already attached. One round trip replaces a separate lookup per order, and the matching happens close to the data.

Once you recognise that shape, you'll see it everywhere. An order list shows buyer names, a blog feed shows each post's author, and a product page shows the category each item belongs to. Almost any screen that displays a thing next to its owner or group is a join underneath.

All three are many-to-one relationships: one customer has many orders, one author many posts, one category many products. The recipe never changes. The child's foreign key equals the parent's id. Learn it once and you can read most real-world queries at a glance.

Order · customer
Post · author
Item · category

Common MySQL join mistakes

A broken join rarely throws an error. It returns the wrong rows, or the right rows very slowly. Three mistakes cause most of the trouble, and each looks harmless in the query.

The subtlest is filtering an outer join in WHERE. Suppose you LEFT JOIN orders and then filter on an order column such as status. Customers without orders have NULL status, so WHERE throws them out, and your LEFT JOIN quietly behaves like an INNER JOIN. Moving that condition into the ON clause keeps every customer while still filtering which orders attach.

The other two are easier to spot once you know them. Leave out the ON condition and MySQL treats the join as a cross join, pairing every row with every row, so results explode in size. Join on unindexed columns and MySQL scans the whole table for every match: fine on small data, painfully slow as tables grow.

Missing ON: every row pairs with every row.
WHERE on an outer join: removes the NULL rows you meant to keep.
Unindexed keys: each lookup scans the whole table.

# Wrong: WHERE turns LEFT into INNER
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';
# Right: filter inside ON
LEFT JOIN orders o ON o.customer_id = c.id
  AND o.status = 'paid';

How to choose the right join, then index it

Choosing a join starts with one question: which rows must survive? If you only want matches, use INNER JOIN. If you need every row from the first table, such as every customer even without orders, use LEFT JOIN. If you need both sides completely, combine LEFT and RIGHT with UNION.

Then index the columns in your ON clause. Creating an index on orders.customer_id takes one line. The primary key on customers.id is already indexed, and InnoDB adds an index automatically for declared foreign keys, so check what exists before adding duplicates.

Finally, check with EXPLAIN before trusting a slow query. It shows how MySQL plans the join and whether it uses your index, so you catch full table scans before your users do.

Only matches? INNER JOIN.
Keep the left side? LEFT JOIN.
Keep both sides? LEFT JOIN UNION RIGHT JOIN.
Then index the join columns and confirm with EXPLAIN.

CREATE INDEX idx_orders_customer
  ON orders (customer_id);
EXPLAIN SELECT ... JOIN ...;

Key takeaways

Joins match rows on related keys, usually a primary key and a foreign key.
Normalized data stores each fact once, so reading it back needs joins.
INNER JOIN keeps only matches; LEFT JOIN keeps every left row with NULLs for gaps.
MySQL has no FULL OUTER JOIN, so combine LEFT and RIGHT joins with UNION.
Filter the right-hand table inside ON for outer joins, and never omit ON.
Index the join columns and confirm the plan with EXPLAIN.

Frequently asked questions

What is the difference between INNER JOIN and LEFT JOIN in MySQL?

INNER JOIN returns only rows that have a match in both tables, so unmatched rows disappear. LEFT JOIN keeps every row from the table written first and fills the missing columns with NULL when there is no match.

Does MySQL support FULL OUTER JOIN?

No. MySQL does not support FULL OUTER JOIN, and queries using it fail with a syntax error. You get the same result by writing a LEFT JOIN and a RIGHT JOIN and combining them with UNION, which removes the duplicated matched rows.

Why did my LEFT JOIN return the same rows as an INNER JOIN?

Usually because a condition on the right-hand table sits in the WHERE clause. Unmatched rows have NULL in those columns, so WHERE filters them out. Move the condition into the ON clause to keep every left-table row.

What happens if I forget the ON clause in a join?

MySQL treats a join without a condition as a cross join, pairing every row of one table with every row of the other. The result can become very large and won't answer the question you meant to ask.

Should I index the columns I join on?

Yes. Without an index, MySQL may scan the whole table for each match, which gets slow as data grows. Primary keys are already indexed and InnoDB indexes declared foreign keys automatically; use EXPLAIN to confirm the index is used.

When should I use RIGHT JOIN instead of LEFT JOIN?

RIGHT JOIN keeps every row from the second table, which is the mirror of LEFT JOIN. Most developers simply swap the table order and use LEFT JOIN because it reads more naturally, so RIGHT JOIN mainly appears in the UNION pattern for full outer joins.

Watch the full video on YouTube →

Tag

Ad