Guide
SQL JOINs Explained: Inner, Left, Self, and Anti-Joins
Understand INNER JOIN, LEFT JOIN, self joins and anti-joins through SQL interview questions, with the double-counting and NULL pitfalls that trip up candidates.
The four joins you must know
INNER JOIN keeps only rows with a match on both sides. LEFT JOIN keeps every row from the left table and fills missing matches from the right with NULL. RIGHT JOIN is the mirror image, and FULL OUTER JOIN keeps everything — SQLite supports neither, so interviews rarely need them.
A self join joins a table to itself with aliases, letting you compare rows within one table: employees to managers, weather to the previous day.
Anti-joins: finding what is missing
The classic anti-join is LEFT JOIN ... WHERE right.key IS NULL, or NOT EXISTS (SELECT 1 ...). Both find rows in the left table with no matching row on the right: customers without orders, visits without transactions.
NOT EXISTS is usually the most readable, and it behaves correctly when the right side has NULLs — unlike NOT IN, which silently returns nothing if the subquery contains NULL.
The NULL trap that turns LEFT JOIN into INNER JOIN
If you add a WHERE condition on a column of the right table, unmatched rows (where that column is NULL) are filtered out and the LEFT JOIN behaves like an INNER JOIN. Move such conditions into the ON clause instead: LEFT JOIN b ON ... AND b.status = 'active'.
Double counting: when one side has duplicates
If the right table has two matching rows, the left row is duplicated in the result. This is correct for a many-to-one relationship, but it breaks aggregates: SUM over the joined result double-counts. De-duplicate the right side first, or aggregate before joining.