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

Answers were Sorted based on User's Feedback



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

Answer / rakesh

Total Joins of 8 types::
table1: emp[ename,city]
table2: emp_sal[ename,salary]

1]self join
select e2.ename from emp e1, emp e2 where e1.city=e2.city
and e1.ename="Ram"

2]Natural Join
select ename,city,salary from emp natural join emp_sal

3]cross join
select ename,city,salary from emp cross join emp_sal

4]left outer join : all rows from left table
select e1.ename,city,salary
from emp e1,emp_sal e2
where (e1.ename=e2.ename(+))

5]right outer join : all rows from right table
select e1.ename,city,salary
from emp e1,emp_sal e2
where (e1.ename(+)=e2.ename)

6]full outer join : all rows from [left table+right table]
select e1.ename,city,salary
from emp e1,emp_sal e2
where (e1.ename=e2.ename(+))
UNION
select e1.ename,city,salary
from emp e1,emp_sal e2
where (e1.ename(+)=e2.ename)
OR CAN SAY:
select ename,city,salary
from emp e1 full outer join emp_sal e2
where e1.ename=e2.ename

7]Equi Join/simple join/inner join
select e1.ename,e2.salary
from emp e1, emp_sal e2
where e1.ename=e2.ename

8]Non Equi Join
select e1.ename,e2.salary
from emp e1, emp_sal e2
where e2.salary BETWEEN 1000 and 2000

Is This Answer Correct ?    11 Yes 3 No

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

Answer / mouli

Joins allows you to read or report the data from more than 1 table. There are four types of joins are there..
1) equi join or inner join or natural join
2) non equi join
3) outer join
a)left outer join
b)right outer join
c)full outer join
4) self joins

Is This Answer Correct ?    3 Yes 0 No

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

Answer / t.murali krishna

Types of joins:
1. Inner join
equi join
non equi join
selfjoin
2.Outer join
left outer
right outer
full outer(from 9i)
3.Natural join(from 9i)
4.cross join

Is This Answer Correct ?    5 Yes 3 No

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

Answer / rahul baheti

Joins means we can join two or more then two table in
single query. and joins is combinatoin of two or more table.
there are Four type of join
1 Equi joins
2 Non Equi joins
3 Outer joins
4 Self joins

Is This Answer Correct ?    4 Yes 2 No

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

Answer / sam s. kolta

Joining Types:
1. Joining according to Condition Existence Join:
a. Cross or Cartesian Join: SELECT * FROM emp
CROSS JOIN dept OR SELECT * FROM emp, dept;
b. Restriction Join: SELECT * FROM emp;
c. Natural Join: SELECT d.dname,
d.loc, e.ename, e.job FROM dept d NATURAL JOIN emp e;
d. Named Column Join: SELECT empno, ename,
dname FROM emp natural join dept;
e. Conditional Join: SELECT e.empno,
e.ename, e.job, d.dname, d.loc FROM emp e, dept d Where
e.deptno = d.deptno;

2. Joining according to Row Selection Join:
a. Inner Join: SELECT d.dname, d.loc,
e.ename, e.job FROM dept d INNER JOIN emp e USING (deptno);
b. Left Outer: SELECT deptno, d.dname,
d.loc, e.ename, e.job FROM dept d LEFT OUTER JOIN emp e
USING (deptno);
c. Right Outer: SELECT deptno, d.dname,
d.loc, e.ename, e.job FROM emp e RIGHT OUTER JOIN dept d
USING (deptno);
d. Full Outer: SELECT d.dname, e.ename
FROM dept d LEFT OUTER JOIN emp e ON d.deptno = e.deptno;

3. Joining according to comparison operator:
a. Equijoin: SELECT e.empno,
e.ename,e.sal,d.deptno,d.dname,d.loc FROM emp e, dept d
WHERE e.deptno = d.deptno;
b. Non Equijoin: SELECT e.empno, e.ename,
e.sal, s.grade FROM emp e, salgrade s WHERE e.sal between
s.losal and s.hisal;
c. Predict Operator: SELECT d.dname, d.loc,
e.ename, e.job FROM dept d LEFT OUTER JOIN emp e ON
(d.deptno = e.deptno); WHERE
d.loc = 'DALLAS';

4. Joining according to used table (Self Join):
a. Inner Join: SELECT b. ename, a.
mgr from emp a left outer join emp b on (a.empno = b.mgr);
b. Outer Join: SELECT b. ename, a.
mgr from emp a right outer join emp b on (a.empno = b.mgr);

5. Joining according to ANTI or SEMI JOIN:
a. ANTI JOIN: SELECT D.deptno, D.dname
FROM dept D, emp E WHERE E.deptno = D.deptno ORDER BY
D.deptno;
b. SEMI JOIN: SELECT D.deptno, D.dname
FROM dept D WHERE EXISTS (SELECT 1 FROM emp E WHERE
E.deptno = D.deptno) ORDER BY D.deptno;
________________________________________

Is This Answer Correct ?    3 Yes 1 No

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

Answer / rama mohan reddy

ACTUALLY PRIOR 8i THERE ARE 4 TYPES OF JOINS ARE THERE THEY ARE
1. EQUI JOIN
2. NON EQUIJOIN
3. SELF JOIN
4. OUTER JOIN
A) LEFT OUTER JOIN
B) RIHT OUTER JOIN

BUT ANSI PROIDES NEW JOINS FROM 1999 THEY ARE
1. CROSS JOIN
2. NATURAL JOIN
3. USING CLAUSE
4. INNER JOIN //SAME AS EQUI JOIN PRIOR 1999
5. SELF JOIN //SAME AS SELF JOIN PRIOR 1999
6. RIHT OUTER JOIN
7. LEFT OUTER JOIN
8.FULL OUTER JOIN

Is This Answer Correct ?    2 Yes 0 No

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

Answer / john

Depending on condition existence
----CROSS JOIN (synonyms also CARTESIAN JOIN, CARTESIAN PRODUCT)
----NATURAL JOIN
----# NAMED COLUMNS JOIN - syntactic notation joining source
tables on user defined columns having the same name. This is
less dangerous than Natural join and just short form of
writing Equi joins on some common columns joined together.
Named columns joins always are Equi joins.
----# CONDITIONAL JOIN - fully controllable syntax by user.
This is the most widespread and most useful syntactic
convention. Depending on used predicates in join condition
it may be Equi join as well as Non-equi join.

Depending on row selection
INNER JOIN
OUTER JOIN--- left,right,full

Depending on comparison operator
equi Non-equi

Depending on used tables
SELF JOIN

Is This Answer Correct ?    3 Yes 2 No

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

Answer / khaleek

There are three types of joins Equi Join and Non-Equi Join
and Cross Product.
Further Equi join can be divided into following categories...

1) Outer Join
2) Inner Join
3) Self Join

Again Outer join can be divided into following categories..
1) Right Outer Join
2) Left Outer Join
3) Full Outer Join


You can see the detail at..
http://interview-preparation-for-you.blogspot.com/2010/11/difference-between-inner-joinouter-join.html

Is This Answer Correct ?    1 Yes 0 No

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

Answer / reshma patil

when you want to retrive data from two or more tables then
you require joins.

Types of joins:-
1)Cross join/Cartain join
2)Equi join/inner join
3)Outer join
4)Self join
5)Non-equi join

Is This Answer Correct ?    2 Yes 1 No

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

Answer / srikanth keshavena

Basically join's are three type's those are :

1.Cross join
2.Equi join
a.Natural join
b.inner join
c.outer join(Simple join)
i.Left outer join
ii.Right outer join
iii.FUll outer join
3.Non-equi join

Is This Answer Correct ?    1 Yes 0 No

Post New Answer

More Oracle General Interview Questions

How does the on-delete-cascade statement work?

0 Answers  


What is oracle thin client?

0 Answers  


what diffrence between function and procedure?

4 Answers  


What does `(+)` do in a where clause?

1 Answers  


How to execute the package in oracle?

0 Answers  






What is a server parameter file in oracle?

0 Answers  


How to find 1st, 2nd, 3rd highest using select select statement not using rownum

2 Answers  


Can Multiple instances be run on Single Machine???

3 Answers  


i want department wise maxmum salary and empolyee name

9 Answers  


What is data file?

0 Answers  


Give the Types of modules in a form?

1 Answers  


Explain oracle insert into command?

0 Answers  


Categories
  • Oracle General Interview Questions Oracle General (1789)
  • Oracle DBA (Database Administration) Interview Questions Oracle DBA (Database Administration) (261)
  • Oracle Call Interface (OCI) Interview Questions Oracle Call Interface (OCI) (10)
  • Oracle Architecture Interview Questions Oracle Architecture (90)
  • Oracle Security Interview Questions Oracle Security (38)
  • Oracle Forms Reports Interview Questions Oracle Forms Reports (510)
  • Oracle Data Integrator (ODI) Interview Questions Oracle Data Integrator (ODI) (120)
  • Oracle ETL Interview Questions Oracle ETL (15)
  • Oracle RAC Interview Questions Oracle RAC (93)
  • Oracle D2K Interview Questions Oracle D2K (72)
  • Oracle AllOther Interview Questions Oracle AllOther (241)