JOINS

Joins are used to combine data from two or more tables to form a single result set. A join is actually performed whenever multiple tables appear in the FROM clause. 

Note: If any two of the table participating in joining with same column name, then it is necessary to provide references of these columns throughout the query with table names to avoid ambiguity.

Types Of Joins:

There are 4 major types of join in Oracle:

1. Inner Join
2. Left Outer Join
3. Right Outer Join
4. Full Outer Join

Inner Join:

It is also called Simple Join. It returns all records from multiple tables where the join condition is met. It is the most common type of join.

Syntax:

SELECT column1, column2 ..column x

FROM table1 

INNER JOIN table2

ON table1.column = table2.column;

Old Syntax:

SELECT column1, column2 ..column x

  FROM table1, table2

WHERE table1.column = table2.column;

Example:

Suppose we have a table named "EMPLOYEE" with two fields ID and Name. It contains the following data.

Table Name “EMPLOYEE”

ID	NAME
101	Rajesh
102	Swati
103	Manoj
104	Sachin
105	Swati

We have another table named "SALARY" having two fields ID and SALARY. It contains the following data.

Table Name “SALARY”

ID	SALARY
101	10000
102	8000

Query: 

SELECT ID, NAME, SALARY

   FROM EMPLOYEE

INNER JOIN SALARY

ON EMPLOYEE.ID = SALARY.ID;

               OR

SELECT ID, NAME, SALARY

   FROM EMPLOYEE, SALARY

 WHERE EMPLOYEE.ID = SALARY.ID;

Output of the Query will be:

ID	NAME	SALARY
101	Rajesh	10000
102	Swati	8000

Left Outer Join:

It is also called Left Join. It returns all records from the left table, and the matched records from the right table.

Syntax:

SELECT column1, column2 ..column x

FROM table1 

LEFT OUTER JOIN table2

ON table1.column = table2.column;

Old Syntax:

SELECT column1, column2 ..column x

  FROM table1, table2

WHERE table1.column  = table2.column(+);

Example:

Suppose we have a table named "EMPLOYEE" with two fields ID and Name. It contains the following data.

Table Name “EMPLOYEE”

ID	NAME
101	Rajesh
102	Swati
103	Manoj
104	Sachin

We have another table named "SALARY" having two fields ID and SALARY. It contains the following data.

Table Name “SALARY”

ID	SALARY
101	10000
102	8000

Query: 

SELECT ID, NAME, SALARY

   FROM EMPLOYEE

LEFT OUTER JOIN SALARY

ON EMPLOYEE.ID = SALARY.ID;

               OR

SELECT ID, NAME, SALARY

   FROM EMPLOYEE, SALARY

 WHERE EMPLOYEE.ID = SALARY.ID(+);

Output of the Query will be:

ID	NAME	SALARY
101	Rajesh	10000
102	Swati	8000
103	Manoj	Null
104	Sachin	Null

Right Outer Join:

It is also called Right Join. It returns all records from the Right table, and the matched records from the Left table.

Syntax:

SELECT column1, column2 ..column x

FROM table1 

RIGHT OUTER JOIN table2

ON table1.column = table2.column;

Old Syntax:

SELECT column1, column2 ..column x

  FROM table1, table2

WHERE table1.column(+)  = table2.column;

Example:

Suppose we have a table named "EMPLOYEE" with two fields ID and Name. It contains the following data.

Table Name “EMPLOYEE”

ID	NAME
101	Rajesh
102	Swati
103	Manoj
104	Sachin

We have another table named "SALARY" having two fields ID and SALARY. It contains the following data.

Table Name “SALARY”

ID	SALARY
101	10000
102	8000
105	14000

Query: 

SELECT ID, NAME, SALARY

   FROM EMPLOYEE

RIGHT OUTER JOIN SALARY

ON EMPLOYEE.ID = SALARY.ID;

               OR

SELECT ID, NAME, SALARY

   FROM EMPLOYEE, SALARY

 WHERE EMPLOYEE.ID(+) = SALARY.ID;

Output of the Query will be:

ID	NAME	SALARY
101	Rajesh	10000
102	Swati	8000
105	Null	14000

Full Outer Join:

It is also called Full Join. It returns all records when there is a match in either left or right table.

Syntax:

SELECT column1, column2 ..column x

FROM table1 

FULL OUTER JOIN table2

ON table1.column = table2.column;

Old Syntax:

No Syntax for Full outer join.

Example:

Suppose we have a table named "EMPLOYEE" with two fields ID and Name. It contains the following data.

Table Name “EMPLOYEE”

ID	NAME
101	Rajesh
102	Swati
103	Manoj
104	Sachin

We have another table named "SALARY" having two fields ID and SALARY. It contains the following data.

Table Name “SALARY”

ID	SALARY
101	10000
102	8000
105	14000

Query: 

SELECT ID, NAME, SALARY

   FROM EMPLOYEE

FULL OUTER JOIN SALARY

ON EMPLOYEE.ID = SALARY.ID;

Output of the Query will be:

ID	NAME	SALARY
101	Rajesh	10000
102	Swati	8000
103	Manoj	Null
104	Sachin	Null
105	Null	14000