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.
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..
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.
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.
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
