[ SQL ] JOIN (문제풀이와 포괄조인)

하루입니다.

 

 

---

 

 

•  직원아이디, 직원이름, 직종제목, 급여, 급여등급, 소속부서명, 근무지도시명, 관리자이름을 조회하기
•         E1          E1           J        E1         S           D                 L               E2

SELECT E.EMPLOYEE_ID        EMP_ID,
       E.FIRST_NAME         EMP_NAME,
       J.JOB_TITLE          EMP_JOB_TITLE,
       E.SALARY             EMP_SALARY,
       S.GRADE              EMP_GRADE,
       D.DEPARTMENT_NAME    EMP_DEPT_NAME,
       L.CITY               EMP_DEPT_CITY,
       M.FIRST_NAME         MGR_NAME
FROM EMPLOYEES E, EMPLOYEES M, JOBS J, SALARY_GRADE S, DEPARTMENTS D, LOCATIONS L
WHERE E.JOB_ID = J.JOB_ID                                   
AND E.SALARY >= S.MIN_SALARY AND E.SALARY <= S.MAX_SALARY   
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID                       
AND D.LOCATION_ID = L.LOCATION_ID                           
AND E.MANAGER_ID = M.EMPLOYEE_ID                          
ORDER BY E.EMPLOYEE_ID;

코드해석

FROM   총 5개의 테이블, 셀프조인까지 해서 6개의 테이블이 적힌다.

SELECT  직원의 아이디, 이름, 직종이름, 급여, 급여등급, 소속부서, 근무지도시명 / 매니저의 이름

WHERE  직원의 JOB_ID와 직종의 JOB_ID 컬럼같이 같은 행끼리 연결한다.
AND     직원의 SALARY가 등급의 MIN_SALARY이상, MAX_SALARY 이하인 행끼리 연결한다.
AND     직원의 DEPARTMENT_ID와 부서의 DEPARTMENT_ID가 같은 행끼리 연결한다.
AND     직원의 DEPARTMENT_ID와 부서의 DEPARTMENT_ID가 같은 행의 부서 LOCATION_ID와 소재지의 LOCATION_ID가 같은 행끼리 연결한다. (왜 이렇게 적냐면 직원과 장소 테이블은 연관키가 없어서 부서를 통해 연결되기 때문)
AND     직원의 MANAGER_ID와 관리자의 EMPLOYEE_ID가 같은 행끼리 연결한다.
ORDER BY 직원 아이디를 오름차순으로 정렬한다.

 

• 결과는 총 105개의 행이 나온다. 
• 하지만 총 직원은 107명 ...
• 왜 2명의 직원이 빠졌냐면 - 100번 STEVEN은 최고직원이라 상사가 NULL이다. 170번 KIMBERLY는 부서아이디가 NULL이다. 조인할 때 하나라도 값이 NULL이라면 조인되지 않기에 벌어진 불상사이다.
• 소외된 두 사람을 함께 조회하기 위해선 포괄조인을 사용해야 한다.

 

 

---

 

 

포괄조인

• 테이블을 조인했을 때 연결된 행이 없는 정보도 조회할 수 있게 하는 조인
• 한쪽 테이블에는 데이터가 있고, 다른쪽 테이블에는 데이터가 없는 경우에 데이터가 있는 쪽 테이블의 내용을 전부 조회하는 조인방법이다.
• 위에서 100번은 MANAGER_ID가, 170번은 DEPARTMENT_ID가 NULL이라 JOIN에 포함되지 못했다. 하지만 다른 정보들은 전부 있는 상황.
• 이런 상황에서 데이터가 없는 쪽에 NULL행을 추가해서 100번도 107번도 조회할 수 있게 하는 게 포괄조인이다.

 

 

• '모든' 직원아이디, 직원이름, 직종제목, 급여, 급여등급, 소속부서명, 근무지도시명, 관리자이름을 조회하기
•               E1          E1           J        E1         S           D                 L               E2
• NULL행을 추가할 곳에 (+)를 붙이면 된다. 왼쪽 오른쪽 상관 없지만 양쪽에 붙일 순 없다.
• 여기서는 직원테이블 부서아이디가 NULL이라서 부서테이블 부서아이디에 NULL을 더해주기 위해, 직원테이블의 매니저아이디가 NULL이라서 부서테이블 직원아이디에 NULL 더해주기 위해 (+)를 사용했다.
• 107명이 모두 조회된다!
• LOCATION_ID에는 왜 붙인 걸까?

SELECT E.EMPLOYEE_ID        EMP_ID,
       E.FIRST_NAME         EMP_NAME,
       J.JOB_TITLE          EMP_JOB_TITLE,
       E.SALARY             EMP_SALARY,
       S.GRADE              EMP_GRADE,
       D.DEPARTMENT_NAME    EMP_DEPT_NAME,
       L.CITY               EMP_DEPT_CITY,
       M.FIRST_NAME         MGR_NAME
FROM EMPLOYEES E, EMPLOYEES M, JOBS J, SALARY_GRADE S, DEPARTMENTS D, LOCATIONS L
WHERE E.JOB_ID = J.JOB_ID                                   
AND E.SALARY >= S.MIN_SALARY AND E.SALARY <= S.MAX_SALARY   
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)                       
AND D.LOCATION_ID = L.LOCATION_ID(+)
AND E.MANAGER_ID = M.EMPLOYEE_ID(+)                            
ORDER BY E.EMPLOYEE_ID;

 

 

• 밑의 그림을 보자.
• 178번의 E.부서아이디는 NULL이라 조인하면 조회되지 않는다. 조인에 포함하기 위해 D.부서에 NULL행을 추가했다.
• 178번 직원의 부서테이블 행은 모두 NULL이다.
• 이 상황에서 장소ID를 알기 위해 부서테이블의 장소ID와 장소테이블의 장소ID를 연결해야 한다.
• 하지만 178의 부서테이블 행은 모두 NULL ... 이대로 그냥 조인하면 또다시 누락될 것. NULL이니까.
• NULL과 장소ID를 연결하기 위해 (+)를 사용하여 178번의 장소테이블 행에도 NULL을 추가시킨다.
• 이렇게 하면 ID, NAME, DEPT_ID는 조회되고 나머지 값은 모두 NULL로 조회된다.

 

 

---

 

 

잘못된 정보에 대한 지적은 언제나 환영입니다. 

 

복습 끝!