프로그래밍/데이터베이스

테이블 구조도, 테이블 조인(join)

안싱미 2016. 2. 17. 14:20

Oracle SQL Manager 에서 제공하는 HR 테이블들의 구조도를 그려보자.

가장 핵심이 되는 테이블은 Key Table이라고 부른다. 연결고리가 가장 많기 때문에. 그렇다면 여기에서 key table 은 EMPLOYEES 테이블이다.

 

1
2
3
4
5
6
7
8
-- 전체 사원들의 사원번호, LAST_NAME, 부서명, 부서 번호를 조회한다.
SELECT  EMPLOYEE_ID
        , LAST_NAME
        , DEPARTMENT_NAME
        , D.DEPARTMENT_ID
FROM    EMPLOYEES E
        , DEPARTMENTS D
;
 cs

DEPARTMENT_ID 는 EMPLOYEES테이블에도 있고, DEPARTMENTS 테이블에도 있기 때문에 SELECT 문 안에 테이블명.컬럼명 으로 적어주어야한다.
보통 저렇게 다 적지 않고 약칭을 주어 가독성을 높인다.

이렇게 하면 너무 많은 결과들이 출력된다(데카르트, 카테이션 버그). 그래서 두 테이블이 일치하는 것만 가져와야한다. 아래처럼! 이것을 조인, 또는 이이퀴 조인(EQUI JOIN) 이라고 부른다.

그리고 일반적인 경우 행들은 PK와 FK 값의 연관에 의해 JOIN 이 성립된다.

EQUI JOIN

두 개의 테이블 간에 컬럼 값들이 서로 정확하게 일치하는 경우에 사용되는 방법이다. 형식은 다음과 같다.

SELECT    테이블1.컬럼명, 테이블2.컬럼명
FROM       테이블1, 테이블2
WHERE     테이블1.컬럼명1 = 테이블2.컬럼명2;

 

 

1
2
3
4
5
6
7
8
9
-- 전체 사원들의 사원번호, LAST_NAME, 부서명, 부서 번호를 조회한다.
SELECT  EMPLOYEE_ID
        , LAST_NAME
        , DEPARTMENT_NAME
        , D.DEPARTMENT_ID
FROM    EMPLOYEES E
        , DEPARTMENTS D
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
;
 cs

 

예제1.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 16. 커미션을 버는 모든 사람들의
-- LAST_NAME, 부서명, 지역ID, 도시명을 조회
SELECT   LAST_NAME
        , DEPARTMENT_NAME
        , L.LOCATION_ID  -- LOCATION TABLE에서 가져다 쓴다고 지정하지 않으면, ERROR
        , CITY
FROM    EMPLOYEES E
        , DEPARTMENTS D
        , LOCATIONS L
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID  
AND     D.LOCATION_ID = L.LOCATION_ID  
--AND     L.CITY = 'Seattle'
AND     L.LOCATION_ID = (
                          SELECT  LOCATION_ID
                          FROM    LOCATIONS
                          WHERE   CITY = 'Seattle'
                        )
;
 
 cs

결과

 

 

예제2.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 부서명, 위치 ID, 각 부서별 사원 총 수, 각 부서별 평균 연봉을 조회한다.
-- 평균 연봉은 소수점 2자리까지만 표현한다.
SELECT  DEPARTMENT_NAME
        , LOCATION_ID
        , COUNT(EMPLOYEE_ID)
        , ROUND(AVG(SALARY), 2)
FROM    DEPARTMENTS D
        , EMPLOYEES E
WHERE   D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP   BY DEPARTMENT_NAME
          , LOCATION_ID
;
 
 cs

결과

 

 

예제3.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 27. 다음과 같은 포맷으로 각 부서 별 각 직업 별 연봉 총 합 및 각 부서별 연봉 총 합을 조회한다.(...넘나어렵...)
SELECT  JOB_ID JOB
        ,NVL((
            SELECT  SUM(SALARY)
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 20
            AND     JOB_ID = E.JOB_ID
         ), 0) "DEPT 20"
        ,NVL((
            SELECT  SUM(SALARY)
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 50
            AND     JOB_ID = E.JOB_ID
         ), 0) "DEPT 50"
        ,NVL((
            SELECT  SUM(SALARY)
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 80
            AND     JOB_ID = E.JOB_ID
         ), 0) "DEPT 80"
        ,NVL((
            SELECT  SUM(SALARY)
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 90
            AND     JOB_ID = E.JOB_ID
         ), 0) "DEPT 90"
        ,NVL((
            SELECT  SUM(SALARY)
            FROM    EMPLOYEES
            WHERE   JOB_ID = E.JOB_ID
         ), 0) TOTAL
FROM    EMPLOYEES E
GROUP   BY JOB_ID
;
 
 cs

결과