What are joins..how many types of joins are there?

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


Please Help Members By Posting Answers For Below Questions

Hi Can any one tell me what are the API's used in requisition import.

1454


What is the usage of save points in oracle database?

516


What is an oracle tablespace?

570


What is MTTR advisor in Oracle?

584


Hi this srilatha. I comlpeted my Oracle-hrms. can u provide me interview questions on Core hr, payroll,sshr,OLM etc..

1634






How to create tables for odbc connection testing?

468


How do we get field details of a table?

528


How to divide query output into groups in oracle?

539


how may join possible between (requisition with purchase order)

1680


We are using Oracle apps with XML publisher.In that,we are facing some problems while giving a Footer in RTF Template.While giving a footer in RTF Template it is Visible in all the pages,but after the PDF is getiing generated,the Footer are Visible on alternate pages only (like on first page ,third page) and so on. Please provide the Solution for getting the Footer on all the pages.

4908


What are the differences between blob and clob in oracle?

557


What is oracle used for?

514


What is bulk copy or bcp in oracle?

587


How to create a new user account in oracle?

555


What is sequence?

602