Joins

Types of Joins :

EQUIJOINS or INNERJOIN
NATURAL JOINS
NON-EQUIJOINS
OUTER JOINS
SELF JOINS


EQUIJOINS or INNERJOIN :
The EQUIJOIN joins two tables with a common column in which each is usually the primary key.

The syntax for an EQUIJOIN is

SELECT TABLE1.COLUMN1, TABLE2.COLUMN2...FROM TABLE1, TABLE2 [, TABLE3 ]WHERE TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME[ AND TABLE1.COLUMN_NAME = TABLE3.COLUMN_NAME ]

NATURAL JOINS :

A NATURAL JOIN is nearly the same as the EQUIJOIN; however, the NATURAL JOIN differs from the EQUIJOIN by eliminating duplicate columns in the joining columns. The JOIN condition is the same, but the columns selected differ.

The syntax is as follows:

SELECT TABLE1.*, TABLE2.COLUMN_NAME [ TABLE3.COLUMN_NAME ]FROM TABLE1, TABLE2 [ TABLE3 ]WHERE TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME[ AND TABLE1.COLUMN_NAME = TABLE3.COLUMN ]

NON-EQUIJOINS :

NON-EQUIJOIN joins two or more tables based on a specified column value not equaling a specified column value in another table.

The syntax for the NON-EQUIJOIN is

SELECT TABLE1.*,TABLE2.COLUMN_NAME FROM TABLE1, TABLE2 [, TABLE3 ]WHERE TABLE1.COLUMN_NAME != TABLE2.COLUMN_NAME[ AND TABLE1.COLUMN_NAME != TABLE2.COLUMN_NAME ]

OUTER JOINS :

An OUTER JOIN is used to return all rows that exist in one table, even though corresponding rows do not exist in the joined table.In many implementations, the OUTER JOIN is broken down into joins called

LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

LEFT OUTER JOIN

It returns all rows that exist in the table that is to the left of the join, even though corresponding rows do not exist in the joined table that is to the right of the join.

RIGHT OUTER JOIN

It returns all rows that exist in the table that is to the right of the join, even though corresponding rows do not exist in the joined table that is to the left of the join.

FULL OUTER JOIN

It returns only the rows that has the corresponding match in both tables.

SELF JOINS

The SELF JOIN is used to join a table to itself, as if the table were two tables, temporarily renaming at least one table in the SQL statement. Self joins are useful when all of the data you want to retrieve resides in one table, but you must somehow compare records in the table to other records in the table.

The syntax is as follows:

SELECT A.LAST_NAME, B.LAST_NAME, A.FIRST_NAMEFROM EMPLOYEE_TBL A, EMPLOYEE_TBL BWHERE A.LAST_NAME = B.LAST_NAME;

To understand with examples Click Here