1.CROSS JOIN
It (Cartesian product) is the simplest join.
2.INNER JOIN
Sometimes called the "EQUI-JOIN" where tables are combined based on a common column;
SELECT <column_name> FROM <Table1>, <Table2> WHERE (Table1.column = Table2.column)
3.OUTER JOIN
which involves combining all rows of one table with only matching rows from the other table
I).Left Outer Join or Left Join
A cross-join between two tables takes the data from each row in table1 and joins it to the data from each row in table2. If particular row of data not available in table 2 . it shows null .
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
II).Right Outer Join OR Right Join
Same concept
III). Full Outer Join:
In mysql , Full Outer Join=Left Outer Join union Right Outer Join


4. SELF JOIN
A self-join is a query in which a table is joined (compared) to itself.  Self-joins are used to compare values in a column with other values in the same column in the same table.
Example : Correlated sub-query(find-nth-maximum-value-in-sql-server) :
Select * From Employee E1 Where
(4-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary > E1.Salary)
Referred from:
http://www.sqlteam.com/article/find-nth ... sql-server