> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL JOIN Performance and Common Mistakes > Why a MySQL JOIN runs slow or returns the wrong rows: missing indexes, a WHERE that undoes a LEFT JOIN, duplicate rows, NULL join keys, and no FULL OUTER JOIN. > [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md) > > All examples on this page work on VillageSQL. Install Now → JOINs work the first time and go wrong later. The query is correct SQL, the server raises nothing, and the answer is a row count you were not expecting or a report that takes 40 seconds. This guide covers those cases: what makes a join slow, and the 4 ways one quietly returns the wrong rows. If you are still deciding which kind of join to write, the tutorial teaches each one against a sample database: [how joins work](https://villagesql.com/docs/en/tutorial/joins.md), [INNER JOIN](https://villagesql.com/docs/en/tutorial/inner-join.md), [LEFT JOIN](https://villagesql.com/docs/en/tutorial/left-join.md), [RIGHT JOIN](https://villagesql.com/docs/en/tutorial/right-join.md), [CROSS JOIN](https://villagesql.com/docs/en/tutorial/cross-join.md) and [self join](https://villagesql.com/docs/en/tutorial/self-join.md). ## The tables used in this guide ```sql theme={null} CREATE TABLE customers ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL ); CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT, -- nullable: orders without a customer amount DECIMAL(10,2) NOT NULL ); INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol'); INSERT INTO orders VALUES (1, 1, 99.00), -- Alice's order (2, 1, 45.00), -- Alice's second order (3, 2, 120.00), -- Bob's order (4, NULL, 15.00); -- order with no customer ``` ## Index the join column Without an index on the joining column, the server builds a hash table over one side and scans the other. `EXPLAIN FORMAT=TREE` names it: ```sql theme={null} EXPLAIN FORMAT=TREE SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id\G ``` ```text theme={null} *************************** 1. row *************************** EXPLAIN: -> Inner hash join (o.customer_id = c.id) (cost=2 rows=3) -> Table scan on o (cost=0.117 rows=4) -> Hash -> Table scan on c (cost=0.55 rows=3) ``` A hash join reads each table once. On 7 rows that is nothing. On real tables it is 2 full table scans plus the memory to hold the hash, and when the build side does not fit in `join_buffer_size` the server spills it to disk in batches. Adding the index replaces all of that with a lookup: ```sql theme={null} CREATE INDEX idx_orders_customer ON orders (customer_id); ``` Ask for the same plan again and the hash is gone: ```sql theme={null} EXPLAIN FORMAT=TREE SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id\G ``` ```text theme={null} *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=1.95 rows=4) -> Table scan on c (cost=0.55 rows=3) -> Index lookup on o using idx_orders_customer (customer_id=c.id) (cost=0.378 rows=1.33) ``` Index the foreign key column on the "many" side of a one-to-many relationship. The primary key on the "one" side is already indexed, so that half needs nothing. In the default `EXPLAIN` output the same 2 plans read as `Extra: Using join buffer (hash join)` and `type: ref`. That table also shows the order the server chose to join in, which matters as soon as a query has 3 or more tables: the server reads them top to bottom, and the row estimate on the first one multiplies through everything below it. [Reading EXPLAIN output](https://villagesql.com/docs/en/guides/reading-explain.md) covers the rest of the plan. An equality condition is what a hash join can look up. Join on a range or an inequality instead and the server still builds the hash, but it has nothing to look up with: the plan reads `Inner hash join (no condition)` with the test in a `Filter` above it, so every pair of rows is formed and then thrown away. That is the cost an indexed equality join avoids. ## A WHERE clause turns a LEFT JOIN back into an INNER JOIN The most common wrong-row-count bug, and nothing warns you. ```sql theme={null} -- Excludes customers with no orders, so this is an inner join now SELECT c.name, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.amount > 50; -- Keeps customers with no orders SELECT c.name, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.amount > 50; ``` The join runs first and gives unmatched customers a NULL `o.amount`. `WHERE` then tests each row, `NULL > 50` is NULL rather than true, and those rows are dropped. Moving the condition into `ON` makes it part of what counts as a match instead. The rule: on an outer join, a condition on the inner table belongs in `ON`, and a condition on the preserved table belongs in `WHERE`. The exception is `WHERE IS NULL`, where dropping the matched rows is the point. ## A NULL in the join column matches nothing `orders.customer_id` is nullable, and order 4 has no customer. An inner join drops it, because NULL is not equal to anything, including another NULL. ```sql theme={null} SELECT c.name, o.amount FROM customers c INNER JOIN orders o ON o.customer_id = c.id ORDER BY c.name, o.amount; ``` ```text theme={null} +-------+--------+ | name | amount | +-------+--------+ | Alice | 45.00 | | Alice | 99.00 | | Bob | 120.00 | +-------+--------+ 3 rows in set (0.00 sec) ``` 3 rows from 4 orders. Carol is missing because she has no orders, which is the expected half. Order 4 is missing because its `customer_id` is NULL, which is the half that surprises people: the row exists, its amount is real, and no join condition can reach it. A revenue total built on that join is short by 15.00 and nothing says so. When a join column is nullable, decide what an orphan row should do before you write the join. `LEFT JOIN` from `orders` keeps it with a NULL customer name. [NULL in MySQL](https://villagesql.com/docs/en/guides/null-in-mysql.md) covers the comparison rule behind it. ## A one-to-many join multiplies your rows A join returns 1 row per match, so joining to a table that holds several matching rows repeats the left row once per match. Totals computed over that result are then wrong, usually too high, and the query still looks right. ```sql theme={null} -- Alice appears twice, because she has 2 orders SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id; ``` Before treating duplicate rows as a bug, confirm the relationship really is one-to-one. When it is one-to-many and you want 1 row per customer, aggregate: `GROUP BY c.id` with `SUM` or `COUNT` over the joined column. See [MySQL GROUP BY performance](https://villagesql.com/docs/en/guides/group-by-having.md). The same arithmetic goes wrong faster with 2 one-to-many joins in one query, where the 2 sets of matches multiply against each other. Aggregate each in its own subquery rather than joining both at once. ## A missing ON gives you every combination MySQL accepts `INNER JOIN` with no `ON` clause and reads it as a cross join, so a typo produces the product of both tables rather than an error. 2 tables of 10,000 rows give 100 million rows. When a join returns a suspiciously round and suspiciously large number, check for a missing `ON` before anything else. A comma between tables in `FROM` is the same thing in older syntax, with the matching condition usually further down in `WHERE`. ## MySQL has no FULL OUTER JOIN There is no `FULL OUTER JOIN` in MySQL. Combine the 2 halves with `UNION`: ```sql theme={null} SELECT c.name, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.id UNION SELECT c.name, o.amount FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id; ``` `UNION` removes duplicate rows, which is what makes the 2 halves join up cleanly. `UNION ALL` does not emulate a full outer join at all: it returns every matched row twice, once from each half. When your data has duplicate rows you need to keep, deduplicate on a key column instead of reaching for `UNION ALL`. ## JOIN or subquery A join and an `IN` subquery often express the same filter, and which one the optimizer prefers depends on the data. When you only need to know whether a related row exists and want none of its columns, `EXISTS` usually reads better and lets the server stop at the first match. [Subqueries vs JOINs in MySQL](https://villagesql.com/docs/en/guides/subqueries-vs-joins.md) compares them with plans. ## Troubleshooting | Problem | Cause and fix | | :- | :- | | `LEFT JOIN` returns fewer rows than expected | A `WHERE` condition on the right table is dropping the NULL rows. Move it to `ON`. | | A join on large tables is slow | The join column on the "many" side has no index. Check `EXPLAIN` for `type: ALL`. | | Unexpected duplicate rows | The relationship is one-to-many. Aggregate with `GROUP BY`, or confirm it should be one-to-one. | | A total is too high | Same cause: the join repeated the left row, so the sum counts it several times. | | A join returns a huge round number of rows | The `ON` clause is missing, so the join is a cross join. | | Rows are missing that exist in both tables | The join column is NULL on one side, so nothing matches it. | | `RIGHT JOIN` is hard to follow | Rewrite as `LEFT JOIN` with the tables swapped. Same result. | ## See also * [Reading EXPLAIN output](https://villagesql.com/docs/en/guides/reading-explain.md) — the plan that tells you which index a join used * [Foreign keys in MySQL](https://villagesql.com/docs/en/guides/foreign-keys.md) — the constraint that decides which column to index * [MySQL GROUP BY performance](https://villagesql.com/docs/en/guides/group-by-having.md) — aggregating a one-to-many join back to 1 row * [How joins work](https://villagesql.com/docs/en/tutorial/joins.md) — the tutorial, if you want the basics first This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.