Great post by Biplab Paul on SQL joins and por/cons of each one on his blog.
I love to explain things in much simpler way but SQL Joins are one of those concepts which are not so simple to explain and I really spend a lot of time in scribbling on White Board for this. But finally I found an article from Jeff Atwood where he did an incredible job by simply using Venn Diagrams to explain the whole concept. I will take few extract from his article but rest you can read from his blog.
| INNER JOIN:SELECT * FROM TableA
INNER JOIN TableB ON TableA.name = TableB.name |
 |
| OUTER JOIN (1):SELECT * FROM TableA
FULL OUTER JOIN TableB ON TableA.name = TableB.name |
 |
| OUTER JOIN (2):SELECT * FROM TableA
LEFT OUTER JOIN TableB ON TableA.name = TableB.name |
 |
| OUTER JOIN (3):SELECT * FROM TableA
LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null |
 |
| OUTER JOIN (4):SELECT * FROM TableA
RIGHT OUTER JOIN TableB ON TableA.name = TableB.name |
 |
| OUTER JOIN (5):SELECT * FROM TableA
FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null |
 |
| CROSS APPLY (1): without Where ClauseSELECT p.SalesPersonID, t.Name ASTerritory
FROM Sales.SalesPerson p CROSS JOIN Sales.SalesTerritory t ORDER BY p.SalesPersonID |
Note: This can’t be represented by Venn Diagrams but above would multiply all the contents of First Table with all the contents of the second table. |
| CROSS APPLY (2): with Where ClauseSELECT p.SalesPersonID, t.Name ASTerritory
FROM Sales.SalesPerson p CROSS JOIN Sales.SalesTerritory t WHERE p.TerritoryID = t.TerritoryID ORDER BY p.SalesPersonID |
 |
Now when we understood these different types of Joins, let’s talk about some of the internal on How these joins would work internally within SQL Server.
<snip>
Read the rest @> Few things which you may find helpful!! : Simplifying SQL Server Joins (Query and Internals)