JOINS ORACLE

SQL> desc emp
Name Null? Type
----------------------------------------------------- -------- -----------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)




SQL> desc dept
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL> desc salgrade
Name Null? Type
----------------------------------------------------- -------- --------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER



equijoin

select e.empno,d.dname
from emp e,dept d
where e.deptno=d.deptno;


non-equijoin

select e.ename,e.sal,j.grade
from emp e,salgrade j
where e.sal between j.losal and j.hisal;

outer joins

select e.empno,d.dname
from emp e,dept d
where e.deptno(+)=d.deptno;

select e.empno,d.dname
from emp e,dept d
where e.deptno=d.deptno(+);

selfjoin

select worker.ename||' '||'works for'||' '||manager.ename
from emp worker,emp manager
where worker.empno=manager.mgr;