Golgappa.net | Golgappa.org | BagIndia.net | BodyIndia.Com | CabIndia.net | CarsBikes.net | CarsBikes.org | CashIndia.net | ConsumerIndia.net | CookingIndia.net | DataIndia.net | DealIndia.net | EmailIndia.net | FirstTablet.com | FirstTourist.com | ForsaleIndia.net | IndiaBody.Com | IndiaCab.net | IndiaCash.net | IndiaModel.net | KidForum.net | OfficeIndia.net | PaysIndia.com | RestaurantIndia.net | RestaurantsIndia.net | SaleForum.net | SellForum.net | SoldIndia.com | StarIndia.net | TomatoCab.com | TomatoCabs.com | TownIndia.com
Interested to Buy Any Domain ? << Click Here >> for more details...

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


Please Help Members By Posting Answers For Below Questions

How do I limit the number of oracle database connections generated by weblogic server?

1070


Tab A A B ------ 1 A 2 B 3 C Tab B A B ----- 4 D 5 E 6 F Generate the value into B table from A table. Only table A has the value. Write the SQL query to get B table value.

1782


Please explain me all types of Data models. Also give me the details if each model can have other name.for example:schematic data model is also known as conceptual data model and entity relation data model.

2310


What are the tools for Oracle ?

1062


what is normalisation?what are its uses?

2273


What is the difference between online and offline backups?

1057


How to display employee records who gets more salary than the average salary in the department?

1065


Explain overloading. Can functions be overloaded?

986


How to use select statement to count the number of rows in oracle?

1042


How to update values in a table in oracle?

1026


What language does oracle use?

1098


What does sharding mean?

1000


Can we write dml statement in function in oracle?

1081


Explain do view contain data?

1008


What is the disadvantage of User defind function?

2290