[Database/Theory]07. SQL syntax(5) -Join

07. SQL syntax(5) - Join

   하나 이상의 테이블로 부터 연관된 Data 조회

   보통 둘 이상의 Column들의 공통된 값 Primary Key 및 Foreign Key 값을 사용하여 조인 한다.

   4가지로 분류 : EQUI JOIN / NON-EQUI JOIN / SELF JOIN / OUTER JOIN

   이해를 돕기위하여 테이블들을 열거한다.

     c_emp;

     이름                                            널?      유형

      ----------------------------------------- -------- ------------

      ID                                        NOT NULL NUMBER(5)

      NAME                                   NOT NULL CHAR(15)

      SALARY                                                NUMBER(7,2)

      PHONE                                                CHAR(15)

      DEPT_ID                                               NUMBER(7)

     s_dept;

      이름                                      널?      유형

      ----------------------------------------- -------- ------------

      ID                                        NOT NULL NUMBER(7)

      DEPT_NAME                                          VARCHAR2(12)

     sal_grade;

      이름                                      널?      유형

      ----------------------------------------- -------- ---------

      GRADE                                     NOT NULL CHAR(1)

      LOSAL                                              NUMBER(4)

      HISAL                                              NUMBER(4)

   1. EQUI JOIN : 

        서로 관계를 가진 Table들을 이용한 Data 조회.

        PK 와 FK로 관계의 무결성 보장 필요.

        Equi join의 성능을 높이기 위해서는 Index 기능을 사용하는 것이 좋다.

     * Cartesian Product : 테이블 모든 행이 다른 테이블의 모든행을 연결. 쓰레기값.

select
c_emp.name, c_emp.dept_id, s_dept.id, s_dept.dept_name
from c_emp, s_dept;

       NAME                              DEPT_ID         ID DEPT_NAME

       ------------------------------ ---------- ---------- ------------------------

       김사장                                  1          1 총무부

       홍길동                                  1          1 총무부

       홍길순                                  1          1 총무부

       이순신                                  2          1 총무부

       강감찬                                  2          1 총무부

       류시원                                  2          1 총무부

       이문세                                  3          1 총무부

       강호동                                  3          1 총무부

       강문영                                  3          1 총무부

       추현재                                  4          1 총무부

       박상민                                  4          1 총무부

       박상면                                  4          1 총무부

       유재석                                  5          1 총무부

       노무현                                  5          1 총무부

       전두환                                  5          1 총무부

       김일성                                  6          1 총무부

       김정일                                  6          1 총무부

       .....

       => WHERE로 조건 해결

select
c_emp.name, c_emp.dept_id, s_dept.id, s_dept.dept_name
from c_emp, s_dept
where c_emp.dept_id = s_dept.id;

 

     * 실습 예제

       -  김씨성 직원의  이름 / 부서명 /연봉  조회

           ==> 연봉:  \250,000 형식 , 연봉높은순 

select 
c.name 이름, s.dept_name 부서명, to_char(c.salary*12,'L999,999') 연봉
from c_emp c, s_dept s
where c.dept_id=s.id and c.name like '김%';

       이름                           부서명                   연봉

       ------------------------------ ------------------------ ------------------------------------

       김사장                         총무부                            ￦12,000

       김일성                         회계부                            ￦24,000

       김정일                         회계부                            ￦36,000

       -  월급이 2000이상 직원 부서명 / 이름 /일당 조회

             ==> 부서별 :: 오름차순

             ==> 이    름 :: 오름차순

             ==> 일    당 :: 30일기준, 소수점 0자리 버림, \250,000형식, 높은순 

select
s.dept_name 부서명, c.name 이름, to_char(trunc(c.salary/30,0),'L999,999') 일당
from c_emp c, s_dept s
where c.dept_id=s.id and c.salary >= 2000
order by s.id, c.name asc, c.salary desc;

       부서명                   이름                           일당

       ------------------------ ------------------------------ ------------------------------------

       총무부                   홍길동                                      ￦66

       총무부                   홍길순                                     ￦100

       개발부                   강감찬                                     ￦166

       개발부                   이순신                                     ￦133

       인사부                   박상면                                     ￦116

       인사부                   박상민                                      ￦83

       자재부                   노무현                                     ￦183

       자재부                   유재석                                     ￦150

       회계부                   김일성                                      ￦66

       회계부                   김정일                                     ￦100

       

       

       

       

       - 부서별 평균 월급  부서번호/부서명/평균월급 조회

            ==> 부서번호 :: 오름차순

select
s.id 부서번호, s.dept_name 부서명, avg(c.salary) 평균월급
from c_emp c, s_dept s
where c.dept_id=s.id
group by s.id, s.dept_name
order by s.id;

         부서번호 부서명                     평균월급

       ---------- ------------------------ ----------

                1 총무부                         2000

                2 개발부                   3366.66667

                3 영업부                         1300

                4 인사부                         2500

                5 자재부                   3666.66667

                6 회계부                         2500

       

       

       - 총무부 평균 월급  부서번호/부서명/평균월급 조회

       

       =>Where가 효율적

     #1.

select
s.id 부서번호, s.dept_name 부서명, avg(c.salary) 평균월급
from c_emp c, s_dept s
where c.dept_id = s.id and s.dept_name='총무부'
group by s.id, s.dept_name;

       

         부서번호 부서명                     평균월급

       ---------- ------------------------ ----------

                1 총무부                         2000 

     #2. 

select
s.id 부서번호, s.dept_name 부서명, avg(c.salary) 평균월급
from c_emp c, s_dept s
where c.dept_id = s.id
group by s.id, s.dept_name
having s.dept_name='총무부';

       

         부서번호 부서명                     평균월급

       ---------- ------------------------ ----------

         1 총무부                         2000

   2. NON - EQUI JOIN 

           관계가 없는 Table들을 이용한 Data 조회.

           테이블의 어떤 column도 Join할 테이블의 column에 일치하지 않을 때 사용하고, 

           조인조건은 동등( = )이외의 연산자를 갖는다.

           => EQUI JOIN 은 = 연산자를 사용

     => NON-EQUI JOIN 은 =연산자를 제외한 비교 연산자 사용

                (BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN)

     * 예제

select
c.name 이름, c.salary 월급, sg.grade 월급등급
from c_emp c, sal_grade sg
where c.salary between sg.losal and sg.hisal;

       이름                                 월급 월

       ------------------------------ ---------- --

       김사장                               1000 D

       전두환                               1000 D

       류시원                               1100 D

       이문세                               1200 D

       강호동                               1300 D

       강문영                               1400 D

       추현재                               1500 D

       홍길동                               2000 C

       김일성                               2000 C

       박상민                               2500 C

       홍길순                               3000 B

       김정일                               3000 B

       박상면                               3500 B

       이순신                               4000 A

       유재석                               4500 A

       강감찬                               5000 A

       노무현                               5500 A

     * NON - EQUI JOIN & EQUI JOIN 응용 실습

       - 개발부 직원과 영업부직원 이름 , 월급 , 월급등급 , 부서명 조회

           => 등급 높은순  ( NON-EQUI JOIN /  EQUI JOIN 같이 사용)

select
c.name 이름, c.salary 월급, sg.grade 월급등급, s.dept_name 부서명
from c_emp c, sal_grade sg, s_dept s
where s.id=c.dept_id and s.dept_name in ('개발부', '영업부')
      and c.salary between sg.losal and sg.hisal;

       이름                                 월급 월 부서명

       ------------------------------ ---------- -- ------------------------

       류시원                               1100 D  개발부

       이문세                               1200 D  영업부

       강호동                               1300 D  영업부

       강문영                               1400 D  영업부

       이순신                               4000 A  개발부

       강감찬                               5000 A  개발부

       - sal_grade가 A  인원이 2명이상인 부서명 , 인원수 , 등급 

            ( NON-EQUI JOIN /  EQUI JOIN 같이 사용)

select
s.dept_name 부서명, count(c.id) 인원수, sg.grade 등급
from c_emp c, sal_grade sg, s_dept s
where s.id=c.dept_id
      and c.salary between sg.losal and sg.hisal
      and sg.grade = 'A'
group by s.dept_name, sg.grade
having count(c.id)>=2;

       부서명                       인원수 등

       ------------------------ ---------- --

       자재부                            2 A

       개발부                            2 A

   3. OUTER JOIN : 

            NULL의 비교는 FALSE, NULL의 연산은 NULL이라는 특징을 이용한 JOIN

            NULL값에 의하여, 조건문에 배제된 값을 출력할 때 사용.

select
c.name 이름, s.dept_name 부서명
from c_emp c, s_dept s
where c.dept_id = s.id;

        이름                           부서명

        ------------------------------ ------------------------

        김사장                         총무부

        홍길동                         총무부

        홍길순                         총무부

        이순신                         개발부

        강감찬                         개발부

        류시원                         개발부

        이문세                         영업부

        강호동                         영업부

        강문영                         영업부

        추현재                         인사부

        박상민                         인사부

        박상면                         인사부

        유재석                         자재부

        노무현                         자재부

        전두환                         자재부

        김일성                         회계부

        김정일                         회계부

        17 개의 행이 선택되었습니다.

        OUTER JOIN 적용(+)

select
c.name 이름, s.dept_name 부서명
from c_emp c, s_dept s
where c.dept_id = s.id(+);

        이름                           부서명

        ------------------------------ ------------------------

        홍길순                         총무부

        홍길동                         총무부

        김사장                         총무부

        류시원                         개발부

        강감찬                         개발부

        이순신                         개발부

        강문영                         영업부

        강호동                         영업부

        이문세                         영업부

        박상면                         인사부

        박상민                         인사부

        추현재                         인사부

        전두환                         자재부

        노무현                         자재부

        유재석                         자재부

        김정일                         회계부

        김일성                         회계부

        신입1

        신입2

        19 개의 행이 선택되었습니다.

   4. SELF JOIN

           한 Table에 대하여, 두 개의 Alias를 사용하여

           FROM절에 두 개의 테이블을 사용하는 것 처럼 조인한다.

select
e1.empno 사번, e1.ename 이름, e2.empno 매니저사번, e2.ename 매니저이름
from emp e1, emp e2
where e1.mgr=e2.empno
order by e1.empno;

              사번 이름                 매니저사번 매니저이름

        ---------- -------------------- ---------- --------------------

              7369 SMITH                      7902 FORD

              7499 ALLEN                      7698 BLAKE

              7521 WARD                       7698 BLAKE

              7566 JONES                      7839 KING

              7654 MARTIN                     7698 BLAKE

              7698 BLAKE                      7839 KING

              7782 CLARK                      7839 KING

              7788 SCOTT                      7566 JONES

              7844 TURNER                     7698 BLAKE

              7876 ADAMS                      7788 SCOTT

              7900 JAMES                      7698 BLAKE

              7902 FORD                       7566 JONES

              7934 MILLER                     7782 CLARK

        

        OUTER JOIN 적용(+)   

select
e1.empno 사번, e1.ename 이름, e2.empno 매니저사번, e2.ename 매니저이름
from emp e1, emp e2
where e1.mgr=e2.empno(+)
order by e1.empno;

              사번 이름                 매니저사번 매니저이름

        ---------- -------------------- ---------- --------------------

              7369 SMITH                      7902 FORD

              7499 ALLEN                      7698 BLAKE

              7521 WARD                       7698 BLAKE

              7566 JONES                      7839 KING

              7654 MARTIN                     7698 BLAKE

              7698 BLAKE                      7839 KING

              7782 CLARK                      7839 KING

              7788 SCOTT                      7566 JONES

              7839 KING

              7844 TURNER                     7698 BLAKE

              7876 ADAMS                      7788 SCOTT

              7900 JAMES                      7698 BLAKE

              7902 FORD                       7566 JONES

              7934 MILLER                     7782 CLARK