MySQL JOIN Types

Also Known As

TL;DR

Explanation

INNER JOIN returns only rows where the condition matches in both tables. LEFT JOIN returns all rows from the left table plus matching rows from the right (NULLs where no match). RIGHT JOIN is the mirror of LEFT JOIN — rarely used since LEFT JOIN with swapped tables is equivalent. CROSS JOIN returns the Cartesian product. SELF JOIN joins a table to itself for hierarchical data. JOIN performance depends entirely on indexes on the joined columns — always index foreign keys.

Common Misconception

Why It Matters

Common Mistakes

• Using INNER JOIN when some records legitimately have no related row — silently dropping valid data from results.
• Not indexing the JOIN column — causes full table scan on every join operation.
• Writing multiple LEFT JOINs without aliases — results in ambiguous column names.

Avoid When

• Avoid RIGHT JOIN — rewrite as LEFT JOIN with swapped table order for consistency.
• Avoid implicit joins (comma-separated tables in FROM) — use explicit JOIN syntax for clarity.

When To Use

• Use INNER JOIN when you only want rows that have a match in both tables.
• Use LEFT JOIN when you need all rows from the primary table, with NULLs where no related row exists.
• Always index the JOIN column on both sides for efficient execution.

Code Examples

-- INNER JOIN silently drops users with no orders
SELECT u.email, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
-- Users with no orders are missing from results — may be a reporting bug

-- LEFT JOIN: all users, with their latest order (NULL if no orders)
SELECT u.id, u.email, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.active = 1;

-- INNER JOIN: only users who have placed orders
SELECT u.email, COUNT(o.id) as order_count
FROM users u
INNER JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/join.html

Tags

Related Terms