GeekInterview.com
Series: Subject: Topic:
Question: 19 of 133

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

Asked by: Interview Candidate | Asked on: Oct 5th, 2006
Showing Answers 1 - 9 of 9 Answers
kamal

Answered On : Oct 9th, 2006

There are 4 types of joins

equi join

non equi join

outer join

self join

 

Yes  1 User has rated as useful.
  
Login to rate this answer.
deepak Bhardwaj

Answered On : 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

Yes  1 User has rated as useful.
  
Login to rate this answer.
Srikanth

Answered On : Oct 19th, 2006

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

  
Login to rate this answer.
sujana

Answered On : Nov 8th, 2006

equi joins

non equi joins

outer joins

self joins

vertical joins

  
Login to rate this answer.
Debi Prasad Maharana,Wipro Technologies.

Answered On : 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

  
Login to rate this answer.
singh.mandeep

Answered On : Jun 24th, 2007

View all answers by singh.mandeep

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

Yes  1 User has rated as useful.
  
Login to rate this answer.
nalls_indian

Answered On : May 13th, 2008

View all answers by nalls_indian

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

  
Login to rate this answer.
mans_1201

Answered On : Jun 7th, 2008

View all answers by mans_1201

There are four Type of joins in oracle

1. Inner join
2.cartision join
3.self join
4. outer join

  
Login to rate this answer.


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;

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.