SQL Joins Demystified

Explain the types of joins in SQL.
Yet another of those common technical interview questions that every interviewer wants to know, every candidate gets wrong and everyone looks up anyway when using them. There are four types of joins that this article focuses on Inner, Outer, Right, and Left. For perspective, the following two tables will be used.

tblTypes ID TYPE 1 Animal 2 Vegetable 3 Mineral 4 TBD

tblItems ID tblTYPE_ID NAME 1 1 Ocelot 2 2 Cardoon 3 1 Banteng 4 1 Guenon 5 3 Vomicine 6 7 Brobee

What is the difference between an Inner Join and an Outer Join? 
By definition an Inner Join (also just called a Join, inner is assumed) connects the rows from both tables when there is a match between the columns compared.
SELECT tblTypes.TYPE, tblItems.NAME FROM tblItems JOIN tblTypes ON tblItems.tblTYPE_ID = tblTypes.ID

TYPE	NAME
Animal	Ocelot
Vegetable	Cardoon
Animal	Banteng
Animal	Guenon
Mineral	Vomicine

An Inner Join or simply Join matches when there is a matching condition as above between the type_id in the Items table and the id in the Types table. Notice that we did not get the 'Brobee' record. There was no matching id to the specified type.
What is we want to get all the records regarless if there is a match in our condition. In that case, use an Outer Join.  Outer is slightly different in that it is comprised of a Left or Right Join.
What is a Right Join and Left Join?
SELECT tblTypes.TYPE, tblItems.NAME FROM tblItems LEFT JOIN tblTypes ON tblItems.tblTYPE_ID = tblTypes.ID

TYPE	NAME
Animal	Ocelot
Vegetable	Cardoon
Animal	Banteng
Animal	Guenon
Mineral	Vomicine
NULL	Brobee

The above left joins records regardless if there is a match for our type_id. All the records were retrieved regardless of a match on our left side table. Conversely, a Right Join will match only the right side of our condition. SELECT tblTypes.TYPE, tblItems.NAME FROM tblItems RIGHT JOIN tblTypes ON tblItems.tblTYPE_ID = tblTypes.ID

TYPE	NAME
Animal	Ocelot
Vegetable	Cardoon
Animal	Banteng
Animal	Guenon
Mineral	Vomicine
TBD	NULL

In summation:
There are four major types of joins: Inner, Outer, Right, and Left. Right and Left are part of an outer join. Inner retrieves all matches from the specified join condition(s). Right retrieves from the right table regardless of a match for the left and Left, okay well you have it now.