JOIN Operation 04
EQUI-JOIN
조건에 동등비교 연산자(=) 가 사용되는 JOIN 문장
1. SMITH의 이름과 부서명을 출력한다.
SQL> select ename, dname from emp, dept where ename='SMITH';
---------- --------------
SMITH ACCOUNTING
SMITH RESEARCH
SMITH SALES
SMITH OPERATIONS
SQL> select ename, dname from emp, dept where ename='SMITH' and deptno=20;
select ename, dname from emp, dept where ename='SMITH' and deptno=20
*
1행에 오류:
ORA-00918: 열의 정의가 애매합니다
SQL> select ename, dname from emp, dept where emp.ename='SMITH' and dept.deptno=20;
---------- --------------
SMITH RESEARCH
SQL> select ename, dname from emp JOIN dept ON emp.ename='SMITH' and dept.deptno=20;
---------- --------------
SMITH RESEARCH
INNER JOIN
ON 조건에 해당하는 레코드만 결과집합에 포함한다
묵시적(Implicit) INNER JOIN
SQL> select ename, dname from emp e, dept d where e.ename='SMITH' and d.deptno=20;
---------- --------------
SMITH RESEARCH
명시적(Explicit) INNER JOIN
SQL> select ename, dname from emp e JOIN dept d ON e.ename='SMITH' and d.deptno=20;
---------- --------------
SMITH RESEARCH
명시적(Explicit) INNER JOIN
SQL> select ename, dname from emp e INNER JOIN dept d ON e.ename='SMITH' and d.deptno=20;
---------- --------------
SMITH RESEARCH
2. SMITH와 같은 부서에 근무하는 모든 사원의 이름을 출력한다.
SQL> select ename from emp where deptno=(select deptno from emp where ename='SMITH');
ENAME
----------
SMITH
JONES
SCOTT
ADAMS
FORD
위의 문장은 다음과 같은 EQUI-JOIN 문장을 이용해도 동일한 결과를 가져온다.
SQL> select e2.ename from emp e1 JOIN emp e2 ON e1.ename='SMITH' and e1.deptno=e2.deptno;
----------
SMITH
JONES
SCOTT
ADAMS
FORD
SELF JOIN
3. 각 사원의 이름과 매니저 이름을 출력한다. (SELF JOIN)
SQL> select e.ename 사원, e2.ename as 매니저 from emp e, emp e2 where e.mgr=e2.empno;
사원 매니저
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
사원 매니저
---------- ----------
JONES KING
SMITH FORD
3개의 테이블(EMP, DEPT, SALGRADE)을 연결하여 각 사원의 이름, 급여(sal), 급여수준에 따른 급수, 부서명을 출력한다
SQL> select ename, sal, grade, dname from emp e, salgrade s, dept d
where (e.sal between s.losal and s.hisal) and e.deptno=d.deptno;
ENAME SAL GRADE DNAME
---------- ---------- ---------- --------------
KING 5000 5 ACCOUNTING
SCOTT 3000 4 RESEARCH
FORD 3000 4 RESEARCH
JONES 2975 4 RESEARCH
BLAKE 2850 4 SALES
CLARK 2450 4 ACCOUNTING
ALLEN 1600 3 SALES
TURNER 1500 3 SALES
MILLER 1300 2 ACCOUNTING
WARD 1250 2 SALES
MARTIN 1250 2 SALES
ENAME SAL GRADE DNAME
---------- ---------- ---------- --------------
ADAMS 1100 1 RESEARCH
JAMES 950 1 SALES
SMITH 800 1 RESEARCH
NATURAL JOIN
SQL> select * from emp NATURAL JOIN dept;
OUTER JOIN
SQL> select ename, sal, grade from emp e, salgrade s where e.sal >= 3000 and
(e.sal between s.losal and s.hisal);
---------- ---------- ----------
SCOTT 3000 4
FORD 3000 4
KING 5000 5
ON 조건에서 벗어났지만(outer) emp 테이블(left 테이블)의 정보만은 출력에 포함시키고자 한다면......
SQL> select ename, sal, grade from emp e LEFT OUTER JOIN salgrade s ON e.sal >= 3000 and e.sal betwe
en s.losal and s.hisal;
ENAME SAL GRADE
---------- ---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000 4
KING 5000 5
TURNER 1500
ADAMS 1100
ENAME SAL GRADE
---------- ---------- ----------
JAMES 950
FORD 3000 4
MILLER 1300
ON 조건으로부터 벗어나긴 했지만 우측 테이블의 모든 정보는 출력에 포함시키고자 한다면.....
SQL> select ename, sal, grade from salgrade s RIGHT OUTER JOIN emp e ON
e.sal >= 3000 and e.sal between s.losal and s.hisal;
ENAME SAL GRADE
---------- ---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000 4
KING 5000 5
TURNER 1500
ADAMS 1100
ENAME SAL GRADE
---------- ---------- ----------
JAMES 950
FORD 3000 4
MILLER 1300
OUTER JOIN 연습문제>
다음문장을 실행하면 매니저가 할당되지 않은 KING은 결과집합에서 제외된다.
select e.ename 사원, e2.ename as 매니저 from emp e, emp e2 where e.mgr=e2.empno;
매니저를 할당받지 못한 사원도 그 이름만큼은 출력되도록 해야 한다면......
SQL> select e.ename 사원, e2.ename 매니저 from emp e LEFT OUTER JOIN emp e2 ON e.mgr=e2.empno;
사원 매니저
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
사원 매니저
---------- ----------
JONES KING
SMITH FORD
KING
RIGHT OUTER JOIN 예제
SQL> select e1.comm, e2.ename from emp e1 RIGHT OUTER JOIN emp e2 ON
e1.comm > 0 and e1.comm=e2.comm;
COMM ENAME
---------- ----------
300 ALLEN
500 WARD
1400 MARTIN
MILLER
FORD
JAMES
ADAMS
KING
SCOTT
CLARK
BLAKE
COMM ENAME
---------- ----------
JONES
SMITH
TURNER
SQL> select * from t1;
ID
----------
1
2
3
4
SQL> select * from t2;
ID
----------
3
4
5
6
-- LEFT OUTER JOIN
SQL> select t1.id, t2.id
2 from t1 LEFT OUTER JOIN t2 ON (t1.id = t2.id);
ID ID
---------- ----------
3 3
4 4
1
2
-- RIGHT OUTER JOIN
SQL> select t1.id, t2.id
2 from t1 RIGHT OUTER JOIN t2 ON (t1.id = t2.id);
ID ID
---------- ----------
3 3
4 4
6
5
-- LEFT JOIN (SAME AS LEFT OUTER JOIN)
SQL> ed
Wrote file afiedt.buf
1 select t1.id, t2.id
2* from t1 LEFT JOIN t2 ON (t1.id = t2.id)
SQL> /
ID ID
---------- ----------
3 3
4 4
1
2
-- RIGHT JOIN (SAME AS RIGHT OUTER JOIN)
SQL> ed
Wrote file afiedt.buf
1 select t1.id, t2.id
2* from t1 RIGHT JOIN t2 ON (t1.id = t2.id)
SQL> /
ID ID
---------- ----------
3 3
4 4
6
5
-- INNER JOIN (REGULAR JOIN)
SQL> ed
Wrote file afiedt.buf
1 select t1.id, t2.id
2* from t1 INNER JOIN t2 ON (t1.id = t2.id)
SQL> /
ID ID
---------- ----------
3 3
4 4
-- FULL OUTER JOIN
SQL> ed
Wrote file afiedt.buf
1 select t1.id, t2.id
2* from t1 FULL OUTER JOIN t2 ON (t1.id = t2.id)
SQL> /
ID ID
---------- ----------
3 3
4 4
1
2
6
5
6 rows selected.
SQL>