join문

* Equi Join

1. select ename, dname

    from emp e, dept d 

    where e.deptno = d.deptno;

2. 이름이 같은 컬럼이 1개 밖에 없을때.

    select ename, dname

    from emp natural join dept;

3. join되는 컬럼의 이름이 같을때.

   select ename, dname

   from emp join dept using(deptno);

4. join되는 컬럼의 이름이 달라도 가능(모든 경우에 사용가능)

   select ename, dname

   from emp e join dept d 

   on e.deptno = d.deptno;

* Outer Joins

Right Join

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

select ename, dname from emp  right (outer) join dept using(deptno); 

Left Join

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

select ename, dname from emp left (outer) join dept using(deptno);

select ename, dname from emp e left (outer) join dept d on e.deptno = d.deptno;

Full Outer Join

select ename, dname from emp e Full (outer) join dept d on e.deptno = d.deptno;

* Cartesian Products : 모든 경우의 수가 다 나오게..

select count(*) from emp,dept;

select last_name, department_name from employees cross join departments;

* 사원의 월급을 연봉에 맞게 측정하기
SELECT ename, sal, s.*
FROM EMP e , SALGRADE s
WHERE e.sal BETWEEN S.LOSAL AND S.HISAL;

SELECT e.ename, d.dname, e.sal,s.*
FROM EMP e JOIN DEPT d ON e.deptno = d.deptno
                    JOIN SALGRADE s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.ename LIKE '%A%';