Let Us Learn Oracle - Part 19 of N [ Joins in Oracle ]
Source table: tblEmployee
Joins in Oracle are similar to Sql Server with some more thing in place. We will look into those shortly
- Cross Join / Cartesian Join
- Natural Join
- Equi Join/ Inner Join
- Using clause
- Left Outer Join
- Right Outer Join
- Full Outer join
- Self Join
1.Cross Join
Produces a cartesian product.It is join of every row of one table to every row of another table.
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
From tblEmployee e, tblDept d;
/* Result */
EmpId EmpName DeptId DeptName
1 Deepak Kumar Goyal 1 IT
1 Deepak Kumar Goyal 1 Accounts
1 Deepak Kumar Goyal 1 Finance
1 Deepak Kumar Goyal 1 Research and Development
1 Deepak Kumar Goyal 1 Sales
2 Shashi Dayal 2 IT
2 Shashi Dayal 2 Accounts
2 Shashi Dayal 2 Finance
2 Shashi Dayal 2 Research and Development
2 Shashi Dayal 2 Sales
3 Amitav Mallik 1 IT
3 Amitav Mallik 1 Accounts
3 Amitav Mallik 1 Finance
3 Amitav Mallik 1 Research and Development
3 Amitav Mallik 1 Sales
4 Amit Ojha 5 IT
4 Amit Ojha 5 Accounts
4 Amit Ojha 5 Finance
4 Amit Ojha 5 Research and Development
4 Amit Ojha 5 Sales
5 Sumanta Manik 3 IT
5 Sumanta Manik 3 Accounts
5 Sumanta Manik 3 Finance
5 Sumanta Manik 3 Research and Development
5 Sumanta Manik 3 Sales
The above statement was used till oracle 8i.From 9i we can use CROSS JOIN inbuilt statement to acheive the same result. Let the loom below
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
From tblEmployee e
Cross Join tblDept d;
2.Natural Join
It filters the record based on the name of the matching column in both tables.Both columns must have identical name in each table for this to work. This feature works even without primary or foreign key referentiual integrity.
Natural Join before Oracle 9i
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
From tblEmployee e,tblDept d
where e.DeptId = d.DeptId;
/* Result */
EmpId EmpName DeptId DeptName
3 Amitav Mallik 1 IT
1 Deepak Kumar Goyal 1 IT
2 Shashi Dayal 2 Accounts
5 Sumanta Manik 3 Finance
4 Amit Ojha 5 Sales
Natural Join from Oracle 9i onwards
The previous approach will work for any Oracle version but from Oracle 9i we can use the Natural Join keyword
Select EmpId,EmpName,DeptId,DeptName
From tblEmployee
Natural Join tblDept;
N.B.~We cannot use any alias in Natural Join
SQL> Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
2 From tblEmployee e
3 Natural Join tblDept d
4 ;
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
*
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
3.Equi Join
It is the inner join between two tables
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
From tblEmployee e
Inner Join
tblDept d
On e.DeptId = d.DeptId;
--OR
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
From tblEmployee e
Join tblDept d
On e.DeptId = d.DeptId;
/*
EmpId EmpName DeptId DeptName
3 Amitav Mallik 1 IT
1 Deepak Kumar Goyal 1 IT
2 Shashi Dayal 2 Accounts
5 Sumanta Manik 3 Finance
4 Amit Ojha 5 Sales
*/
4.Using Clause
We can say that Using Clause is a new way of performing Inner/Equi join in Oracle. We can use it when columns shares the same name.
Henceforth, the above queries for Inner Join can be re-written by using this new clause as under
Select EmpId,EmpName,DeptId,DeptName
From tblEmployee
Join tblDept
Using (DeptId);
N.B.~We cannot use any alias in Using clause
SQL> Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
2 From tblEmployee e
3 Join tblDept d
4 Using (DeptId);
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier
OR
SQL> Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
2 From tblEmployee e
3 Join tblDept d
4 Using (e.DeptID);
Using (e.DeptID)
*
ERROR at line 4:
ORA-01748: only simple column names allowed here
5.Left Outer Join
For the tables X and Y , a left outer join will always contains all records of the "left" table X even if the join-condition does not find any matching record in the "right" table (Y).
Left Outer Join before Oracle 9i - Theta Style
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
From tblEmployee e,tblDept d
Where e.DeptId = d.DeptId(+);
Oracle 9i - ANSI left outer join
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
From tblEmployee e
Left Join tblDept d
On e.DeptId = d.DeptId;
OR
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
From tblEmployee e
Left Outer Join tblDept d
On e.DeptId = d.DeptId;
/* Result
EmpId EmpName DeptId DeptName
3 Amitav Mallik 1 IT
1 Deepak Kumar Goyal 1 IT
2 Shashi Dayal 2 Accounts
5 Sumanta Manik 3 Finance
4 Amit Ojha 5 Sales
*/
6.Right Outer Join
For the tables X and Y , a right outer join will always contains all records of the "right" table Y even if the join-condition does not find any matching record in the "left" table (X).
Right Outer Join before Oracle 9i - Theta Style
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
From tblEmployee e,tblDept d
Where e.DeptId(+) = d.DeptId;
Oracle 9i - ANSI right outer join
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
From tblEmployee e
Right Outer Join tblDept d
On e.DeptId = d.DeptId;
OR
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
From tblEmployee e
Right Join tblDept d
On e.DeptId = d.DeptId;
/* Result
EmpId EmpName DeptId DeptName
1 Deepak Kumar Goyal 1 IT
2 Shashi Dayal 2 Accounts
3 Amitav Mallik 1 IT
4 Amit Ojha 5 Sales
5 Sumanta Manik 3 Finance
Research and Development
*/
7.Full outer join
It combines the effect of applying both left and right joins.
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
From tblEmployee e
Full Join tblDept d
On e.DeptId = d.DeptId;
OR
Select e.EmpId,e.EmpName,e.DeptId,d.DeptName
From tblEmployee e
Full Outer Join tblDept d
On e.DeptId = d.DeptId;
/* Result
EmpId EmpName DeptId DeptName
3 Amitav Mallik 1 IT
1 Deepak Kumar Goyal 1 IT
2 Shashi Dayal 2 Accounts
5 Sumanta Manik 3 Finance
4 Amit Ojha 5 Sales
Null Null Null Research and Development
*/
8.Self Join
It is the case when a table joins to itself.
Select e1.EmpId,e1.EmpName,e2.EmpName
From tblEmployee e1,tblEmployee e2
Where e1.EMPNAME = e2.EMPNAME;
/* Result
EmpId EmpName EmpName_1
1 Deepak Kumar Goyal Deepak Kumar Goyal
2 Shashi Dayal Shashi Dayal
3 Amitav Mallik Amitav Mallik
4 Amit Ojha Amit Ojha
5 Sumanta Manik Sumanta Manik
*/
Hope it is useful.Thanks for reading