Cool explaination: A Visual Explanation of SQL Joins

I stumbled on this web page while doing a little revision. and when I read the comment "If you have tried to understand how joins work and constantly get confused about what join to use, you just need to keep a simple picture in mind (I like pictures). " ... as I like pictures I have borrowed them.

The joins are drawn in the form of  VENN diagrams.

The full article can be found here: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

We will start with just an empty diagram:

INNER JOIN
An inner join only returns those records that have “matches” in both tables.
 

In programming logic – think in terms of AND.

The SQL: SELECT * FROM T1 INNER JOIN T2 ON T1.name = T2.name

OUTER JOIN
An outer join is the inverse of the inner join. It only returns those records not in T1 and T2.

In programming logic – think in terms of NOT AND.

FULL OUTER JOIN
Produces the set of all records in Table A and Table B:

  
The SQL:  SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name

LEFT JOIN
A left join returns all the records in the “left” table (T1) whether they have a match in the right table or not.
If, however, they do have a match in the right table – give me the “matching” data from the right table as well. If not – fill in the holes with null.

LEFT OUTER JOIN
A left outer join combines the ideas behind a left join and an outer join.

Again it is noted that the same thing is possible with a right outer join – most people just use a left one.

Basically – if you use a left outer join you will get the records in the left table that DO NOT have a match in the right table.