How do SQL joins work and how do you choose the right one?

A join combines rows from two sources according to a predicate. The database may use nested loops, a hash join or a merge join depending on size, indexes and statistics. The logical result is the same; the performance is not.

SELECT u.id, u.email, o.id AS order_id, o.total_cents
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= '2026-01-01'
ORDER BY u.id, o.id;

Types and traps

• INNER JOIN keeps only rows satisfying the condition
• LEFT JOIN keeps all left rows, even without a match
• RIGHT JOIN keeps all right rows; it is often rewritten as a left join for readability
• FULL OUTER JOIN keeps unmatched rows from both sides
• CROSS JOIN has no predicate and can explode row counts
• A condition in WHERE on the right table can turn a left join into an inner join
• Indexes on join keys matter, especially for nested-loop plans

A frequent bug is putting a filter for the optional side in WHERE instead of the ON clause. For example, WHERE o.status = 'paid' removes users with no orders, because null does not satisfy the comparison. If the intent is to keep all users and only attach paid orders, put the condition in ON.