SQL Server Joins - cheat sheet

With thanks to SQL Authority

There are 3 main types of join in SQL Server:


1 - Inner Join
2 - Outer Join (3 sub types)
       i   - left outer join (same as 'left join')
       ii  - right outer join (same as 'right join')
       iii - full outer join
3 - Cross Join

Breakdown of join types:

1 - Inner Join

This join returns rows when there is at least one match in both the tables

2(i) - Left Outer Join

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

2(ii) - Right Outer Join

This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

2(iii) - Full Outer Join

This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

3 - Cross Join

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.