Combining related data.
WHY
Useful information usually spans tables. Orders in one, customer details in another, linked by an id.
A JOIN combines them into one result.
THE BASIC FORM
SELECT a.column, b.column FROM table_a a JOIN table_b b ON a.id = b.table_a_id;
The ON clause states how the tables relate.
INNER JOIN
The default. Returns only rows with a match in both tables.
A row with no match is excluded entirely, which is frequently not what people expect.
LEFT JOIN
Returns every row from the first table, with NULL where the second has no match.
Use this when you want everything from one side regardless.
FINDING ORPHANS
A LEFT JOIN with WHERE the second table's id IS NULL finds rows with no match.
That is how you find orphaned metadata: rows referencing content that no longer exists.
PRACTICAL ADVICE
Write it as a SELECT and look at the result before using it for anything destructive.
Joins on large unindexed tables are slow.
