What are joins..how many types of joins are there?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
How does the on-delete-cascade statement work?
What is oracle thin client?
what diffrence between function and procedure?
What does `(+)` do in a where clause?
How to execute the package in oracle?
What is a server parameter file in oracle?
How to find 1st, 2nd, 3rd highest using select select statement not using rownum
Can Multiple instances be run on Single Machine???
i want department wise maxmum salary and empolyee name
What is data file?
Give the Types of modules in a form?
Explain oracle insert into command?