Joins

Joins: When We need to combine two or more tables from database then we use Joins.

Step 1: Here we are using Oracle 10g to execute oracle and sql commands. so run 'sql command line'
connect to hr schema.

Step 2: Two tables named Employees and Departments are already present in this schema and we are performing join over these two tables.
Employees Table:

Departments Table:

Following are the commands for Different Joins:
Cartesian Product:(for Oracle)

Cross Join:(for sql/oracle) :
 It is a join which does not include Where Clause and the result of this join is number of rows in First Table multiply by number of rows in Second Table.

Output of Cartesian Product and Cross join are same

Natural Join (Inner Join):
 It is a Join where exists at-least one column name in both the Tables and that get Implicitly Compared and if you are selecting that common column then there is no need to mention from which Table you are going to select that column(DEPARTMENT_ID is common column here)
but in the case of on clause we need to mention it(see Query E.DEPARTMENT_ID where E is Alias of table Employees)

Natural Join with Using Clause:

Natural Join with on Clause:

Equi Join:(For Oracle)
It is a Join Where comparison takes place Explicitly. E and D are Alias for Tables Employees and Departments respectively.
Example 1.

Example 2.

Outer Join: SQL
Left Outer Join:
Returns All Rows from Left Table whether corresponding values are available or not.

Right Outer Join:
Returns All Rows from Right Table whether corresponding values are available or not. 

Full Outer Join:
Returns All Rows from Right Table and Left Table whether corresponding values are available or not.

Outer Join: Oracle
Left Outer Join:

output:

Right Outer Join:

output: