Joins

 select * from emp

EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO - Total records 15

--insert into emp(EMPNO, ENAME, JOB, SAL) values(7979, 'KILLER', 'ANALYST', 1200);

--commit;

select * from dept

DEPTNO, DNAME, LOC - Total records 4


/* Cartesian Product */

select * from emp, dept;


/* Equijoins */

select emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO, dept.DNAME, dept.LOC
from emp emp, dept
where emp.DEPTNO = dept.DEPTNO;

/* AND Operator */

select emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO, dept.DNAME, dept.LOC
from emp emp, dept
where emp.DEPTNO = dept.DEPTNO
--AND dept.DEPTNO = 30
AND emp.sal >= 1500;

/* Non-Equijoins */

select emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO, dept.DNAME, dept.LOC
from emp emp, dept
where emp.DEPTNO = dept.DEPTNO
and emp.SAL between 1500 and 5000;


/* LEFT Outer Joins */

select emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO, dept.DEPTNO, dept.DNAME, dept.LOC
from emp emp, dept
where emp.DEPTNO = dept.DEPTNO(+);

/* RIGHT Outer Joins */

select emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO, dept.DEPTNO, dept.DNAME, dept.LOC
from emp emp, dept
where emp.DEPTNO(+) = dept.DEPTNO;


/* Self Joins */

select employee.ENAME || ' works for ' || manager.ENAME "Emp and Manager"
from emp employee, emp manager
where employee.MGR = manager.EMPNO;


/* Cross Joins */

select *
from emp Cross Join dept;


/* Natural Joins */

select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME, LOC
from emp Natural Join dept;

--where emp.DEPTNO = dept.DEPTNO;

/* USING Clause */

select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME, LOC
from emp Join dept
USING(DEPTNO);

/* ON Clause */

select emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO, dept.DEPTNO, dept.DNAME, dept.LOC
from emp emp JOIN dept
ON (emp.DEPTNO = dept.DEPTNO);

/* INNER JOIN */

select emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO, dept.DEPTNO, dept.DNAME, dept.LOC
from emp emp INNER JOIN dept
ON (emp.DEPTNO = dept.DEPTNO);

/* LEFT OUTER JOIN */

select emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO, dept.DEPTNO, dept.DNAME, dept.LOC
from emp emp
LEFT OUTER JOIN dept
ON (emp.DEPTNO = dept.DEPTNO);

select emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO, dept.DEPTNO, dept.DNAME, dept.LOC
from emp emp, dept
where emp.DEPTNO = dept.DEPTNO(+);

/* RIGHT OUTER JOIN */

select emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO, dept.DEPTNO, dept.DNAME, dept.LOC
from emp emp
RIGHT OUTER JOIN dept
ON (emp.DEPTNO = dept.DEPTNO);

select emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO, dept.DEPTNO, dept.DNAME, dept.LOC
from emp emp, dept
where emp.DEPTNO(+) = dept.DEPTNO;

/* FULL OUTER JOIN */

select emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO, dept.DEPTNO, dept.DNAME, dept.LOC
from emp emp
FULL OUTER JOIN dept
ON (emp.DEPTNO = dept.DEPTNO);