JOINS IN ORACLE

1- The purpose of join is to combine the data across tables.
2-A Join is actually performed by the where clause which combines the specified rows of tables.
3-IF a join involves in more than two tables , 
then oracle joins first two tables based on join condition and then compares the result with the next table 
and so on.

TYPES:
1.EQUI join or  Simple join or Inner Joins  
2.Non-equi join 
3.Self join 
4.Natural join 
5.Cross join 
6.Outer  join 
  a)Left outer
  b)Right outer
  c)Full outer
7. Inner join
8.Using clause
9.On clause

1. Equijoin:

• A join which contains an equal to '=' operator in the join condition.
• It will retrieve the matching rows from both the tables.
• This type of join involves primary and foreign key complements.

SELECT e.employee_id, e.last_name, e.job_id , d.department_name, d.location_id
from employees e , departments d
where e.department_id = d.department_id;


SELECT e.last_name, d.department_name, l.city
from employees e ,   departments d ,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;

--or By mentioning USING clause:

SELECT e.employee_id, e.last_name, e.job_id ,d.department_name, d.location_id
from employees e  join  departments d
USING (department_id);

106 rows selected.

--or By mentioning ON  clause:

SELECT e.employee_id, e.last_name, e.job_id ,d.department_name, d.location_id
from employees e  join  departments d
ON  (e.department_id=d.department_id);

2. Non-equi join  :

•  A join which contains an operator other than equal to '=' in the Join condition.

SELECT e.last_name, e.salary,j.grade_level
FROM  employees e ,job_grades j
WHERE e.salary BETWEEN j.lowest_sal and j.highest_sal;

3.Self join 

•  joining the table to itself is called self join.

SELECT worker.last_name || 'works for '|| manager.last_name 
from employees worker , employees manager
where worker.manager_id= manager.employee_id;

4.Natural join 

• Natural join joins two tables based on the same column name.

select *  from employees
natural join  departments;

--common column is department_id and manager_id.

5.Cross join 

•  This will give the cross product of two tables.

select * from employees cross join departments;

or

select * from employees , departments ; 107*27=2889 rows.

6.Outer  join 

• Outer join gives the non-matching records along with matching records.
• In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

a) Left outer Join:

• It will display all matching records from both the tables along with the records which are in left hand side table those who don't have matching records in the in the right hand side table.
• Note: The LEFT OUTER JOIN keyword returns all records from the left table (employees), even if there are no matches in the right table (Departments).

SELECT  e.last_name,e.department_id,d.department_name
FROM  employees e 
LEFT OUTER JOIN  departments d   -- After 'LEFT OUTER JOIN' or 'RIGHT OUTER JOIN'         .                                                            Keyword we have to mention right side table name with alias

ON (e.department_id= d.department_id) ;

For Employee with Last_Name 'Grant', the department_id is NULL in employees table. Hence in Left Outer Join , it will retrive the Last_Name 'Grant' along with NULL entries from Departments Table. 

Additional Filter condition:

SELECT  e.last_name,e.department_id,d.department_name

FROM  employees e 

LEFT OUTER JOIN  departments d   -- After 'LEFT OUTER JOIN' or 'RIGHT OUTER JOIN'         .                                                            Keyword we have to mention right side table name with alias

ON e.department_id= d.department_id 

WHERE e.SALARY > 1000

and d.department_name ='Purchasing' ;

Joining 3 Tables:

SELECT  e.last_name,e.department_id,d.department_name,l.city

FROM  employees e 

LEFT OUTER JOIN  departments d   -- After 'LEFT OUTER JOIN' or 'RIGHT OUTER JOIN'         .                                                            Keyword we have to mention right side table name with alias

ON e.department_id= d.department_id 

LEFT OUTER JOIN locations l

ON d.location_id = l.location_id 

--WHERE e.SALARY > 1000

--and d.department_name ='Purchasing'

-- Number of Records : 107 ( Full employees table records and matching /unmatching from dept and loc table ) 

OR

SELECT  e.last_name,e.department_id,d.department_name
FROM  employees e , departments d
WHERE  e.department_id =d.department_id (+);

-- --The "(+)" goes on the columns of the table that generates subrows of nulls. 

-- It is advisable to mention (+) sign right side of '=' operator in Left Outer Join Case.

--The '+' sign is in the department table because it is lacking of information in employees table (No Department_id assigned or department_id is NULL for few employees ) for employee GRANT in employees table.

b) Right outer Join :

• It will display all matching records from both the tables along with the records which are in RIGHT HAND SIDE table those who don't have matching records in the in the LEFT HAND SIDE  table.
• Note: The RIGHT OUTER JOIN keyword returns all records from the right table (departments ), even if there are no matches in the left table (employees).

SELECT  e.last_name,e.department_id,d.department_name
FROM  employees e 
RIGHT OUTER JOIN  departments d   -- After 'LEFT OUTER JOIN' or 'RIGHT OUTER JOIN'         .                                                            Keyword we have to mention right side table name with alias

ON (e.department_id= d.department_id) ;

For department names Treasury and Corporate Tax etc from Department table(Right Side Table) , 
there is no entry of department_id  in employees (Left Side Table) table. This means these department_ids ( 110,120,130,140,150,160 ) has not yet assigned to any employee in employees table.  Hence it will retrive all department name from department table even though there is no match present in employees table. 

OR

SELECT  e.last_name,e.department_id,d.department_name
FROM  employees e , departments d
WHERE  e.department_id (+) =d.department_id ;

-- It is advisable to mention (+) sign left side of '=' operator in Right Outer Join Case.

--The "(+)" goes on the columns of the table that generates subrows of nulls. 

c) Full outer join :

• This will display all matching records and non-matching records from both the tables.

SELECT e.last_name,e.department_id,d.department_name
FROM  employees e 
FULL OUTER JOIN  departments d
ON (e.department_id= d.department_id) ;

----or----

SELECT  e.last_name,e.department_id,d.department_name
FROM  employees e , departments d
WHERE  e.department_id =d.department_id (+) 

UNION


SELECT  e.last_name,e.department_id,d.department_name
FROM  employees e , departments d
WHERE  e.department_id (+) =d.department_id ;



7. Inner Join :

• It will retrieve the matching rows from both the tables. 

SELECT e.employee_id, e.last_name, e.job_id ,d.department_name, d.location_id
from employees e  INNER JOIN   departments d
USING (department_id);

An inner join is a join with a join condition that may contain both equality or non-equality sign whereas an equijoin is a join with a join condition that only contain only equality sign. 
So we can say an equijoin is a type of inner join containing (Equal)= operator in the join condition.

It is good to know the difference between join and INNER JOIN keywoed. 
Actually there is no difference. If we write JOIN then by default Inner join is performed. 

SL 8 (Using clause)  and SL  9 (On clause): Already mentioned above.

Cheers
Rajani