SQL: SELECT Joins (and their usage) [Part IV]

This is part IV (last one) of a set of SQL notes I wanted to post about my current fitness website project.

These are the links for each part to navigate:

Part I | Part II | Part III | Part IV

When using SELECT, you are able to use a join. There are a few main types of these:
1. Inner Join = selects records where joined keys are in BOTH specified tables.
2. Left Outer Join = selects all from the first (left) table, and matching records from the second (right) table.
3. Right Outer Join = selects all from the first second (right), and matching records from the first (left)table.

Below is example #1: Inner Join.

-- Select only salmon salad and its photos. This is a innnerjoin (synonym to equijoin). not what we want here.
-- If there are no photos, then it is not coming back! 
select *
from dbo.Recipes recipes, dbo.RecipePhotos photos
where recipes.RecipeId = photos.RecipeId
and recipes.Title = 'Salmon Salad'
GO

Below is example #2: Left Outer Join. I want you to notice comparisons between this and the above example. Both of them work and give the same result. Capital letters don't make a difference in SQL Server. And notice how the syntax changes between Example #1 and #2. However, it is NOT best to use example #1. WHY? If both work why not? Well.. let's keep going..

-- Left outer (this is what we would want! photos are ok and optional) for salmon salad. 
SELECT *
FROM dbo.Recipes recipes
LEFT JOIN dbo.RecipePhotos photos
ON recipes.RecipeId = photos.RecipeId
WHERE recipes.Title = 'Salmon Salad'

Below is ANOTHER example of #2: Left Outer Join with a null. Remember that I want to give the users on the site a CHOICE to not add a photo.. maybe they don't have one etc. I certainly don't want it to blow up and not work just because they have a great recipe and no photo. In addition, I don't want them finding some crazy "workaround" to my mistake if I made it an Inner Join where they would upload a 1x1 pixel of white JPEG or something just to make my program happy. Customers first! The Left Outer Join allows them to have a null and be ok, whereas the Inner Join REQUIRES BOTH TABLES as seen in the written SQL in the very first example I gave above.

-- Left outer join
SELECT *
FROM dbo.Recipes recipes
LEFT JOIN dbo.RecipePhotos photos
ON recipes.RecipeId = photos.RecipeId
WHERE recipes.Title = 'KFC Chicken'
-- ORDER BY Recipes.RecipeID;

You can also specify how to order them, but I chose to exclude this in my SQL above as can be seen.

Well, what happened to an example of #3 Right Outer Join? This is why I believe that this is not a terribly useful tool (feel free to disagree with me in the comments below if you can think of a good reason to use this and enlighten me!). Say you need stuff out of some table on the second right called VARIABLE_Y and to check with the table on the first left VARIABLE_X. We will use this as an example as Right Outer Join.

-- Right outer
SELECT *
FROM dbo.VARIABLE_Y recipes
RIGHT JOIN dbo.VARIABLE_X photos
ON recipes.RecipeId = photos.RecipeId
WHERE recipes.Title = 'la la la'

Now, tell me why this isn't easier? Just swap out the variables (X and Y) and make it a Left Outer Join. It does the same thing :D

-- Left outer
SELECT *
FROM dbo.VARIABLE_X recipes
LEFT JOIN dbo.VARIABLE_Y photos
ON recipes.RecipeId = photos.RecipeId
WHERE recipes.Title = 'la la la'