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. i.e. tell me what you have in your bar and I will tell you what drinks can be made with a subset of those ingredients.
The solution to pulling this data turned out to be what is called a Left Outer Join and is supported in mySQL 5.0 (and possibly below this but I don't have a lower version to check).
The best way to describe what a left outer join does is an example so let's name two tables: age and beauty (the names of the trial eights in my college boatclub when I was captain).
I may well want to find those people who are beautiful but not old and if would be great to have an effective way to do this without doing some complex IS NOT IN statement.
So what I could do is as follows:
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
Which would give me the following result:
|Brad Pitt||Male||Brad Pitt||Male|
|Jen Anniston||Female||Jen Anniston||Female|
However I would like to just see young beautiful people and so I want to get rid of Brad and Jen (too old). In order to do that I simply select those rows where the Name_a variable is null.
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 WHERE Name_a IS NULL
Job now done the output is as follows and I have gone from a list of old people and a list of beautiful people of all ages to a list which just contains young beautiful people... now off to Google!