What is join and how many types of joins?

The purpose of JOIN is to combine data across the tables.

A join is a actually performed by the where clause which combines the specified rows of a tables

if a join involves more than one table then oracle joins first tow tables based on the joins condition and then compares the result with the next table so on.

Types of Joins

1. Inner Join
2. Right outer Join    
3. Left outer Join
4. Full Outer Join       
5. Cross Join

Inner Join:

Equi Join :

Is also called simple or inner join.

An equi join is a join condition that contains equality sign.

Non Equi Join :

It is a join condition that contains something other than the equality operator.

Outer Join:

Outer joins are used to also see rows that do not meet the join condition.

Outer join operator is (+).

The missing rows can be returned if an outer join operator is used in the join condition.

The operator (+) is placed on the side of the join that is deficient in information.

This operator has the effect of creating one or more null rows to each one or more rows from the (join condition) non deficient table can be joined.

Left Outer Join : 

This will display matching records and the records which are in the left hand side table those that are not in the right hand side table

Right Outer join :

This will display the matching records and the records which are in the right hand side table those that are not in the left hand side table.

Full Outer Join :

This will display matching records and not matching records from both the side tables.

Self Join : 

Self join is used to join a table to itself.

Table must have similar column repeated in itself to apply this join 

Cartesian Join : 

Used to retrieve the data from multiple tables with out any condition.

Used to retrieve data analysis reports.

No need to have common column between tables to apply this join.