I always have trouble keeping the different types of SQL joins straight in my head so here is a quick reference:
Inner Join – An inner join returns only those records from both tables that have a matching value in the related field. In other words only those records that satisfy the join condition.
Outer Joins – Outer joins return records from one table where there are no matching records in the other table. There are three types of outer joins – left outer, right outer, full outer.
Left Outer Join – The left outer join retrieves records from both tables, retrieving all the records from the left table and any records from the right table where the condition values match. If there are no matching values in from the right table, the join still retrieves all the records from the left table. Any columns from the right table that are unmatched are left NULL. Consequently, the resulting recordset often appears to have incomplete records.
Right Outer Join – The right outer join is similar to the left outer join in that it retrieves all the records from one side of the relationship, but this time it’s the right table. Only records where the condition values match are retrieved from the left. A right outer join returns all the records from the right table, or the many side of a relationship.
Full Outer Join – The full outer join retrieves all records from both the left and the right table.
Cross Join – A cross join returns what’s known as a Cartesian product. This means that the join combines every row from the left table with every row in the right table.
Self Join – There’s a special type of relationship that’s called a self join. It’s not really a type of join, since you can apply it to any join type. A self join is rather unique in that it involves a relationship with only one table. Instead of relating a table to a second table, the join is based on a relationship based on the same table. You simply reference the table twice and use an alias to avoid confusion.