MySQL JOINs Explained: INNER, LEFT, RIGHT, CROSS and the Missing FULL OUTER

Joins combine rows from two tables based on a condition. The type of join decides what happens to rows that have no match on the other side — and that single question is the whole subject.

This guide covers every join MySQL supports, with worked output, plus the two things people most often get wrong: emulating the FULL OUTER JOIN that MySQL lacks, and the WHERE clause that silently converts a LEFT JOIN back into an INNER JOIN.

The sample data

SELECT * FROM people;
+------------+--------------+-----+
| name       | phone        | pid |
+------------+--------------+-----+
| Mr Brown   | 01225 708225 |   1 |
| Miss Smith | 01225 899360 |   2 |
| Mr Pullen  | 01380 724040 |   3 |
+------------+--------------+-----+

SELECT * FROM property;
+-----+------+----------------------+
| pid | spid | selling              |
+-----+------+----------------------+
|   1 |    1 | Old House Farm       |
|   3 |    2 | The Willows          |
|   3 |    3 | Tall Trees           |
|   3 |    4 | The Melksham Florist |
|   4 |    5 | Dun Roamin           |
+-----+------+----------------------+

Two deliberate mismatches make the differences visible: Miss Smith (pid 2) has no property, and Dun Roamin (pid 4) has no owner.

The four behaviours at a glance

INNER JOIN — matches only

SELECT name, phone, selling
FROM people
INNER JOIN property ON people.pid = property.pid;

+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+-----------+--------------+----------------------+
4 rows

Miss Smith and Dun Roamin both vanish — neither has a match. Note Mr Pullen appears three times: a join multiplies rows, it does not merely append columns.

JOIN on its own means INNER JOIN. Write INNER explicitly; it costs nothing and makes intent obvious to the next reader.

LEFT JOIN — keep everything on the left

SELECT name, phone, selling
FROM people
LEFT JOIN property ON people.pid = property.pid;

+------------+--------------+----------------------+
| Mr Brown   | 01225 708225 | Old House Farm       |
| Miss Smith | 01225 899360 | NULL                 |
| Mr Pullen  | 01380 724040 | The Willows          |
| Mr Pullen  | 01380 724040 | Tall Trees           |
| Mr Pullen  | 01380 724040 | The Melksham Florist |
+------------+--------------+----------------------+
5 rows

Miss Smith now appears with NULL for selling. This is the join you want whenever the answer is "every X, with their Y if they have one" — every customer with their order count, every product with its reviews.

RIGHT JOIN — keep everything on the right

SELECT name, phone, selling
FROM people
RIGHT JOIN property ON people.pid = property.pid;

+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
| NULL      | NULL         | Dun Roamin           |
+-----------+--------------+----------------------+
5 rows

RIGHT JOIN is LEFT JOIN with the tables swapped, and that is exactly how most teams treat it — as something to avoid. Reading a query where some joins point left and others right is genuinely hard. Convention is to use LEFT JOIN throughout and reorder the tables instead.

The trap: WHERE turns LEFT JOIN into INNER JOIN

This catches people constantly:

-- Intended: every person, plus their London properties
SELECT name, selling
FROM people
LEFT JOIN property ON people.pid = property.pid
WHERE property.city = 'London';    -- Miss Smith disappears!

The LEFT JOIN dutifully produces a row for Miss Smith with all property columns NULL. Then WHERE property.city = 'London' evaluates NULL = 'London', which is NULL — not true — so the row is filtered out. You have silently written an INNER JOIN.

Put the condition in the ON clause instead, where it filters what gets joined rather than what survives:

SELECT name, selling
FROM people
LEFT JOIN property
  ON people.pid = property.pid
 AND property.city = 'London';    -- Miss Smith kept, with NULL

The rule: conditions on the optional table belong in ON; conditions on the required table belong in WHERE. The one exception is deliberately checking for absence, which is the next section.

Anti-joins: finding rows with no match

"Which people own no property?" is a LEFT JOIN plus an IS NULL test:

SELECT name
FROM people
LEFT JOIN property ON people.pid = property.pid
WHERE property.pid IS NULL;

+------------+
| Miss Smith |
+------------+

Test a column that can never legitimately be NULL — the joined key or primary key. Testing a nullable column cannot distinguish "no matching row" from "matched a row whose value is null".

NOT EXISTS expresses the same thing and is often clearer:

SELECT name FROM people p
WHERE NOT EXISTS (SELECT 1 FROM property r WHERE r.pid = p.pid);

Both perform similarly on modern MySQL. Avoid NOT IN against a nullable column — if the subquery returns even one NULL, the whole expression evaluates to unknown and you get zero rows back, which is a genuinely baffling bug to debug.

FULL OUTER JOIN — which MySQL does not have

A FULL OUTER JOIN returns everything from both sides, matched or not. PostgreSQL and SQL Server support it. MySQL does not, at any version — despite the phrase appearing in the title of countless tutorials, this one's included until now.

Emulate it by combining both outer joins:

SELECT name, phone, selling
FROM people
LEFT JOIN property ON people.pid = property.pid

UNION

SELECT name, phone, selling
FROM people
RIGHT JOIN property ON people.pid = property.pid;

+------------+--------------+----------------------+
| Mr Brown   | 01225 708225 | Old House Farm       |
| Miss Smith | 01225 899360 | NULL                 |
| Mr Pullen  | 01380 724040 | The Willows          |
| Mr Pullen  | 01380 724040 | Tall Trees           |
| Mr Pullen  | 01380 724040 | The Melksham Florist |
| NULL       | NULL         | Dun Roamin           |
+------------+--------------+----------------------+
6 rows

Use UNION, not UNION ALL — the matched rows appear in both halves and UNION deduplicates them. If you know there are no duplicates to worry about, UNION ALL with a WHERE … IS NULL filter on the second half is faster, because UNION must sort the entire result to deduplicate.

CROSS JOIN

SELECT name, selling FROM people CROSS JOIN property;   -- 3 × 5 = 15 rows

Every combination, no condition. Genuinely useful for generating grids — every product in every size, every date in every region. It is also what you accidentally produce by omitting the ON clause, which on two large tables can generate billions of rows and take the server down.

Performance

The single most important thing: index the columns you join on. A foreign key column without an index turns each joined row into a full table scan.

CREATE INDEX idx_property_pid ON property (pid);

Verify with EXPLAIN. What you want to see:

• type of ref or eq_ref — an index is being used.
• type of ALL — a full table scan. Fix this first.
• Using join buffer (Block Nested Loop) in Extra — no usable index on the join.

Also make sure joined columns share a type and collation. Joining a VARCHAR to an INT, or two columns with different collations, forces a conversion on every row and silently disables the index.

Summary