I agree with Cade about the limitations of Venn diagrams here. A more apposite visual representation might be this.
Tables
SELECT A.Colour, B.Colour FROM A CROSS JOIN B SQL Fiddle
The cross join (or cartesian product) produces a result with every combination of the rows from the two tables. Each table has 4 rows so this produces 16 rows in the result.
SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour SQL Fiddle
The inner join logically returns all rows from the cross join that match the join condition. In this case five do.
SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN (‘Green’,’Blue’) SQL Fiddle
The inner join condition need not necessarily be an equality condition and it need not reference columns from both (or even either) of the tables. Evaluating A.Colour NOT IN ('Green','Blue') on each row of the cross join returns.
An inner join condition of 1=1 would evaluate to true for every row in the cross join so the two are equivalent (SQL Fiddle).
SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour SQL Fiddle
Outer Joins are logically evaluated in the same way as inner joins except that if a row from the left table (for a left join) does not join with any rows from the right hand table at all it is preserved in the result with NULL values for the right hand columns.
SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL SQL Fiddle
This simply restricts the previous result to only return the rows where B.Colour IS NULL. In this particular case these will be the rows that were preserved as they had no match in the right hand table and the query returns the single red row not matched in table B. This is known as an anti semi join.
It is important to select a column for the IS NULL test that is either not nullable or for which the join condition ensures that any NULL values will be excluded in order for this pattern to work correctly and avoid just bringing back rows which happen to have a NULL value for that column in addition to the un matched rows.
SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour SQL Fiddle
Right outer joins act similarly to left outer joins except they preserve non matching rows from the right table and null extend the left hand columns.
SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour SQL Fiddle
Full outer joins combine the behaviour of left and right joins and preserve the non matching rows from both the left and the right tables.
