How to JOIN Two MySQL Tables

Three JOIN types you'll reach for every day in MySQL: INNER JOIN (returns rows that match in both tables), LEFT JOIN (returns every row from the left table, matched rows from the right, NULL where there is no match), and RIGHT JOIN (the mirror of LEFT, almost never the right tool). The same three work on every MySQL version from 5.0 to 8.4. I'll walk all three on customers and orders, the canonical pair, and call out the row-multiplication mistake that explains 90% of "why is my JOIN returning more rows than expected" questions.

CREATE TABLE customers (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name        VARCHAR(120) NOT NULL,
  email       VARCHAR(255) NOT NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_email (email)
);

CREATE TABLE orders (
  id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id  INT UNSIGNED NOT NULL,
  total_cents  INT UNSIGNED NOT NULL,
  placed_at    DATETIME     NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Sample data: 5 customers, but only 3 of them have placed orders (and one of those has placed two). The other 2 customers have no orders. This is the shape that reveals the difference between the JOIN types most clearly.

INSERT INTO customers (name, email) VALUES
('Mei Tanaka',    'mei.tanaka@example.com'),
('James Walker',  'james.walker@example.com'),
('Emma Carter',   'emma.carter@example.com'),
('Sofia Marek',   'sofia.marek@example.com'),
('Lukas Becker',  'lukas.becker@example.com');

INSERT INTO orders (customer_id, total_cents, placed_at) VALUES
(1, 4500,  '2024-01-08 09:14:00'),
(1, 8900,  '2024-02-22 17:03:00'),
(3, 12500, '2024-03-11 11:48:00'),
(5, 3200,  '2024-04-04 14:25:00');

Jump to:

• How do I JOIN two MySQL tables?
• INNER JOIN: rows that exist in both tables
• LEFT JOIN: every row on the left, matches where they exist
• RIGHT JOIN: the mirror of LEFT (rarely the right choice)
• FULL OUTER JOIN via UNION (MySQL has no native syntax)
• USING vs ON: when each is the cleaner choice
• Self-join: when both tables are the same table
• The multiplication gotcha: why am I getting more rows?
• Joining on multiple columns or expressions
• Comparison: which JOIN to pick
• FAQ

How do I JOIN two MySQL tables?

The four JOIN shapes you need for almost every query: INNER JOIN (rows that match in both), LEFT JOIN (every left row, matched right or NULL), RIGHT JOIN (mirror of LEFT, rarely used), and the UNION trick that synthesises a FULL OUTER JOIN since MySQL has no native syntax for it. The minimum-correct query is SELECT c.name, o.total_cents FROM customers c INNER JOIN orders o ON o.customer_id = c.id; — pick the join keyword based on whether unmatched rows on either side should appear in the result (INNER drops them, LEFT keeps them on the left, RIGHT keeps them on the right). The join condition goes in the ON clause; the filter (WHERE) runs after the join. If the join column has the same name on both sides, USING (column) is shorter than ON a.column = b.column. Always index the join column on the larger table; without an index, MySQL falls back to a nested-loop scan and the query goes from milliseconds to seconds.

INNER JOIN: rows that exist in both tables

INNER JOIN (often just written as JOIN) returns only the rows where the join condition matches in both tables. Customers without orders disappear from the result; orders pointing at a deleted customer (an orphan) also disappear.

SELECT c.name, c.email, o.total_cents, o.placed_at
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
ORDER BY o.placed_at;

Result (4 rows):

+---------------+----------------------------+-------------+---------------------+
| name          | email                      | total_cents | placed_at           |
+---------------+----------------------------+-------------+---------------------+
| Mei Tanaka    | mei.tanaka@example.com     | 4500        | 2024-01-08 09:14:00 |
| Mei Tanaka    | mei.tanaka@example.com     | 8900        | 2024-02-22 17:03:00 |
| Emma Carter   | emma.carter@example.com    | 12500       | 2024-03-11 11:48:00 |
| Lukas Becker  | lukas.becker@example.com   | 3200        | 2024-04-04 14:25:00 |
+---------------+----------------------------+-------------+---------------------+

Mei appears twice because she has two orders — each row in the result represents one row from the right (orders) table joined to its matching customer. James and Sofia do not appear at all because they have no orders.

This is the join you want for "show me all the orders, with the customer attached". Use INNER JOIN whenever the right side rows are the focus of the query.

LEFT JOIN: every row on the left, matches where they exist

LEFT JOIN returns every row from the left table, regardless of whether there's a match in the right table. Where a match exists, the right-table columns are populated; where no match exists, they are NULL.

SELECT c.name, c.email, o.total_cents, o.placed_at
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.placed_at;

Result (6 rows — every customer plus their orders if any):

+---------------+----------------------------+-------------+---------------------+
| name          | email                      | total_cents | placed_at           |
+---------------+----------------------------+-------------+---------------------+
| Mei Tanaka    | mei.tanaka@example.com     | 4500        | 2024-01-08 09:14:00 |
| Mei Tanaka    | mei.tanaka@example.com     | 8900        | 2024-02-22 17:03:00 |
| James Walker  | james.walker@example.com   | NULL        | NULL                |
| Emma Carter   | emma.carter@example.com    | 12500       | 2024-03-11 11:48:00 |
| Sofia Marek   | sofia.marek@example.com    | NULL        | NULL                |
| Lukas Becker  | lukas.becker@example.com   | 3200        | 2024-04-04 14:25:00 |
+---------------+----------------------------+-------------+---------------------+

James and Sofia now appear, with NULL for the order columns. This is the join you want when the left table is the focus and the right-table data is optional. "Show me every customer, with their orders if they have any" is the classic use case.

LEFT JOIN is also the basis for finding rows that exist on the left but not the right (anti-join): add WHERE right.col IS NULL to the query. That pattern has its own walkthrough at How to Find Rows in One MySQL Table Not in Another.

RIGHT JOIN: the mirror of LEFT (rarely the right choice)

RIGHT JOIN is the mirror of LEFT JOIN: every row from the right table, regardless of left-side match. In practice almost nobody writes RIGHT JOIN because it is always more readable to flip the table order and use LEFT JOIN instead.

-- These two queries return the same rows in the same order:

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

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

Both queries return every row from the orders table, with customer info where it exists. The second form (LEFT JOIN with tables swapped) is the one a human will read more easily a year later. Stick to LEFT JOIN whenever possible.

FULL OUTER JOIN via UNION (MySQL has no native syntax)

MySQL 8.0 and 8.4 still do not implement FULL OUTER JOIN as a keyword. PostgreSQL, SQL Server, and Oracle all do; MySQL is the holdout. The standard workaround is UNION of a LEFT JOIN and a LEFT JOIN with the sides swapped:

SELECT c.id AS customer_id, c.name, o.id AS order_id, o.total_cents
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id

UNION

SELECT c.id AS customer_id, c.name, o.id AS order_id, o.total_cents
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

Read this as: "every customer with their orders if any, unioned with every order with its customer if any". The plain UNION (not UNION ALL) deduplicates the rows that exist in both halves, leaving you with the symmetric difference plus the intersection — the FULL OUTER shape.

Real-world use cases are rare. The pattern shows up most often in data-quality audits where you want to surface both orphan orders (customer_id pointing at a deleted customer) and customers with no orders in one query. For the orphan-only case, the cleaner pattern is the anti-join walkthrough in How to Find Rows in One MySQL Table Not in Another.

USING vs ON: when each is the cleaner choice

When the join column has the same name on both tables, USING (column) is shorter than ON a.column = b.column and de-duplicates the column in the output:

-- Two queries, same result. The second is shorter when the column names match.

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

-- If the foreign key column were named 'id' on both sides:
SELECT customers.name, orders.total_cents
FROM customers
JOIN orders USING (id);  -- 'id' appears once, not twice, in SELECT *

The catch: USING only works when the column names are identical. The customers.id and orders.customer_id pair in the example schema does not qualify (different names), so you need ON. For an orders table with a customer_id column joined to a customers table that also exposed customer_id (rare in normalised schemas), USING (customer_id) would be the cleanest form.

Use ON for the everyday case. Reach for USING only in the specific situation where it shortens the query meaningfully.

Self-join: when both tables are the same table

A self-join is just a JOIN where both sides reference the same table. It comes up in two patterns: hierarchies (an employees table with a manager_id pointing at another row in the same table) and pair-matching (every pair of rows that share some property).

-- Hierarchy: employees and their managers, both from the same table
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

-- Pair-matching: every pair of orders placed by the same customer
SELECT a.id AS order_a, b.id AS order_b, a.customer_id
FROM orders a
JOIN orders b
ON a.customer_id = b.customer_id
AND a.id < b.id;  -- avoid (a, b) AND (b, a) and (a, a)

The a.id < b.id filter on the second query is what stops it returning both (1, 2) and (2, 1) for the same pair, plus the trivial (1, 1) self-pair. Without it you get every ordered pair, three times more rows than you usually want.

Self-joins are also how duplicate detection works without window functions; the self-join pattern in How to Find Duplicate Rows in MySQL is the exact same shape applied to a duplicate-key column.

The multiplication gotcha: why am I getting more rows?

The number-one JOIN bug: the result has way more rows than expected. This almost always means the join condition matches multiple rows in one of the tables, so each row in the other table gets multiplied.

Imagine the customers table also had a customer_tags table for marketing labels, and Mei had been tagged with both 'VIP' and 'newsletter'. A naive three-table join produces a cross-product:

-- BAD: this returns 4 rows for Mei's 2 orders × 2 tags
SELECT c.name, o.total_cents, t.tag
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN customer_tags t ON t.customer_id = c.id;

Mei has 2 orders and 2 tags. The query joins customers to orders (2 rows for Mei) then joins those 2 rows to her 2 tags, producing 4 result rows for Mei alone. None of the duplicates represents new information.

The fix is to aggregate one side or pull it into a subquery so each customer row joins against a single tag row:

-- GOOD: comma-separate the tags so there's one row per customer + order
SELECT c.name, o.total_cents, GROUP_CONCAT(t.tag) AS tags
FROM customers c
JOIN orders o ON o.customer_id = c.id
LEFT JOIN customer_tags t ON t.customer_id = c.id
GROUP BY c.id, o.id;

GROUP_CONCAT collapses Mei's tags into a single 'VIP,newsletter' string per row, and the GROUP BY puts each customer-order pair on its own line. The result has the expected 4 rows (one per order), not the inflated 6.

If you do find a JOIN producing unexpected rows, the duplicate-finder query from How to Find Duplicate Rows in MySQL is a fast way to confirm what's multiplying.

Joining on multiple columns or expressions

The ON clause is a normal boolean expression — anything that returns true or false qualifies. Multi-column joins, computed joins, and range joins all work.

-- Multi-column: match on customer_id AND tenant_id
SELECT c.name, o.total_cents
FROM customers c
JOIN orders o
ON o.customer_id = c.id
AND o.tenant_id  = c.tenant_id;

-- Computed: match on lowercase email
SELECT c.name, o.total_cents
FROM customers c
JOIN orders o ON LOWER(o.customer_email) = LOWER(c.email);

-- Range: match orders placed within 7 days of customer creation
SELECT c.name, o.placed_at
FROM customers c
JOIN orders o
ON o.customer_id = c.id
AND o.placed_at BETWEEN c.created_at AND c.created_at + INTERVAL 7 DAY;

For the range and computed forms, performance depends on indexes. A JOIN on LOWER(o.customer_email) cannot use a regular index on o.customer_email; you would need a functional index on LOWER(customer_email) (MySQL 8.0.13+) to keep it fast on large tables.

Comparison: which JOIN to pick

INNER JOIN and LEFT JOIN cover 95% of real-world cases. The other shapes (RIGHT JOIN, CROSS JOIN, FULL OUTER JOIN — which MySQL does not support directly) are rare enough that you can look them up when you need them.

What to do next

If your goal is to find rows in one table that do not exist in another — customers who have never placed an order, products that were never sold, students missing from a roster — that anti-join pattern has its own walkthrough: How to Find Rows in One MySQL Table Not in Another covers LEFT JOIN ... IS NULL, NOT EXISTS, and NOT IN with their performance trade-offs.

If a JOIN unexpectedly multiplies rows in your result, the duplicate-detection patterns in How to Find Duplicate Rows in MySQL are a quick way to confirm and isolate which side is doing the multiplying. Once located, How to Delete Duplicate Rows in MySQL is the matching cleanup.

If a JOIN slow-down comes from a column-type mismatch (an INT joined to a BIGINT column, for instance, forces implicit conversion and disables the index), How to Change a MySQL Column Type is the fix; MySQL Field Types and Sizes is the reference for picking the right type up front. For adding a new join column to an existing table, How to Add a Column to a MySQL Table covers the ALGORITHM=INSTANT path that finishes in milliseconds even on large tables.

For datasets that flow in and out via CSV, How to Import a CSV File Into MySQL and How to Export a MySQL Table to CSV are the bookends. For full-database backup and restore around JOIN-heavy schemas, How to Export or Back Up All MySQL Databases covers mysqldump --single-transaction --routines --events. If you need to format a joined DATETIME column for a report, How to Format a MySQL DATETIME in a SELECT is the lookup.

For the wider reference covering GROUP BY, GROUP_CONCAT, aggregate functions, and the other clauses you commonly combine with JOINs, see the MySQL Cheat Sheet.

External reference: official documentation on JOIN syntax and the optimization notes for joins.

FAQ