MySQL JOIN Types
debt(d7/e3/b3/t7)
Closest to 'only careful code review or runtime testing' (d7). The detection_hints list semgrep and flag the implicit join pattern (FROM t1, t2 WHERE ...), but the more dangerous misuse — INNER JOIN silently dropping valid rows — produces no error, no warning, and no linter alert. The query runs and returns results; only careful review of the result set or business-logic testing reveals the missing rows.
Closest to 'simple parameterised fix' (e3). The quick_fix is a direct keyword swap (INNER → LEFT), but the common_mistakes show the fix may require auditing multiple queries across a codebase, adding indexes, and adding aliases to resolve ambiguous column names — slightly more than a pure one-liner but well within a single component.
Closest to 'localised tax' (b3). The applies_to scope is web and cli, and the tags are tightly scoped to mysql/sql. Each query that uses the wrong join type is a localised data-correctness issue. It doesn't impose a codebase-wide architectural tax — only the queries touching affected tables need attention.
Closest to 'serious trap' (t7). The misconception field documents that developers believe LEFT JOIN is slower than INNER JOIN (a performance trap), and the common_mistakes highlight that using INNER JOIN when LEFT was needed silently drops valid data — a correctness trap. Both traps contradict reasonable developer intuitions (one about performance, one about data completeness), putting this close to t7.
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;