[KH정보교육원] 18일차_JOIN

2021.11.4(목)

 

출처: https://www.analyticsvidhya.com/blog/2015/03/basics-sql-rdbms/

 

🌿 equal(등가)/inner(내부) join

• 연결시키는 컬럼의 값이 일치하는 행들만 JOIN되어 조회
• 일치하지 않는 값/행들은 조회에서 제외 → 일치하지 않는 값들 조회하려면 포괄JOIN 등 사용

🌱 연결할 두 컬럼명이 다른 경우

-- 문제 = DEPARTMENT 테이블, LOCATION 테이블 참고해서 부서코드, 부서명, 지역코드, 지역명(LOCAL_NAME)(, 국가코드) 조회
-- ORACLE구문
SELECT DEPT_ID, DEPT_TITLE, LOCATION_ID, LOCAL_NAME, NATIONAL_CODE
FROM DEPARTMENT, LOCATION
WHERE LOCATION_ID = LOCAL_CODE;
-- ANSI구문
SELECT DEPT_ID, DEPT_TITLE, LOCATION_ID, LOCAL_NAME, NATIONAL_CODE
FROM DEPARTMENT
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE); -- 연결고리 컬럼명이 다르므로 ON구문만 사용 가능

🌱 연결할 두 컬럼명이 같은 경우

-- 문제 = 직급이 대리인 사원들의 정보(사번, 이름, 급여(이상 EMPLOYEE 테이블), 직급이름(이상 JOB 테이블)) 조회
-- ORACLE 전용 구문 = WHERE절에서 동등 조건 제시
SELECT EMP_ID, EMP_NAME, SALARY, JOB_NAME -- 조회할 정보 명시
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE -- 두 테이블의 연결고리는 각 테이블을 열어보고 직접 찾아야 함
 AND JOB_NAME = '대리'; -- 협업 시 가독성 향상을 위해 보통 조건은 줄바꿈+1칸띄어씀

-- ANSI 구문
SELECT EMP_ID, EMP_NAME, SALARY, JOB_NAME -- 조회할 정보 명시
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE) -- 또는 JOIN JOB USING (JOB_CODE)
WHERE JOB_NAME = '대리';

🌿 left/right/full join

• 테이블 간의 JOIN 시 일치하지 않는 행도 포함시켜서 조회 가능
• 단, 반드시 LEFT/RIGHT(기준이 되는 테이블) 지정해야 함

🌱 LEFT [OUTER] JOIN

• 2개의 테이블 중 왼편에 기술된 테이블을 기준으로 JOIN
• 뭐가 되었든간에 왼편에 기술된 테이블의 데이터는 무조건/일치하는 것 없어도(NULL 포함) 조회

-- ANSI구문
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE -- 기준 테이블; 이 테이블의 행 수 = 결과 SET의 행 수
LEFT /*OUTER*/ JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID); -- OUTER 생략 가능
-- EMPLOYEE 테이블을 기준으로 조회했기 때문에 EMPLOYEE 테이블(왼쪽에 있는 테이블)에 존재하는 데이터가 뭐가 되었든 간에 조회되게끔 함

-- ORACLE구문
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+);
-- 내가 기준으로 삼을 테이블의 컬럼명의 반대쪽에 (+)를 붙임

🌱 RIGHT [OUTER] JOIN

• 2개의 테이블 중 오른편에 기술된 테이블을 기준으로 JOIN
• 뭐가 되었든간에 오른편에 기술된 테이블의 데이터는 무조건/일치하는 것 없어도(NULL 포함) 조회

-- ANSI구문
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE RIGHT /*OUTER*/ JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID) ORDER BY DEPT_ID;
-- 기준 테이블 = 오른편에 기술된 DEPARTMENT 테이블 -> 소속 직원이 없는 D3, D4, D7 부서들이 RESULT SET에 표시됨
-- OUTER 생략 가능

-- ORACLE구문
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE(+) = DEPT_ID;
-- 내가 기준으로 삼을 테이블의 컬럼명의 반대쪽에 (+)를 붙임

🌱 FULL (OUTER) JOIN

• 2개의 테이블이 가진 모든 행을 조회

-- ANSI구문
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE
FULL OUTER JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

-- ORACLE구문: ERROR 발생; "ONLY ONE OUTER-JOINED TABLE" -> FULL OUTER는 ORACLE에서 사용 불가능
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE(+) = DEPT_ID(+);

🌿 cartesian/cross(교차) join

• 모든 테이블의 각 행들이 서로서로 매핑된 데이터(곱집합)가 조회됨
• 두 테이블의 행들이 모두 곱해진 조합 출력 → 방대한 데이터 출력 → 과부하의 위험

-- 문제 = 사원명, 부서명 조회
-- ORACLE 전용 구문
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT;
-- ANSI 구문
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
CROSS JOIN DEPARTMENT;

🌿 self(자체) join

• 같은 테이블을 다시 한 번 JOIN하는 경우 자기자신의 테이블과 JOIN을 맺음

-- 문제 = 사원 사번, 사원명, 사원 부서코드, 사원 급여 + 사수 사번, 사수명, 사수 부서코드, 사수 급여 조회
-- ORACLE 전용 구문
SELECT E.EMP_ID "사원 사번", E.EMP_NAME "사원명", E.DEPT_CODE "사원 부서코드", E.SALARY "사원 급여",
    M.EMP_ID "사수 사번", M.EMP_NAME "사수명", M.DEPT_CODE "사수 부서코드", M.SALARY "사수 급여"
FROM EMPLOYEE E, EMPLOYEE M
-- WHERE E.MANAGER_ID = M.EMP_ID; -- 나의 사수의 MANAGER_ID와 사수의 EMP_ID를 연결 -> 동등JOIN -> 사수가 있는 사람만 나옴
WHERE E.MANAGER_ID = M.EMP_ID(+); -- 사수가 없는 사원도 조회되도록 LEFT 포괄/OUTER JOIN

-- ANSI 구문
SELECT E.EMP_ID "사원 사번", E.EMP_NAME "사원명", E.DEPT_CODE "사원 부서코드", E.SALARY "사원 급여",
    M.EMP_ID "사수 사번", M.EMP_NAME "사수명", M.DEPT_CODE "사수 부서코드", M.SALARY "사수 급여"
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE M ON (E.MANAGER_ID = M.EMP_ID);

🌿 non equal(비 등가) join

• =를 사용하지 않는 JOIN문
• 지정해주는 컬럼 값이 일치하는 경우(x) '범위'에 포함되는 경우(o) 매칭시킴

-- 문제 = 사원명, 급여, 급여등급(SAL_LEVEL) 함께 조회
-- ORACLE 전용 구문
SELECT EMP_NAME, SALARY, E.SAL_LEVEL
FROM EMPLOYEE E, SAL_GRADE S -- FROM절에 JOIN해줄 테이블 모두 나열
-- WHERE MIN_SAL <= SALARY AND SALARY <= MAX_SAL;
WHERE SALARY BETWEEN MIN_SAL AND MAX_SAL;

-- ANSI 구문(ON구문만 가능)
SELECT EMP_NAME, SALARY, E.SAL_LEVEL
FROM EMPLOYEE E
JOIN SAL_GRADE ON (SALARY BETWEEN MIN_SAL AND MAX_SAL);

🌿 다중 join

• 순서 중요!

-- 문제 = 사번, 사원명, 부서명, 직급명 조회
SELECT * FROM EMPLOYEE;   -- DEPT_CODE    JOB_CODE
SELECT * FROM DEPARTMENT; -- DEPT_ID
SELECT * FROM JOB;        --              JOB_CODE
-- ORACLE 구문
SELECT EMP_ID 사번, EMP_NAME 사원명, DEPT_TITLE 부서명, JOB_NAME 직급명
FROM EMPLOYEE E, DEPARTMENT D, JOB J
WHERE E.DEPT_CODE = D.DEPT_ID(+) -- 개인적으로 해 봤을 때는 D는 별칭 안 만들어도 잘 작동하는 듯..
 AND E.JOB_CODE = J.JOB_CODE(+);
-- ANSI 구문
SELECT EMP_ID 사번, EMP_NAME 사원명, DEPT_TITLE 부서명, JOB_NAME 직급명
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE);

📗 소감

• 여러 가지 종류의 JOIN이 JOIN 대상 테이블들의 레코드들에 대해 어떤 작용을 하는지 머리에 찬찬히 그려보며 이해하자!
• JOIN을 하니 더욱 다양한 데이터 조회가 가능해졌다! 복잡은 하지만, 재미있다
• JOIN의 성능을 고민해보아야 한다고 한다, 흠.. 공부해보자!

📗 homework:

-- 1. 직급이 대리이면서 ASIA 지역에 근무하는 직원들의 사번, 사원명, 직급명, 부서명, 근무지역명, 급여를 조회하시오
SELECT * FROM EMPLOYEE;   -- DEPT_CODE    JOB_CODE
SELECT * FROM DEPARTMENT; -- DEPT_ID                LOCATION_ID
SELECT * FROM JOB;        --              JOB_CODE
SELECT * FROM LOCATION;   --                        LOCAL_CODE

SELECT EMP_ID 사번, EMP_NAME 사원명, JOB_NAME 직급명, DEPT_TITLE 부서명, LOCAL_NAME 근무지역명, SALARY 급여
FROM EMPLOYEE E
LEFT JOIN JOB USING (JOB_CODE) -- JOB_NAME 컬럼을 이용하려면 JOB 테이블 JOIN 필요; EMPLOYEE 테이블과 연결고리 JOB_CODE 있음
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID) -- DEPT_TITLE 컬럼을 이용하려면 DEPARTMENT 테이블 JOIN 필요; EMPLOYEE 테이블과 연결고리 DEPT_CODE/ID 있음
LEFT JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE) -- LOCAL_NAME 컬럼을 이용하려면 LOCATION 테이블 JOIN 필요; DEPARTMENT 테이블과 연결고라 LOCATION_ID/CODE 있음
WHERE JOB_NAME = '대리'
 AND LOCAL_NAME LIKE ('ASIA%');

-- 2. 70년대생이면서 여자이고, 성이 전씨인 직원들의 사원명, 주민번호, 부서명, 직급명을 조회하시오
SELECT EMP_NAME 사원명, EMP_NO 주민번호, DEPT_TITLE 부서명, JOB_NAME 직급명
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID) -- EMPLOYEE 테이블과 DEPARTMENT 테이블 JOIN; EMPLOYEE 테이블에서 DEPT_CODE가 NULL인 행이 배제되지 않도록 LEFT OUTER JOIN
LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE) -- EMPLOYEE 테이블과 JOB 테이블 LEFT OUTER JOIN
WHERE E.EMP_NO LIKE ('7%') -- 주민번호 첫번째 자리로 70년대생 찾기
 AND SUBSTR(E.EMP_NO, 8, 1) = '2' -- 주민번호 뒷부분 첫번째 숫자로 여성 판별
 AND E.EMP_NAME LIKE ('전%'); -- 사원명 첫글자가 '전'인 경우 찾기

-- 3. 이름에 '형'자가 들어있는 직원들의 사번, 사원명, 직급명을 조회하시오
SELECT EMP_ID 사번, EMP_NAME 사원명, JOB_NAME 직급명
FROM EMPLOYEE
LEFT JOIN JOB USING (JOB_CODE) -- '직급명'을 표시하기 위해 EMPLOYEE 테이블과 JOB 테이블 JOIN; 두 테이블의 연결고리 컬럼명이 동일하므로 USING문 사용 -> 각 테이블의 별명 지을 필요 없음
WHERE EMP_NAME LIKE ('%형%'); -- 조회하고자 하는 사원명에 대한 조건

-- 4. 해외영업팀에 근무하는 직원들의 사원명, 직급명, 부서코드, 부서명을 조회하시오
SELECT EMP_NAME 사원명, JOB_NAME 직급명, DEPT_CODE 부서코드, DEPT_TITLE 부서명
FROM EMPLOYEE E
LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE) -- '직급명'을 표시하기 위해 EMPLOYEE 테이블과 JOB 테이블 JOIN
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID) -- '부서명'을 표시하기 위해 EMPLOYEE 테이블과 DEPARTMENT 테이블 JOIN
WHERE D.DEPT_TITLE LIKE ('%해외영업%'); -- 조회하고자 하는 직원들의 근무 부서명에 대한 조건
-- ORDER BY E.DEPT_CODE, E.JOB_CODE;

-- 5. 보너스를 받는 직원들의 사원명, 보너스, 연봉(급여*12), 부서명, 근무지역명을 조회하시오
SELECT EMP_NAME 사원명, BONUS 보너스, SALARY * 12 연봉, DEPT_TITLE 부서명, LOCAL_NAME 근무지역명
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID) -- '부서명'을 표시하기 위해 EMPLOYEE 테이블과 DEPARTMENT 테이블 JOIN
LEFT JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE) -- '근무지역명'을 표시하기 위해 DEPARTMENT 테이블과 LOCATION 테이블 JOIN
WHERE E.BONUS IS NOT NULL; -- 조회하고자 하는 직원들의 보너스에 대한 조건

-- 6. 부서가 있는 직원들의 사원명, 직급명, 부서명, 근무지역명을 조회하시오
SELECT EMP_NAME 사원명, JOB_NAME 직급명, DEPT_TITLE 부서명, LOCAL_NAME 근무지역명
FROM EMPLOYEE E
LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
LEFT JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
WHERE E.DEPT_CODE IS NOT NULL; -- 조회하고자 하는 직원들의 부서에 대한 조건

-- 7. '한국' 과 '일본' 에 근무하는 직원들의 사원명, 부서명, 근무지역명, 근무국가명을 조회하시오
SELECT EMP_NAME 사원명, DEPT_TITLE 부서명, LOCAL_NAME 근무지역명, NATIONAL_NAME 근무국가명
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
LEFT JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
LEFT JOIN NATIONAL N ON (L.NATIONAL_CODE = N.NATIONAL_CODE)
WHERE N.NATIONAL_NAME IN ('한국', '일본');

-- 8. 보너스를 받지 않는 직원들 중 직급코드가 J4 또는 J7 인 직원들의 사원명, 직급명, 급여를 조회하시오
SELECT EMP_NAME 사원명, JOB_NAME 직급명, SALARY 급여
FROM EMPLOYEE E
LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
WHERE BONUS IS NULL AND E.JOB_CODE IN ('J4', 'J7');

-- 9. 사번, 사원명, 직급명, 급여등급, 구분을 조회하는데,
-- 이 때, 구분에 해당하는 값은 급여등급이 S1, S2 인 경우 '고급', 급여등급이 S3, S4 인 경우 '중급', 급여등급이 S5, S6 인 경우 '초급' 으로 조회되게 하시오
SELECT DISTINCT SAL_LEVEL FROM EMPLOYEE; -- 조회 결과 = S1,4,5,6

SELECT EMP_ID 사번
    , EMP_NAME 사원명
    , JOB_NAME 직급명
    , SAL_LEVEL 급여등급
    , CASE WHEN SAL_LEVEL IN ('S1', 'S2') THEN '고급'
           WHEN SAL_LEVEL IN ('S3', 'S4') THEN '중급'
           WHEN SAL_LEVEL IN ('S5', 'S6') THEN '초급'
        END 구분
FROM EMPLOYEE
LEFT JOIN JOB USING (JOB_CODE)
-- LEFT JOIN SAL_GRADE USING (SAL_LEVEL)
ORDER BY 구분, JOB_CODE;

-- 10. 사번, 사원명, 주민번호(마스킹), 입사년월, 입사 시점 나이, 직급명, 급여등급 조회, 근무국가명
SELECT EMP_ID 사번
    , EMP_NAME 사원명
    , RPAD(SUBSTR(EMP_NO, 1, 8), 14, '*') 주민번호
    , TO_CHAR(HIRE_DATE, 'YYYY-MM') 입사년월
    , EXTRACT(YEAR FROM HIRE_DATE) - CONCAT('19', SUBSTR(EMP_NO, 1, 2)) "입사 시점 나이"
    , JOB_NAME 직급명
    , SAL_LEVEL 급여등급
    , NVL(NATIONAL_NAME, '(미정)') 근무국가명
FROM EMPLOYEE E
LEFT JOIN JOB USING (JOB_CODE)
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
LEFT JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
LEFT JOIN NATIONAL N ON (L.NATIONAL_CODE = N.NATIONAL_CODE)
ORDER BY JOB_CODE, "근무국가명" DESC;