Different Types Of JOINS:
     Most of the joins you will come across are based on 
equality, with the equijoin being the most dominant. 
    In this chapter you learned about equijoins; there are 
other types of joins you must become familiar with, most 
notably the self-join, the nonequijoin, and the outer join. 
  
Equijoin or Inner Join (Equality) : Traditional comma-
separated join or ANSI JOIN syntax (including optional 
INNER keyword). 
Natural Join (Equality) : NATURAL JOIN keyword. 
Cross-Join or Cartesian Product (No join condition): 
Traditional comma-separated with the missing join condition 
in the WHERE clause or CROSS JOIN keyword. 
Self-Join (Equality): Equijoin or Inner Join. 
Outer Join (left, right, full):(Equality and extending the 
result set): "Complex Joins" OUTER JOIN keywords or outer 
join operator(+). 
Non-Equijoin (Nonequality of values): "Complex Joins" 
Traditional comma-separated join or ANSI join syntax with 
the ON clause. 
The join criteria is not based on equality.

In SQL Server 2000 we have three types of joins
1. Inner join
2. Outer Join
3. Cross Join
1. Inner Join: Inner Join is the default type of join, it 
will producesses the result set, which contains matched 
rows only.
syntax: select * from table1<innerjoin>table2

2. Outer Join: Outer join produces the results, which 
contains matched rows and unmatched rows.
here we have three types of joins,
1.Left Outer Join 2.Right Outer Join 3.Full Outer Join
Left Outer Join: Left Outer Join producesses the results, 
which contains all the rows from Left table and matched 
rows from Right Table.
syntax: select * from table1<leftouterjoin>table2

Right Outer Join: Right Outer Join producesses the 
resultset, which contains all the rows from right table and 
matched rows from left table.
syntax:select * from table1<right outer join>table2

Full Outer Join: Full Outer Join producesses the resultset, 
which contains all the rows from left table and all the 
rows from right table.
syntax:select * from table1<fullouterjoin>table2

3.Cross Join: A join without having any condition is known 
as Cross Join, in cross join every row in first table is 
joins with every row in second table.
syntax: select * from table1<cross join>table2

Self Join: A join joins withitself is called self join
working with self joins we use Alias tables.

There are 1.equijoins 2.self joins,3.Crossjoins.
4.Outerjoins->Leftouterjoin,Rightouterjoin,Fullouterjoin.
1.EquiJoins:They are also called innerjoins,In this only 
matched rows are displayed to the user.
Example:
select eno,ename,sal,e.deptno,d.deptno,dname,loc from emp e 
innerjoin dept d on e.deptno=d.deptno
2.Selfjoin:if the table is joined with table itself.
select distinct (e1.empno),e1.ename,e1.sal from emp e1 join 
emp e2 on e1.sal=2* e2.sal(It dispalys emp deatails salary 
exactly 2 times with any other emp salary)

Already i explained 3 joins now i tell u Outerjoins.
leftouterjoin:It will display all the rows of left table 
irrespective of whether there is a match in the right or 
not.If there is no match in the right table then the null 
row is aasumed and it is displayed in the output.

Display all emps in the emptable.
select empno,ename,esal,e.deptno,d.deptno,dname,loc from 
emp e left outerjoin dept d on e.deptno=d.deptno

RightOuterJoin:It will display all the rows of right table 
irrespective of whether there is a match in the right or 
not.If there is no match in the Left table then the null 
row is aasumed and it is displayed in the output

Example
select empno,ename,esal,e.deptno,d.deptno,dname,loc from 
emp e right outerjoin dept d on e.deptno=d.deptno

There are six type of join in SQL 2000

1) INNER   JOIN
2) OUTER   JOIN
3) CROSS   JOIN
4) EQUI    JOIN
5) NATURAL JOIN
6) SELF    JOIN


1) INNER JOIN :- PRODUCESS THE RESULT SET OF MATCHING ROWS
                 ONLY FROM THE SPECIFIED TABLES.

EXAMPLE---

SELECT COLUMN_LIST FROM 1ST_TABLE_NAME JOIN 2ND_TABLE_NAME
ON 
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN

2) OUTER JOIN :- DISPLAY ALL THE ROWS FROM THE FIRST TABLE
                 AND MATCHING ROWS FROM THE SECOND TABLE.

EXAMPLE---

SELECT COLUMN_LIST FROM 1ST_TABLE_NAME OUTER JOIN 
2ND_TABLE_NAME
ON 
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN 
  
THERE ARE THREE TYPES OF OUTER JOIN:

A)LEFT  OUTER JOIN.
B)RIGHT OUTER JOIN.
C)FULL  OUTER JOIN

A)LFET OUTER JOIN :- DISPLAYS ALL THE ROWS FROM THE FIRST   
                     TABLE AND MATCHING ROWS FROM THE
                     SECOND TABLE.

EXAMPLE---

SELECT COLUMN_LIST FROM 1ST_TABLE_NAME LEFT OUTER JOIN 
2ND_TABLE_NAME      ON 
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN

A)RIGHT OUTER JOIN :- DISPLAYS ALL THE ROWS FROM THE  
                      SECOND TABLE AND MATCHING ROWS FROM   
                      THE FIRST TABLE.

EXAMPLE---

SELECT COLUMN_LIST FROM 1ST_TABLE_NAME RIGHT OUTER JOIN 
2ND_TABLE_NAME        ON 
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN

A)FULL OUTER JOIN :- DISPLAYS ALL MATCHING AND NONMATCHING
                     ROWS  OF BOTH THE TABLES.

EXAMPLE---

SELECT COLUMN_LIST  FROM 1ST_TABLE_NAME FULL OUTER JOIN 
2ND_TABLE_NAME     ON 
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN

3)CROSS JOIN :- IN THIS TYPE OF JOIN, EACH ROWS FROM THE   
                JOIN WITH EACH ROWS FROM THE SECOND TABLE
                WITHOUT ANY CONDTION.
                ALSO CALLED AS CARTESIAN PRODUCT.

EXAMPLE---

SELECT COLUMN_LIST FROM 1ST_TABLE_NAME CROSS JOIN 
2ND_TABLE_NAME


4) EQUI JOIN :- DISPLAYS ALL THE MATHCING ROWS FROM JOINED  
                TABLE. AND ALSO DISPLAYS REDUNDANT VALUES.
                IN THIS WE USE * SIGN TO JOIN THE TABLE.

EXAMPLE---

SELECT * FROM 1ST_TABLE_NAME JOIN 2ND_TABLE_NAME
ON 
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN 
 
5)NATURAL JOIN :- DISPLAYS ALL THE MATHCING ROWS FROM   
                    JOINED  TABLE.IT RESTRICT            
                    REDUNDANT VALUES.

6)SELF JOIN :- IN THIS TABLE JOIN WITH ITSELF WITH  
               DIFFERENT ALIAS NAME. 

ASSUME DEPARTMENT IS A TABLE:

SELECT A.DEP_NAME,B.MANAGER_ID(COLUMN LIST) FROM DEPARTMENT 
A JOIN 
DEPARTMENT B
ON A.MANAGER_ID=B.MANAGER_ID

http://en.wikipedia.org/wiki/Join_(SQL)