Identifying Missing Matches in a Mysql DB

Question

Is there a way to identify missing combination in a join table. eg: Three tabes

animals

food

animals_food

can identify wether any particular food hasn't been associated with an animal, or vice versa?

Answer 1

You can use a left join:

SELECT animals.id FROM animals LEFT JOIN animals_food ON animals.id = animals_food.animals_id WHERE animals_food.food_id IS NULL;

A left join contains all rows from the left table (animals) even if the join condition does not find any matching rows from the right table (animals_food). When a match is not found, the columns of the right table are replaced with NULL.

The WHERE condition in my query removes all rows that have matches, leaving only orphan rows from the left table in the result.