What are joins..how many types of joins are there?
- 29 Answers
- 76686 Views
- Amdocs, CSC, TCS, I also Faced
- E-Mail Answers
Answer Posted / tripti gour
In Oracle we can catogrise joins in following ways -
(1) Equi Join (Query having equal sign for condition)
select empno, ename, dname
from emp join dept
on emp.deptno = dept.deptno;
(2) Natural Join : both joining table must have the same
column name on which we are joining both tables.and no need
to specify the column name
select empno, ename, dname
from emp natural join dept;
(3) Non Equi Join: in both table column value is not
directly equal. so, for joining both table can't use equal
sign. in plce of it use BETWEEN,<=, >= operators.
select ename, sal
from emp join salgrade
on sal between losal and hisal;
(4)Outer Join: to fetch matched or unmatched data from
single or both tables.
- left outer join
- right outer join
- full outer join
(4) self Join or Theta Join : joining one table with itself
as another table.
select e1.empno, e1.ename employee, e2.empno mgr_number,
e2.ename manager
from emp e1 right outer join emp e2
on e1.mgr = e2.empno
(5) Cross Join : when we avoid to specify the joiing
condition means WHERE clause then it's become CROSS JOIN.
select ename, dname
from emp join dept;
always try to avoid CROSS JOIN.
| Is This Answer Correct ? | 75 Yes | 5 No |
Post New Answer View All Answers
How to rollback the current transaction in oracle?
What are the four Oracle system processes that must always be up and running for the database to be useable?
How to work with data objects interactively?
How to delete a column in an existing table?
How to empty your oracle recycle bin?
hello friends Im doing my final year engineering in B.Tech.. one of uncle said he can provide job in his company if im good in database management. but i have only basic knowledge about database, so like to join database management course in good intuition. so friends kindly help me to get good intuition because its my future.
ABOUT IDENTITY?
What is a nvl function? How can it be used?
How to use "out" parameter properly?
what are archived logs?
What are the various oracle database objects?
What is archive log in Oracle?
What are the uses of Database Trigger ?
How do I reset a sequence in oracle?
What are the different types of modules in oracle forms?
