Concept of JOIN

Concept of JOIN:

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

INNER JOIN

This join returns rows when there is at least one match in both the tables.
Example:
SELECT * FROM Table1 T1 INNER JOIN Table2 T2 ON T1.col1=T2.col1

OUTER JOIN

There are three different Outer Join methods.

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.
Example:

SELECT * FROM Table1 T1 LEFT OUTER JOIN Table2 T2 ON T1.col1=T2.col1

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.
Example:
SELECT * FROM Table1 T1 RIGHT OUTER JOIN Table2 T2 ON T1.col1=T2.col1

FULL OUTER JOIN

This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.
Example:
SELECT * FROM Table1 T1 FULL OUTER JOIN Table2 T2 ON T1.col1=T2.col1

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.

Below is good reference link for concept of JOIN.
http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/