---

INNER JOIN

An INNER JOIN returns all rows from both the participating tables where the key record of one table is equal to the key records of another table. This type of join required a comparison operator to match rows from the participating tables based on a common field or column of both the tables.

OUTER JOIN

The OUTER JOIN returns all rows from the participating tables which satisfy the condition and also those rows which do not match the condition will appear in this operation. This result set can appear in three types of format –

The first one is LEFT OUTER JOIN, in this join includes all the rows from a left table of JOIN clause and the unmatched rows from a right table with NULL values for selected columns.

The second one is RIGHT OUTER JOIN, in this join includes all rows from the right of JOIN cause and the unmatched rows from the left table with NULL values for selected columns.

The last one in FULL OUTER JOIN, in this join, includes the matching rows from the left and right tables of JOIN clause and the unmatched rows from left and right table with NULL values for selected columns.

---

Example:

Here is two table tableX and tableY and they have no duplicate rows in each. In tableX the values ( A,B) are unique and in tableY the values (E,F) are unique, but the values (C and D) are common in both the tables.

Here is INNER JOIN

SELECT * 
FROM tableX 
INNER JOIN tableY on tableX.X = tableY.Y;

or

 
SELECT tableX.*,tableY.*  
FROM tableX,tableY 
WHERE tableX.X = tableY.Y; 

Output:

Here only the matching of both tableX and tableY have appeared in the result set.

LEFT OUTER JOIN

 
SELECT tableX.*,tableY.*  
FROM tableX,tableY 
WHERE tableX.X = tableY.Y(+)

or

 
SELECT * 
FROM tableX 
LEFT OUTER JOIN tableY ON tableX.X= tableY.Y 

Output:

Here all the rows from tableX that is left side of JOIN clause and all the rows with NULL values for unmatched columns from tableY that is the right side of JOIN clause have appeared.

RIGHT OUTER JOIN

 
SELECT * FROM tableX 
RIGHT OUTER JOIN tableY ON tableX.X= tableY.Y 

Output:

Here all the rows from tableY that is the right side of JOIN clause and all the rows with NULL values for unmatched columns from tableX that is left side of JOIN clause have appeared.

FULL OUTER JOIN

 
SELECT * 
FROM tableX 
FULL OUTER JOIN tableY ON tableX.X= tableY.Y

Output:

Here all the matching rows from tableX and tableY and all the unmatched rows with NULL values for both the tables have appeared.