A Join combines records from two (or more) tables to create a new data set.  There are several ways to join SQL tables.  Below are the most common Joins.

• INNER Join – Result will contain matching records from both tables
• LEFT Join – Result will contain all records from the “left” table even if the join-condition does not find any matching record in the “right” table
• RIGHT Join – Result will contain all records from the “right” table even if the join-condition does not find any matching record in the “left” table
• FULL Join – Result will contain all records from both tables, and fill in NULLs for missing matches on either side
• CROSS Join – Result will contain returns the cartesian product of the sets of records from the two joined tables (Dangerous with large tables)

Instead of including all the normal code snippets illustrating each type of join, I am listing links to others who have done that job very well.

• Jeff Atwood gives a wonderful Visual Explanation of SQL Joins.  He describes each join in detail.
• Kathi Kellenberger posted a great article on Writing Outer Joins in T-SQL.  She gives a brief review of the INNER Join, then goes on to a much more detailed description of OUTER Joins.
• Jeff Smith gives us a warning to Be Careful When Mixing INNER and OUTER Joins.  Warnings are always a great way to learn from other people’s experiences.
• Kenneth Downs created a very informative post about Joins titled The JOIN is the Cornerstone of Powerful Queries.  This article too, provides an abundance of information about Joins.
• Jason Massie suggested a great set of articles on joins from Craig Freedman.  There looks to be some in-depth information on joins here.

The above articles are ones the I will likely reference over and over again.