[ SQL ] JOIN(등가조인 여러개 하기, 비등가조인, 셀프조인)

하루입니다.

 

 

---

관계테이블. 파란 글자는 테이블명, 검은 글자는 컬럼명, 초록 글자는 데이터타입, 화살표는 서로 관련이 있다는 뜻.

 

 

 

 

등가조인

• 두 테이블 등가조인하기
• 조인할 때는 어떤 테이블에 어떤 값이 있고, 어떤 행끼리 연결해 필요한 정보를 가져올 것인지를 고민해야 한다. 자신이 맡은 테이블(작업)에 대한 이해도가 있어야 한다.
• 급여를 10000이상 받는 직원의 아이디, 이름,     직종제목,     급여,     소속부서명을 조회하기
        EMPLOYEES                   EMPLOYEES        JOBS    EMPLOYEES   DEPARTMENTS

 

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, J.JOB_TITLE, E.SALARY, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, JOBS J
WHERE E.SALARY >= 10000
AND E.JOB_ID = J.JOB_ID
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY E.EMPLOYEE_ID;

코드 해석

FROM 직원 부서 직업 테이블을 조인할 것이다.

SELECT 직원의 직원아이디, 이름, 급여, 직업의 직업명, 부서의 부서 이름을 들고 온다.

WHERE 일단 급여가 10000이상은 돼야 하고

AND 조인조건은 직원 부서 테이블의 부서아이디로 할 것.

ORDER BY 직원아이디 오름차순으로 정렬할 것.

 

 

 

• 엥 그럼. 3개 이상 등가조인은 어떻게 하나요?
• 위와 방식 같음. 다만 테이블을 조인할 때, 조인조건은 최소 테이블 - 1개가 있어야 한다. 세 개의 물건을 연결할 때 최소 두 개의 고리가 필요한 것과 같은 원리.
• 급여를 10000 이상 받는 직원의 아이디, 이름,     직종제목,     소속부서명,     근무지 도시명을 조회하기
•  EMPLOYEES                         EMPLOYEES         JOBS     DEPARTMENTS    LOCATION                 

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME, L.CITY
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L, JOBS J
WHERE E.SALARY >= 10000
AND E.JOB_ID = J.JOB_ID
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
ORDER BY E.EMPLOYEE_ID;

4개의 테이블을 조인했고 3개의 조인조건이 나온 것을 볼 수 있다.

 

연결부 : 직원 테이블과 직업 테이블의 직업아이디(를 통해 직업 이름을 알 수 있다.)

           직원 테이블과 부서 테이블의 부서아이디(를 통해 부서 이름을 알 수 있다.)

           부서 테이블과 장소 테이블의 로케이션아이디(를 통해 근무지 도시명 알 수 있다.)

 

만약 냅다 직원과 장소를 연결하래. 근데 둘은 이어진 게 없어. 그러면 직원 - 부서 - 장소 무조건 무언가 중간을 거쳐 가야 한다.

 

 

 

비등가조인

• 등가조인이란 같은 값을 조인하는 것이다. 이름이 등가인 이유도 EQUAL = 을 뜻하는 것임.
• 비등가조인은 같은 값 아니다. = 대신 크다 작다 등의 조건으로 따진다.
• 진행 방식. SALARY_GRADE라는 테이블을 만들었다. 밑은 새로운 행 추가한 것. 최소급여와 최대급여를 값으로 넣고 A부터 E까지의 등급을 만들었다. 숫자 사이의 급여가 오면 해당 등급이 된다.

INSERT INTO SALARY_GRADE(GRADE, MIN_SALARY, MAX_SALARY) VALUES('A', 25000, 50000);
INSERT INTO SALARY_GRADE(GRADE, MIN_SALARY, MAX_SALARY) VALUES('B', 15000, 24999);
INSERT INTO SALARY_GRADE(GRADE, MIN_SALARY, MAX_SALARY) VALUES('C', 7000, 14999);
INSERT INTO SALARY_GRADE(GRADE, MIN_SALARY, MAX_SALARY) VALUES('D', 3000, 6999);
INSERT INTO SALARY_GRADE(GRADE, MIN_SALARY, MAX_SALARY) VALUES('E', 0, 2999);

 

• 급여를 1000이상 받는 직원들의 직원아이디,   이름,   급여,     급여등급 조회하기
•  EMPLOYEES                          EMPLOYEES  EMPLOYEES      SALARY_GRADE

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, S.GRADE
FROM EMPLOYEES E, SALARY_GRADE S
WHERE E.SALARY >= 10000
AND E.SALARY >= S.MIN_SALARY AND E.SALARY <= S.MAX_SALARY
ORDER BY E.EMPLOYEE_ID;

코드해석

FROM    직원 테이블과 급여등급 테이블에서

SELECT   E.직원 아이디, E.직원 이름, E.급여, S.등급을 들고 올 건데 (붙일 건데 / 합칠 건데)

WHERE   일단 급여는 10000 이상이었으면 하고

AND      급여는 S.최소급여보다 크고 S.최대급여보다 작은 식을 사용해서 등급이 나오게 할 거야

ORDER BY    직원 아이디 오름차순으로 나오게 할 거야.

 

 

그림으로 보면 이렇다. 노란 값을 조회하는 게 목표이다.

 

 

 

 

 

• 이번엔 직종아이디, 직종최저급여, 직종최저급여의 등급, 직종최고급여, 직종최고급여의 등급을 구하려 한다.
                JOBS          JOBS          SALARY_GRADE1         JOBS       SALARY_GRADE2
• 무슨 뜻이냐면 내 최저급여가 4000원, 최대급여가 8000원이라고 했을 때 각각의 등급을 구한다는 것. 최저급여등급은 D, 최대급여등급은 C 이렇게.
• 최저급여의 등급 한 번, 최대급여의 등급 한 번 이렇게 조회할 거라 테이블이 두 개 필요하다. 정확히는 테이블을 두 번 부르고 다른 별칭을 붙일 것이다. 

 

초록색(JOBS), 주황색(SALARY_GRADE1), 파란색(SALARY_GRADE2) 세 개의 테이블이 있다.

 

 

이렇게 조인할 것. 최저급여와 주황 테이블 한 번, 최대급여와 파란 테이블 한 번. 

그런데 왜 따로 조인해야 할까? 그냥 테이블 하나로 돌려쓰면 안 될까?

일단은 주황 테이블로 두 번 조인한다고 할 때, 주황 테이블이 나올지 파란 테이블이 나올지 모르기 때문(???)이라고 하셨는데 ... 

잘 모르겠으니까 직접 해 봤다.

 

 

정상적인 실행. S1/S2 테이블을 사용해 최저급여/최대급여 등급을 매겼다. 

 

 

 

S1만 사용했다. 19개의 행 중 최저 / 최대 등급이 일치하는 하나의 행만 조회된다.

 

흠. 여기선 AND를 사용하기에 최저급여등급 AND 최대급여등급, 즉 최저급여등급과 최대급여등급이 일치하는 행만 나올 수 있다. 이게 선생님이 말해주신 내용인가? 테이블을 하나만 사용하면 주황 파랑 중 고를 수 없기 때문에? 당장은 궁금증 해결! 지금은 테이블을 두 개 사용하는 경우도 있다는 걸 기억하자.

 

 

셀프조인

셀프조인은 테이블 한 개로 조인을 하는 것이다.
같은 테이블을 2개 준비하고, 하나는 자식레코드가 있는 테이블, 하나는 부모레코드가 있는 테이블로 역할을 나눈다.
부모가 1 : 자식이 많은 관계. (일단은)

 

셀프조인 예시이자 셀프조인 사용 이유.

 

조직도이다. 본사와 본사가 관리하는 부서를 조회하고 싶은데 한 테이블 안에 있네. 이런 상황에서 셀프조인을 사용한다.

 

 

• 셀프조인 실습. 
• 사원아이디, 사원이름, 해당사원을 관리하는 관리자 아이디, 관리자 이름을 조회하기.
• 모두 EMPLOYEEES 안에 들어 있다. 그렇다면 부모테이블과 자식테이블로 나눠야지. 매니저ID가 직원ID를 참조하니까 직원아이디를 부모로, 매니저아이디를 자식으로 해야지. 별칭으로 같은 테이블의 역할을 나누다 보니 별칭은 필수이다.

 

• 사원아이디, 사원이름, 해당사원을 관리하는 관리자 아이디, 관리자 이름을 조회하기.
•     EMP         EMP                     MGR                              MGR

SELECT EMP.EMPLOYEE_ID EMP_ID,
       EMP.FIRST_NAME  EMP_NAME,
       MGR.EMPLOYEE_ID MGR_ID,
       MGR.FIRST_NAME  MGR_NAME
FROM EMPLOYEES EMP, EMPLOYEES MGR
WHERE EMP.MANAGER_ID = MGR.EMPLOYEE_ID
ORDER BY EMP.EMPLOYEE_ID;

코드해석

FROM    직원 테이블에서 셀프조인 해야 하니까 직원 정보 / 매니저 정보 용도로 별칭 붙여 나눠서

SELECT   직원 아이디, 직원 이름, 매니저 아이디, 매니저 이름이라는 별칭을 붙

WHERE   EMP.MANAGER_ID = MGR.EMPLOYEE_ID

AND      급여는 S.최소급여보다 크고 S.최대급여보다 작은 식을 사용해서 등급이 나오게 할 거야

ORDER BY    직원 아이디 오름차순으로 나오게 할 거야.

라는 뜻. 

 

 

그림으로 보자면 이런 느낌이다.

 

 

• 갑자기 셀프조인 이해가 안 돼서 ;;; 거의 1시간 반은 셀프조인 이해에 쏟았다. 진짜 바보인가 ... ? 
• 헷갈린 부분은 테이블도 같고 EMPLOYEE_ID와 FIRST_NAME도 똑같이 두 번 사용되는데 어떻게 사원아이디 사원이름 매니저아이디 매니저이름이 따로 나오지? 라고 생각한 것. 
• 간과한 점은 같은 테이블이지만 따로 동작한다는 점이다. 
• 그러니까 ... 

 

 

같은 테이블 두 개가 있다. 셀프조인을 할 것이기에 왼쪽은 EMP(직원), 오른쪽은 MGR(매니저)이라고 별칭을 붙였다.

두 테이블을 조인하려 한다. 직원테이블의 매니저 아이디와 매니저 테이블의 직원 아이디가 같은 것을 조인 조건으로 한다.

 

 

 

그럼 이런 식으로 행이 나오겠지. 주황색처럼 나온다. 이 부분이 이해가 안 돼서 그렇게 난리를 피웠는데 ... 매니저 아이디와 같은 임플로이 아이디 - 매니저도 결국 직원이므로 매니저의 아이디들'만' 나오게 된다. 이렇게 왼쪽에는 직원 정보만, 오른쪽에는 매니저의 정보만 있는 행들이 나오게 된다. 그러면 직원에서는 직원 이름이, 매니저에서는 매니저 아이디만 있으니까 - 매니저 이름만 있고 - 그러면 매니저 이름을 뽑아낼 수 있겠지! 아이고!!!!!!!!!!!!!!!!!!!!!!!!!!

 

 

 

이 상태에서 코드를 수행하면

SELECT EMP.EMPLOYEE_ID EMP_ID,
       EMP.FIRST_NAME  EMP_NAME,
       MGR.EMPLOYEE_ID MGR_ID,
       MGR.FIRST_NAME  MGR_NAME

직원 아이디 / 이름, 매니저 아이디 / 이름이 나오게 된다. 같은 테이블이지만 별칭을 줘서 별도의 테이블이 된다는 걸 잊지말자!!!!!!!!!!!!!!!!!

 

 

 

 

임시저장했는데 날아가고 냅다 셀프조인 이해 안 되고 (바보) 분명 여유있게 끝날 거라 생각했는데 예상 시간을 초과했다 그래도 셀프조인 이해했으니 됐어 ... 

 

 

---

 

 

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

 

복습 끝!