What are joins..how many types of joins are there?
Answer Posted / 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 |
Post New Answer View All Answers
What are the ansi data types supported in oracle?
Using the relations and the rules set out in the notes under each relation, write statements to create the two sequence generators specified in the notes.
What is the database name in oracle?
Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?
What is pragma autonomous transaction in oracle?
definition of cluster and non-clustered index?
Why do we use bulk collect in oracle?
What is a proxy object?
How to see the table columns used in an index?
What is a nvl function?
How to write numeric literals in oracle?
How to create a table interactively?
Explain parameter file in oracle.
How to create a new table in oracle?
How to use attributes of the implicit cursor in oracle?