How many types of joins are there in oracle and what are the difference between them

Showing Answers 1 - 9 of 9 Answers

kamal

  • Oct 9th, 2006
 

There are 4 types of joins

equi join

non equi join

outer join

self join

 

deepak Bhardwaj

  • Oct 11th, 2006
 

In Oracle 9i there are two types of oracle join syntax

1.Oracle9i join syntax

2.SQL1999 join syntax

1.Oracle 9i join syntax have four type of joining

a Equi join

b.Non-equi Join

c.Self Join

d. Outer join

e.Curtision products

2.Sql 1999 join syntax have following join types

Natural Join

Cross-join

Using-on

using-with

inner join

Outer join

there is no functionality difference between Oracle9i join syntax and SQL1999 join syntax.They have only syntax difference

Srikanth

  • Oct 19th, 2006
 

HI, Diffferent types of join,Natural Joinequi joinNonequi joinCross-joininner joinOuter joinLeft joinright Join

  Was this answer useful?  Yes

sujana

  • Nov 8th, 2006
 

equi joins

non equi joins

outer joins

self joins

vertical joins

  Was this answer useful?  Yes

Debi Prasad Maharana,Wipro Technologies.

  • Nov 20th, 2006
 

Different type of joinEqui join - when you have a common attribute between two tables.Non Equi join - when you have a common relationship between two tables.Self join - when you want to join the table with itself.Equi join is again have different type(1) inner join(2) outer joinAll the join can be written in two standard format(1) in oracle standard format(2) in ANSI standard formatin ANSI standard format (keywords)(1) natural [inner] join(2)inner join...using(3)inner join...on(4)left outer join(5)right outer join

  Was this answer useful?  Yes

Below listed types of Joins are present in Oracle (8i and prior)

1. Equi
Joins

2. Non-Equi Joins
3. Outer Joins
4. Inner Joins

SQL :1999 Compliant joins are

1.
Cross joins
2. Natural joins
3. Using clause
4. Full or two sided outer joins

5. Arbitrary join conditions for outer joins


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 in where clause as well as table alias.

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

(5) 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


(6) 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;

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions