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 |
If I have a select statment which retrives 2 rows, & that rows have the same data in all the fields except the last field and I want to merge the 2 rows to be in 1 row with concatenating the last field which have the different data.... eg: the 1st row has these fields: A-B-C the second row has: A-B-X ........ i want to merge the two row to be in one row like ----> A- B- C,X
how to get the second max val for every group in a table
We need to compare two successive records of a table based on a field. For example, if the table is CUSTOMER, and the filed is Account_ID, To compare Account_IDs of record1 and record2 of CUSTOMER table, what can be the query ?
What is literal?
what is load balancing and what u have used to do this?(sql loader)
Explain the features of oracle?
What is the usage of synonyms?
How to enter a new row into a table interactively?
What is the difference between normal and corelated subqueries?
what is exact difference between drop and truncate table.
How to retrieve 5th highest sal from emp table?
How to drop an existing view in oracle?