Joins in Oracle(8i & prior):
Equijoin
Non-equijoin
Outer join
Self Join.
We have to use a join to query a data from more than one table.
Note:To join N tables together ,we need a minimum of n-1 join conditions.
Equijoin:
Equijoin retrieves the data those are exact match of the tables queried.
It is also called simple join or inner join.
Retrieving Records with Equijoins:
SELECT e.employee_id,e.last_name,e.department_id,d.department_id
FROM employees e, departments d
Where e.department_id = d.department_id;
Non-Equijoins:
A non-equijoin is a join condition containing something other than an equality operator.
[we need to create a table JOB_GRADES having fields GRA,LOWEST_SAL,HIGHEST_SAL]
e.g
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;
Outer Joins:
We all are known about outer join using (+) operator.
Hence I am not discussing it.Rather I am showing it bit different ways.
LEFT OUTER JOIN:
e.g
SELECT e.last_name,e.department_id,d.department_name
FROM employees e
LEFT OUTER JOIN department d
ON (e.department_id =d.department_id);
This query will retrieves all rows in the EMPLOYEES table, which is the left table even if there is no match in the DEPARTMENTS table.
The same query using (+) operator is as below
SELECT e.last_name,e.department_id,d.department_name
FROM employees e, departments
WHERE e.department_id =d. department_id(+)
The right Outer Join is vice verse. Hence I am not discussing on it.
* Full Outer Join:
Full Outer join is combination of Left Outer join as well as Right outer Join.
e.g
SELECT e.last_name,d.department_name
FROM employees e
FULL OUTER JOIN departments d
on (e.department_id = d.department_id);
The above query retrieves all rows in the EMPLOYEES table ,even if ther is no match in the DEPARTMENTs table as well as retrieves all rows in the DEPARTMENTS table,even if there is no match in the EMPLOYEES table.
Self Join:
Sometimes we need to join a table to itself.
e.g.
Q.find out the managers name of employees
SELECT worker.last_name || ‘works for ’ ||manager.last_name
FROM employees worker,employees manager
WHERE worker.manager_id = manager.employees_id;
o/p
Mourgos works for King
Cross Join:
The CROSS JOIN clause produces the cross-product of two tables.
This is the same as Cartesian product(as an IT people we know what is Cartesian product ery well) between the two tables.
e.g
SELECT last_name ,department_name
FROM employees, departments;
source:Oracle Education
