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
Explain the use of rows option in imp command.
What are the different editions of oracle?
How to export data to a csv file?
How to do clean up if create database failed?
What is a cursor variable?
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.
What are the most common interview questions on ETL Testing for experience?
How to start your 10g xe server?
What is a read write transaction in oracle?
What is an anonymous block?
What privilege is needed for a user to insert rows to tables in another schema?
What is columnar storage what is the advantage?
What is the exact use of Collections?
Name the various constraints used in oracle?
hey friends, What are the steps I can do to transfer the database from Microsoft Access 2003 to oracle10g (or SQL) Best regards