mySQL Left Outer Join

So I don’t want to disappoint my friend who describes this site as everything geek… Earlier this week I was trying to work out an efficient way to find which cocktails in my cocktail making database had a certain set of ingredients and no others.

The solution to pulling this data turned out to be what is called a Left Outer Join and is supported in mySQL 5.0. The best way to describe what a left outer join does is an example.

What I could do is: SELECT Beauty.Name AS Name_b, Beauty.Sex AS Sex_b, Age.Name AS Name_a, Age.Sex AS Sex_a FROM Beauty LEFT OUTER JOIN Age ON Beauty.Name = Age.Name

Then I simply select those rows where the Name_a variable is null to get the filtered results. Job done!