Joins (All Types): Combining Tables the Right Way
Joins are one of those things that look confusing at first but become second nature once you have a mental model for them. The core idea is simple: joins let you combine rows from two or more tables based on a related column.
I'll use the same two tables throughout so you can see exactly how each join type behaves differently.
The Setup
users table:
| user_id | name |
|---|---|
| 1 | Lokesh |
| 2 | Rahul |
| 3 | Sneha |
orders table:
| order_id | user_id | item |
|---|---|---|
| 101 | 1 | Keyboard |
| 102 | 1 | Mouse |
| 103 | 2 | Monitor |
| 104 | 99 | Headphones |
Notice: user 3 (Sneha) has no orders. And order 104 has user_id = 99 which doesn't exist in the users table. These edge cases are what reveal the difference between join types.
INNER JOIN
"Only rows that have a match in BOTH tables."
This is the most common join. If there's no match, the row is excluded from the result — from either side.
SELECT users.name, orders.item
FROM users
INNER JOIN orders ON users.user_id = orders.user_id;
Result:
| name | item |
|---|---|
| Lokesh | Keyboard |
| Lokesh | Mouse |
| Rahul | Monitor |
Sneha is gone — she has no orders. Order 104 is gone — no matching user. Only the intersection makes it through.
LEFT JOIN (LEFT OUTER JOIN)
"All rows from the LEFT table, with matching rows from the right. Non-matches get NULL."
SELECT users.name, orders.item
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id;
Result:
| name | item |
|---|---|
| Lokesh | Keyboard |
| Lokesh | Mouse |
| Rahul | Monitor |
| Sneha | NULL |
Sneha shows up now — with NULL for item because she has no orders. Order 104 (the orphan order) is still excluded — it's not on the left side.
Use this when: You want all records from the left table regardless of whether they have a match — like "show me all users and their orders, if any."
RIGHT JOIN (RIGHT OUTER JOIN)
"All rows from the RIGHT table, with matching rows from the left. Non-matches get NULL."
SELECT users.name, orders.item
FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;
Result:
| name | item |
|---|---|
| Lokesh | Keyboard |
| Lokesh | Mouse |
| Rahul | Monitor |
| NULL | Headphones |
Now order 104 shows up with NULL for name (no matching user). Sneha disappears — she's not on the right side.
In practice, RIGHT JOIN is rarely used. You can always rewrite it as a LEFT JOIN by swapping the table order. Most developers stick to LEFT JOIN for consistency and readability.
FULL OUTER JOIN (FULL JOIN)
"All rows from BOTH tables. Non-matches on either side get NULL."
SELECT users.name, orders.item
FROM users
FULL OUTER JOIN orders ON users.user_id = orders.user_id;
Result:
| name | item |
|---|---|
| Lokesh | Keyboard |
| Lokesh | Mouse |
| Rahul | Monitor |
| Sneha | NULL |
| NULL | Headphones |
Everything shows up. Sneha (no orders) gets NULLs. The orphan order (no user) gets NULLs.
Use this when: You want to find unmatched rows on BOTH sides — like an audit: "show me users with no orders AND orders with no valid user."
MySQL doesn't support FULL OUTER JOIN directly. You can simulate it with a LEFT JOIN UNION a RIGHT JOIN.
CROSS JOIN
"Every row from the left table combined with every row from the right table. No condition needed."
SELECT users.name, orders.item
FROM users
CROSS JOIN orders;
This produces a cartesian product — if users has 3 rows and orders has 4, you get 3 × 4 = 12 rows. Every possible combination.
Result: 12 rows — Lokesh paired with every order, Rahul with every order, Sneha with every order.
Use this when: You intentionally want all combinations — like generating a test dataset, or pairing every product with every size/color variant.
Be careful with CROSS JOIN on large tables. 1,000 rows × 1,000 rows = 1,000,000 rows in the result. It can bring a database to its knees.
SELF JOIN
"A table joined with itself."
Sometimes the relationship is within the same table. A classic example: an employees table where each employee has a manager_id that references another employee_id in the same table.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
Result:
| employee | manager |
|---|---|
| Lokesh | Rahul |
| Sneha | Rahul |
| Rahul | NULL |
Rahul is the top-level manager — he has no manager, so NULL.
All Join Types at a Glance
| Join Type | What it returns |
|---|---|
| INNER JOIN | Only matching rows from both tables |
| LEFT JOIN | All rows from left + matched rows from right (NULL for no match) |
| RIGHT JOIN | All rows from right + matched rows from left (NULL for no match) |
| FULL OUTER JOIN | All rows from both tables (NULL where no match on either side) |
| CROSS JOIN | Every combination of rows (cartesian product) |
| SELF JOIN | Table joined to itself |
A Visual Way to Think About It
Think of each table as a circle. The overlap in the middle is where rows match.
- INNER JOIN → only the overlap.
- LEFT JOIN → the entire left circle + the overlap.
- RIGHT JOIN → the entire right circle + the overlap.
- FULL OUTER JOIN → both entire circles.
- CROSS JOIN → completely ignores overlap, just multiplies everything.
Once you internalize which rows survive in each join type, writing and reading SQL queries becomes a lot more intuitive. The Venn diagram mental model is the one that clicked for me — and it's been solid ever since.