데이터베이스 쿼리문 (join)

2018-08-12

database, oracledb, query, inner join, outer join

이번 포스팅에선 실제 실무에서 유용하게 쓰이는 join문에 대해서 알아보겠습니다.

테이블 생성

이해를 돕기 위해 샘플 테이블을 먼저 생성합니다.
다음은 회사의 부서와 사원관리를 위한 테이블입니다.

SQL> --------------------------------------------------------------
SQL> -- 부서 테이블 생성/레코드 넣기
SQL> --------------------------------------------------------------
SQL> create table dept(
  2  deptno number
  3  ,dname varchar2(14)
  4  ,loc varchar2(13)
  5  ,primary key(deptno)
  6  );
SQL> --------------------------------------------------------------
SQL> -- 사원 테이블 생성/레코드 넣기
SQL> --------------------------------------------------------------
SQL> create table emp(
  2  empno number
  3  ,ename varchar2(10)
  4  ,job varchar2(9)
  5  ,mgr number
  6  ,hiredate date
  7  ,sal number
  8  ,comm number
  9  ,deptno number
 10  ,primary key(empno)
 11  );

14개 정도의 정보를 입력합니다.

결과물은 다음과 같습니다.

SQL> select * from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 80/12/17        800                    20
      7499 ALLEN                SALESMAN                 7698 81/02/20       1600        300         30
      7521 WARD                 SALESMAN                 7698 81/02/22       1250        500         30
      7566 JONES                MANAGER                  7839 81/04/02       2975                    20
      7654 MARTIN               SALESMAN                 7698 81/09/28       1250       1400         30
      7698 BLAKE                MANAGER                  7839 81/05/01       2850                    30
      7782 CLARK                MANAGER                  7839 81/06/09       2450                    10
      7788 SCOTT                ANALYST                  7566 87/04/19       3000                    20
      7839 KING                 PRESIDENT                     81/11/17       5000                    10
      7844 TURNER               SALESMAN                 7698 81/09/08       1500          0         30
      7876 ADAMS                CLERK                    7788 87/05/23       1100                    20
      7900 JAMES                CLERK                    7698 81/12/03        950                    30
      7902 FORD                 ANALYST                  7566 81/12/03       3000                    20
      7934 MILLER               CLERK                    7782 82/01/23       1300                    10

14 rows selected.

SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON

위의 테이블은 정규화작업을 모두 거친 상태입니다.
정규화란 관계형 데이터베이스를 설계할 때 중복을 최소화하도록 데이터를 구조화하는 작업을 말합니다.
중복을 제거하는 과정에서 위처럼 테이블이 두가지로 분리됩니다.

분리가 된 테이블을 처음과 같이 하나처럼 보여줄 수 있는 select문을 가리켜 Join문이라고 합니다.
우리는 Join문을 통해 용도에 따라 알맞은 테이블 조합을 추출해낼 수 있습니다.
join문은 inner join과 outer join, 두가지 유형이 존재합니다.

inner join은 두 테이블간에 외래키에 의해 공통된 레코드만 조회할 수 있는 문장입니다.

먼저 join문이 아닌 단순 select문을 예시를 들어 풀이해봅시다.

단순 select문

Q1. 업무, 업무별 사원수를 업무별로 출력하라.

풀이

SQL> select job as 업무, count(job) as 사원수 from emp group by job;
업무                사원수
------------------ ----------
CLERK                       4
SALESMAN                    4
PRESIDENT                   1
MANAGER                     3
ANALYST                     2

각 컬럼명은 별칭을 설정할 수 있습니다.
컬럼명 as ‘별칭’
단, as 는 생략가능.

Q2. WARD라는 직원의 급여, 커미션을 출력하라.

풀이

SQL> select sal, comm from emp where ename='WARD';
       SAL       COMM
---------- ----------
      1250        500

Q3. 커미션을 하지 않은 직원들의 이름을 출력하라.

풀이

SQL> select ename from emp where comm is NULL;

ENAME
--------------------
SMITH
JONES
BLAKE
CLARK
SCOTT
KING
ADAMS
JAMES
FORD
MILLER

SQL문에서는 값이 존재하지 않는 셀(0이 들어있으면 값이 있는 것입니다. )을 지칭할 때
is null 이라는 용어를 사용합니다.

Q4. 81/06/09 보다 늦게 입사한 사원의 이름, 입사일을 출력해라.

SQL> select ename, hiredate from emp where hiredate > '81/06/09';

ENAME                HIREDATE
-------------------- --------
MARTIN               81/09/28
SCOTT                87/04/19
KING                 81/11/17
TURNER               81/09/08
ADAMS                87/05/23
JAMES                81/12/03
FORD                 81/12/03
MILLER               82/01/23
``` 

Q5. 모든 사원들의 급여에서 1000이 높혀진 급여와 사원이름을 출력해라. 

```markdown
SQL> select ename, sal+1000 from emp;

ENAME                  SAL+1000
-------------------- ----------
SMITH                      1800
ALLEN                      2600
WARD                       2250
JONES                      3975
MARTIN                     2250
BLAKE                      3850
CLARK                      3450
SCOTT                      4000
KING                       6000
TURNER                     2500
ADAMS                      2100
JAMES                      1950
FORD                       4000
MILLER                     2300

여기서 잠깐!! 테이블의 구조를 보여주는 sql문은 다음과 같습니다.

1	SQL> desc '테이블명'

통계값을 구하고 싶을 때는 다음과 같은 식을 사용합니다 .
최대 –> max(칼럼명)
최소 –> min(칼럼명)
평균 –> avg(칼럼명)
갯수 –> count(칼럼명)
합계 –> sum(칼럼명)

Q5. 모든 직원들의 최대급여, 최소급여, 급여평균, 급여개수, 급여합을 출력해라.

SQL> select max(sal) 최대값, min(sal) 최소값, avg(sal) 평균값, count(sal) 총갯수, sum(sal) 합계 from emp;

    최대값     최소값     평균값     총갯수       합계
---------- ---------- ---------- ---------- ----------
      5000        800 2073.21429         14      29025

Q6. 급여를 제일 적게 받는 직원의 이름, 급여, 업무를 출력해라.

SQL> select ename, sal, job from emp where sal = (select min(sal) from emp);

ENAME                       SAL JOB
-------------------- ---------- ------------------
SMITH                       800 CLERK

기존 쿼리문 안에 ()로 감싸서 또 쿼리문을 작성할 수 있습니다.
괄호안에 들어가는 쿼리문을 서브쿼리라고 합니다.

inner조인문

Q1. 최대급여를 받는 직원의 이름과 급여, 부서번호, 부서명을 출력해라

풀이

SQL> select ename, sal, d.deptno, dname from emp e, dept d where e.deptno = d.deptno and sal = (select max(sal) from emp);

ENAME                       SAL     DEPTNO DNAME
-------------------- ---------- ---------- ----------------------------
KING                       5000         10 ACCOUNTING

조인을 하는 이유: 부서명 컬럼은 emp테이블에 존재하지 않으므로 그냥 from emp만 할 경우에는 출력할 수 없다.
그러므로 두 테이블간 공통된 외래키인 deptno가 같아야 한다는 조건을 포함시켜 조인처리를 해야한다.

Q2. 업무를 업무별로 출력해라.

SQL> select job from emp group by job;

JOB
------------------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

group by을 사용시 주의할 점!!
select절에서 명시한 조회 대상이 되는 컬럼은 group by 뒤에 명시한 컬럼만 올수 있다.
단, select절에서 명시한 조회 대상 중 count(),sum(),avg()와 같은 집계함수는 예외이다.

Outer Join문

inner join은 테이블 간의 공통된 부분만을 추출합니다.

만약, 댓글 게시판을 구축한다고 가정합시다.
누군가 글을 작성하면 거기에 달린 댓글의 수를 명시하고 싶다면
inner join을 사용해야 할까? 아니면 outer join을 사용해야 할까?

힌트를 주자면, 댓글이 달리지 않은 글도 게시판에서 사라지면 안됩니다…

비슷한 예시를 들어 설명해보겠습니다.

ex) 부서명과 각 부서별 직원의 수를 출력해라!!

inner join으로 풀이

SQL> select dname, count(ename) from dept d, emp e where d.deptno = e.deptno group by dname;

DNAME                        COUNT(ENAME)
---------------------------- ------------
ACCOUNTING                              3
RESEARCH                                5
SALES                                   6

사원 수가 0인 부서는 자연스럽게 제외된 것을 확인 할 수 있습니다.

만약 사원 수가 없는 operation부서도 출력하고 싶다면 어떻게 할까요!?

바로 outer join을 사용하면 됩니다.
형태는 다음과 같습니다.
select ‘칼럼명’ from ‘무조건 다 남기고 싶은 테이블명’ left outer join ‘또다른 테이블명’ on 조건문 group by 컬럼명;

inner join에서 where을 썻다면 outer join에서는 on 을 사용합니다.
그리고 이번 예시같은 경우 부서명은 하나도 빠짐없이 모두 남기고 싶은 테이블이되겠죠.
그러니까 제일 처음 테이블명으로 입력하고 그 뒤에 나머지 테이블명을 적으면 됩니다.

outer join을 적용한 쿼리문

SQL> select dname, count(ename) from dept d left outer join emp e on d.deptno = e.deptno group by dname;

DNAME                        COUNT(ENAME)
---------------------------- ------------
ACCOUNTING                              3
OPERATIONS                              0
RESEARCH                                5
SALES                                   6

댓글게시판의 경우도 아주 비슷합니다.

댓글을 사원이라고 생각하고, 기존 작성글을 부서라고 생각해보겠습니다.

댓글 수가 0인 작성글도 출력하고 싶어요.

그럼 당연히 outer join을 쓰는 것이 맞겠죠!

다음 포스팅에서는 실제로 댓글 게시판을 만들어 보겠습니다.