JOIN types
- INNER JOIN — returns only rows where the condition is met in both tables. Most common
- LEFT JOIN — all rows from the left table + matching rows from the right (or NULL if no match)
- RIGHT JOIN — the mirror of LEFT JOIN. Rarely used
- FULL OUTER JOIN — all rows from both tables, NULL where there is no match
Example
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';
Performance
JOINs on indexed columns (typically foreign keys) are fast. JOINs on unindexed fields are expensive. EXPLAIN shows whether an index is being used.