오라클 기초#4 ( JOIN, ROWID, ROWNUM )

[ 조인(**)]

- 하나의 테이블로는 원하는 컬럼정보를 얻어올 수 없을때 관련된 테이블을 논리적으로 결합하여 원하는 컬럼정보를 얻어오는 방법.

- 조인조건 : 논리적으로 결합되는 1개이상의 테이블에는 반드시 '공통컬럼'이 존재해야 하며 이 공통컬럼은 동일한 데이터 타입이어야 한다.

 

 

1) EQUI조인

- 가장 일반적인 조인.WHERE 절에 공통컬럼들이 동등연산자(=)에 의해 비교되는 조인

 

예1)사원번호,이름,부서번호,부서이름,부서위치를 조회하시오.

SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME,D.LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

  EMPNO ENAME       DEPTNO DNAME            LOC
 --------- --------- ---------- -------------- ----------
      7369 SMITH                   20 RESEARCH       DALLAS
      7499 ALLEN                   30 SALES             CHICAGO
      7521 WARD                    30 SALES             CHICAGO
      7566 JONES                   20 RESEARCH      DALLAS
      7654 MARTIN                  30 SALES            CHICAGO
      .....

예2) 30번 부서의 사원들의 사원번호,이름,부서번호,부서명을 출력하시오.

SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME
 FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO=30;

 

 

Q1) 부서명이 'ACCOUNTING'에 근무하는 사원들의 이름,급여,부서명을 출력해 보세요.

SELECT E.ENAME,E.SAL,D.DNAME
  FROM EMP E,DEPT D
 WHERE E.DEPTNO=D.DEPTNO AND D.DNAME='ACCOUNTING';

 

  

2) CROSS JOIN

- 2개이상의 테이블에서 조인될때 공통컬럼에 의한 결합이 발생되지 않는경우  (일반적으로 실수로 조인을 하는 경우가 대부분임)

SELECT E.EMPNO,
             E.ENAME,
             E.DEPTNO,
             D.DNAME,
             D.LOC
   FROM EMP E,DEPT D;
  
    
  EMPNO ENAME  DEPTNO DNAME         LOC
 -------- --------  -------- ------------- ------------
     7369 SMITH              20 ACCOUNTING   NEW YORK
     7499 ALLEN              30 ACCOUNTING   NEW YORK
     7521 WARD               30 ACCOUNTING   NEW YORK
 ....
 48 rows selected.
 

 

3) SELF 조인

-  참조해야 하는 컬럼이 자신의 테이블에 있는 다른 컬럼인 경우 사용되는 조인

예) 각 사원의 사원이름,매니저이름을 출력하시오.

SELECT E1.ENAME "사원이름",E2.ENAME "매니져이름"
   FROM EMP E1,EMP E2
  WHERE E1.MGR=E2.EMPNO
   ORDER BY E1.EMPNO;

 

4) Outer 조인

- 한쪽 테이블에는 해당하는 데이터가 존재하는데 다른쪽 테이블에는 데이터가 존재하지 않는 경우 모든 데이터를 출력하게 하는 조인

 

예) 부서번호,부서명,사원이름을 출력하시오.

SELECT D.DEPTNO,D.DNAME,E.ENAME
   FROM  DEPT D,EMP E
  WHERE D.DEPTNO=E.DEPTNO(+);

 

Q1)EMP테이블과 DEPT테이블을 조인하여 부서번호,부서명,이름,급여 출력하세요.

SELECT D.DEPTNO,D.DNAME,E.ENAME,E.SAL
  FROM EMP E,DEPT D
 WHERE E.DEPTNO=D.DEPTNO;

   

Q2)이름이 'ALLEN'인 사원의 부서명을 출력하세요.

SELECT E.ENAME "사원이름",D.DNAME "부서명"
   FROM EMP E,DEPT D
 WHERE E.DEPTNO=D.DEPTNO AND E.ENAME='ALLEN';

 

Q3)사원의 이름,부서번호,부서명,급여를 출력하시오.
  (단,DEPT테이블의 모든 부서를 출력하세요.OUTER조인)

SELECT E.ENAME,D.DEPTNO,D.DNAME,E.SAL
  FROM EMP E,DEPT D
 WHERE E.DEPTNO(+)=D.DEPTNO;

 

Q4)EMP테이블에 있는 EMPNO와 MGR을 이용해서 아래와 같이 출력되도록 하세요.
       사원번호  사원이름   매니져이름   매니져직업
       .......

SELECT E1.EMPNO,E1.ENAME,E2.ENAME,E2.JOB
  FROM EMP E1,EMP E2
 WHERE E1.MGR=E2.EMPNO
 ORDER BY E1.EMPNO;

 

 

## ANSI조인

 

1) INNER 조인

예1) 사원번호,이름,부서명,부서위치를 조회하시오.

SELECT E.EMPNO,E.ENAME,D.DNAME,D.LOC
FROM EMP E INNER JOIN  DEPT D
    ON E.DEPTNO=D.DEPTNO;

 

예2) 급여가 2000이상인 사원번호,이름,부서명,급여를 조회하시오.

SELECT E.EMPNO,E.ENAME,D.DNAME,E.SAL
   FROM EMP E INNER JOIN DEPT D
           ON E.DEPTNO=D.DEPTNO
 WHERE E.SAL>=2000;

 

Q1) 부서명'ACCOUNTING'에 근무하는 사원들의 이름,급여,부서명 출력하세요.
    (INNER JOIN사용)

SELECT E.ENAME,E.SAL,D.DNAME
  FROM EMP E INNER JOIN DEPT D
           ON E.DEPTNO=D.DEPTNO
 WHERE D.DNAME='ACCOUNTING';

 

 

2) OUTER조인

- RIGHT OUTER JOIN,LEFT OUTER JOIN,FULL OUTER JOIN

 

예1) 부서번호,부서명,근무사원이름,급여 출력

SELECT D.DEPTNO,D.DNAME,E.ENAME,E.SAL
  FROM DEPT D LEFT OUTER JOIN EMP E
           ON D.DEPTNO=E.DEPTNO;

 

 

 

[ ROWID와 ROWNUM ]

 

- 오라클에서 테이블을 생성하면 자동으로 생성되는 컬럼
- ROWID : ROW의 고유번호(중간에 ROW를 삭제해도 변하지 않음)
- ROWNUM(*) : 행의 인덱스(중간에 ROW삭제시 변함)

 

 

SQL> SELECT ROWID,ROWNUM,EMPNO FROM EMP;

 ROWID                  ROWNUM      EMPNO
 ------------------ ---------- ----------
 AAAE+4AABAAAK/hAAA          1       7369
 AAAE+4AABAAAK/hAAB          2       7499
 AAAE+4AABAAAK/hAAC          3       7521
 AAAE+4AABAAAK/hAAD          4       7566
 ...

 

예1) EMP테이블에서 급여가 가장 낮은 사람 10명의 번호,이름,급여를 출력하세요.(서브쿼리==>인라인뷰)

 SELECT EMPNO,ENAME,SAL
  FROM (SELECT *
                FROM EMP
             ORDER BY SAL ASC)
 WHERE ROWNUM<11;

 

Q1) 부서별 월급총액이 가장 많은 순서대로 2위까지 해당하는 부서와 월급총액을 출력하세요.

 SELECT DEPTNO,
              SUMSAL
FROM  ( SELECT DEPTNO,
                          SUM(SAL) "SUMSAL"
                FROM EMP
            GROUP BY DEPTNO
            ORDER BY SUM(SAL) DESC)
 WHERE ROWNUM<3;

Q2) EMP테이블에서 가장 많은 JOB의 이름을 출력하시오.

SELECT * FROM (
      SELECT JOB,COUNT(*) CNT
      FROM EMP
      GROUP BY JOB
      ORDER BY CNT DESC)
  WHERE ROWNUM=1;