Joins in SQL

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

Reference:
http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/