What are joins..how many types of joins are there?
Answer Posted / 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 |
Post New Answer View All Answers
In not less than 100 words what's the main difference between Rolap and Molap in ORACLE ?
interview questions with answer for cts
How to define a specific record type?
How to run queries on external tables?
What are the advantages of oracle?
What is a static data dictionary in oracle?
What is materialized view in Oracle?
How to convert csv to table in oracle?
please explain.. DB architecture ...
What is sharded cluster?
What is private procedure oracle?
How to write date and time literals in oracle?
What is set operator oracle?
Explain what are the advantages of views?
Using the relations and the rules set out in the notes under each relation, write table create statements for the relations EMPLOYEE, FIRE and DESPATCH. You should aim to provide each constraint with a formal name, for example table_column_pk.