SQL JOINs Break at the Boundaries — Test the Edge Cases Before They Hit Production
JOIN mistakes don't throw syntax errors — they silently drop rows, inflate counts, or turn outer joins into inner joins. A developer who hasn't run these boundary cases will ship reports that undercount users or overcount revenue, and the bug will survive code review because the SQL looks correct.
Three small tables with orphan rows — a user without orders, an order without a user, a payment without an order — expose exactly how each JOIN type behaves at the edges. INNER JOIN drops unmatched rows from both sides. LEFT JOIN preserves every row from the left table and fills unmatched right-side columns with NULL. RIGHT JOIN does the reverse. FULL JOIN keeps everything, making gaps visible on both sides at once.
The real trap is condition placement. Putting a filter on the right table inside the ON clause limits which rows participate in the join but still preserves left-table rows. Moving that same filter to WHERE silently converts the outer join into an inner join by eliminating rows where the right-side columns are NULL. The SQL looks almost identical; the row count can drop by more than half.
Statistical queries compound the confusion. After a one-to-many LEFT JOIN, COUNT(*) counts the expanded result rows — not the distinct entities. A user with two orders contributes two rows, inflating totals. COUNT(DISTINCT left_table.id) recovers the true entity count, while COUNT(right_table.id) correctly counts only matched detail rows. EXISTS handles simple presence checks without the row-multiplication problem at all.
Most JOIN bugs are invisible in code review because the SQL parses correctly and returns non-empty results — the error is in what's missing or double-counted.
The ON-vs-WHERE distinction is the single most common outer-join mistake in production, and it's rarely caught by ORM abstractions that obscure where the condition actually lands.
FULL JOIN is underused in MySQL-centric teams because MySQL didn't support it until recent versions, yet it's the most direct way to surface orphan data on both sides of a relationship.
COUNT(*) after a join is a loaded footgun: it counts rows, not entities, and the difference only becomes visible when the data contains one-to-many relationships or unmatched rows.
Deliberately seeding test data with orphan rows — users without orders, orders without users — exposes JOIN behavior that clean demo data hides, and should be standard practice before writing any reporting query.