SQL Joins Explained

Introduction
For some reason I can’t remember the different types of joins or rather what exactly the differences are, I always confuse them (I’m hoping I’m not the only one).  I’ve noticed that a graphical ilustration usually helps, so I’ve added Venn diagrams to aid in the explanation.


First a definition: A SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A Join is a means for combining fields from two tables by using values common to each.

The Joins
I am going to explain seven different ways to to return data from two rational tables. The seven Joins are the Following:
1.    Inner Join
2.    Outer Join
3.    Left Join
4.    Right Join
5.    Left Join Exluding Inner Join
6.    Right Join Excludeding Inner Join
7.    Outer Join Excluding Inner Join


 Inner Join

The INNER JOIN returns rows when there is at least one match in both tables. That is, it will return all of the records in the left table (Table A) that have a matching record in the right table (Table B).

Outer Join

 This can also be called a FULL JOIN or a FULL OUTER JOIN. It will return all of the records from both tables, joining records from the left table (Table A) that match records from the right table (Table B).


Left Join

The LEFT JOIN returns all rows from the left table, the table that is listed first in the SQL statement, (Table A), even if there are no matches in the right table, the table that is listed second in the SQL statement (Table B). And will also return matching records from the right table


Right Join

 This is of course the opposite of the LEFT JOIN. That is, The RIGHT JOIN returns all the records from the right table (Table B), even if there are no matches in the left table (Table A).

Left Join Exluding Inner Join

This is also called a Left Excluding Join. This join will return all of the records in the left table (Table A) that do not match any records in the right table (Table B).



Right Join Excluding Inner Join

Also called a Right Exluding Join. Again this is the opposite of the Left Excluding Join. That is, it will return all of the records in the right table (Table B) that do not match any records in the left table (Table A).

Outer Join Excluding Inner Join

Also Called a Outer Excluding Join, This is the opposite of the Inner Join. That is, it will return all of the records in the left table (Table A) and all of the records in the right table (Table B) that do NOT match. I don’t know how useful this join is, but it exists.

Other joins do exist but these are the ones that I’ve found are most frequently used, except this last one.