포스트

04. JOIN

게시
By Kim Jung Wook 16 분읽는 시간

JOIN

  • 오라클 vs ANSI

1) 정의

  • 하나 이상의 테이블에서 데이터를 조회하기 위해 사용
  • 수행 결과는 하나의 Result Set으로 나온다

  • JOIN은 서로 다른 테이블의 행을 하나씩 이어 붙이기 때문에 시간이 오래 걸리는 단점이 있다

  • 관계형 데이터베이스에서 SQL을 이용해 테이블간 ‘관계’를 맺는 방법
    • 관계형 데이터베이스는 최소한의 데이터를 테이블에 담고 있어 원하는 정보를 테이블에서 조회하려면 한 개 이상의 테이블에서 데이터를 읽어와야 되는 경우가 많다.
    • 이 때, 테이블 간 관계를 맺기 위한 연결고리 역할이 필요한데, 두 테이블에서 같은 데이터를 저장하는 컬럼이 연결고리가 된다
    1
2
3
4
5
6
7
8
9
10
11
12
13
14

    -- ✔ 기존에는 서로 다른 테이블의 데이터를 조회 할 경우 아래와 같이 따로 조회했다
  
-- 직원번호, 직원명, 부서코드, 부서명을 조회 하고자 할 때
  
SELECT EMP_ID, EMP_NAME, DEPT_CODE FROM EMPLOYEE;
-- 직원번호, 직원면, 부서코드는 EMPLOYEE테이블에 조회가능
  
SELECT DEPT_ID, DEPT_TITLE FROM DEPARTMENT;
-- 부서명은은 DEPARTMENT테이블에서 조회 가능
  
-- JOIN 구문으로 ..
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE 
FROM EMPLOYEE 
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);

2) 내부 조인(INNER JOIN) (== 등가 조인(EQUAL JOIN))

  • 연결되는 컬럼의 값이 일치하는 행들만 JOIN됨 (== 일치하는 값이 없는 행은 조인에서 제외됨)
  • 작성 방법은 크게 ANSI 구문과 오라클 구문으로 나뉘고, ANSI에서 USING과 ON을 쓰는 방법으로 나뉜다
  • 오라클 전용 구문 : FROM절에 쉼표(,)로 구분하여 합치게 될 테이블명을 기술하고 WHERE절에 합치기에 사용할 컬럼명을 명시한다
  • INNER JOIN 시 문제점 : 연결에 사용된 컬럼의 값이 일치하지 않으면 조회 결과에 포함되지 않는다
1
2
3
4
5
6
7
8
9

-- < ANSI - 연결에 사용할 컬럼명이 다른 경우 ON() 사용 >
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_ID, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

-- < 오라클 >
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_ID, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID;

1
2
3
4
5
6
7
8
9
10

-- < ANSI - 연결에 사용할 컬럼명이 같은 경우 USING(컬럼명)을 사용 >
SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE);

-- < 오라클 - 별칭 사용>
-- 테이블 별로 별칭을 등록할 수 있음
SELECT EMP_ID, EMP_NAME, E.JOB_CODE, JOB_NAME
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE;

3) 외부 조인(OUTER JOIN)

  • 두 테이블의 지정하는 컬럼값이 일치하지 않는 행도 JOIN에 포함을 시킨다
  • 반드시 OUTER JOIN임을 명시해야 한다
1
2
3

-- OUTER JOIN과 비교할 INNER JOIN 쿼리문
SELECT EMP_NAME, DEPT_TITLE FROM EMPLOYEE
/*INNER*/ JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

1
2
3
4
5
6
7
8
9
10
11
12

-- <LEFT [OUTER] JOIN : 합치는 두 테이블 중 왼편에 기술된 테이블의 컬럼 수를 기준으로 JOIN>
--> 왼편에 작성된 테이블의 모든 행이 결과에 포함 되어야 한다 (JOIN이 안되는 행도 결과 포함)

-- < ANSI 표준 >
SELECT EMP_NAME , DEPT_TITLE
FROM EMPLOYEE LEFT JOIN DEPARTMENT
ON (DEPT_CODE = DEPT_ID); -- 23행 (하동운, 이오리 포함)

-- < 오라클 >
SELECT EMP_NAME , DEPT_TITLE
FROM EMPLOYEE , DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+);

1
2
3
4
5
6
7
8
9
10
11
12

-- <RIGHT [OUTER] JOIN : 합치는 두 테이블 중 오른편에 기술된 테이블의 컬럼 수를 기준으로 JOIN>
--> 오른편에 기술된 테이블의 컬럼 수를 기준으로 JOIN

-- < ANSI 표준 >
SELECT EMP_NAME , DEPT_TITLE
FROM EMPLOYEE RIGHT JOIN DEPARTMENT
ON (DEPT_CODE = DEPT_ID);

-- < 오라클 >
SELECT EMP_NAME , DEPT_TITLE
FROM EMPLOYEE , DEPARTMENT
WHERE DEPT_CODE(+) = DEPT_ID;

1
2
3
4
5
6
7
8
9
10
11
12

-- < FULL [OUTER] JOIN   : 합치기에 사용한 두 테이블이 가진 모든 행을 결과에 포함. 오라클 구문은 FULL OUTER JOIN을 사용 못함 >

-- < ANSI 표준 >
SELECT EMP_NAME , DEPT_TITLE
FROM EMPLOYEE 
FULL JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

-- < 오라클 X>
SELECT EMP_NAME , DEPT_TITLE
FROM EMPLOYEE , DEPARTMENT
WHERE DEPT_CODE(+) = DEPT_ID(+);
-- SQL Error [1468] [72000]: ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다

4) 교차 조인(CROSS JOIN == CARTESIAN PRODUCT)

  • 조인되는 테이블의 각 행들이 모두 매핑된 데이터가 검색되는 방법(곱집합)
  • JOIN 구문을 잘못 작성하는 경우 CROSS JOIN의 결과가 조회된다
1
2
3

SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
CROSS JOIN DEPARTMENT; -- 207행 == (EMPLOYEE)23행 * (DEPARTMENT)9행

5) 비등가 조인(NON EQUAL JOIN)

  • ’=’(등호)를 사용하지 않는 JOIN문
  • 지정한 컬럼 값이 일치하는 경우가 아닌, 값의 범위에 포함되는 행들을 연결하는 방식
1
2
3
4
5
6
7
8
9
10

SELECT * FROM SAL_GRADE;

SELECT EMP_NAME, SAL_LEVEL FROM EMPLOYEE;

-- 사원의 급여에 따른 급여 등급 파악하기
SELECT EMP_NAME , SALARY, sg.SAL_LEVEL
FROM EMPLOYEE 
JOIN SAL_GRADE sg ON (SALARY BETWEEN MIN_SAL AND MAX_SAL);
--> 등호가 아닌 범위를 씀 == 비등가 조인
-- EX) 800만원 이상 받는 사람이 무슨 등급인가?? 조회

6) 자체 조인(SELF JOIN)

  • 같은 테이블을 JOIN ( 자기 자신과 JOIN을 맺음 )
  • TIP : 같은 테이블 2개가 있다고 생각하고 JOIN 진행
1
2
3
4
5
6
7
8
9
10
11
12

-- 사번, 이름, 사수의 사번, 사수 이름 조회

-- < ANSI 표준 >
SELECT E1.EMP_ID, E1.EMP_NAME, 
NVL(E1.MANAGER_ID, '없음'), NVL(E2.EMP_NAME, '-')
FROM EMPLOYEE E1 LEFT JOIN EMPLOYEE E2
ON (E1.MANAGER_ID = E2.EMP_ID);

-- < 오라클 >
SELECT e.EMP_ID , e.EMP_NAME , NVL(e.MANAGER_ID, '없음') , NVL(e2.EMP_NAME , '-')
FROM EMPLOYEE e , EMPLOYEE e2 
WHERE e.MANAGER_ID = e2.EMP_ID(+);

7) 자연 조인(NATURAL JOIN)

  • 동일한 타입과 이름을 가진 컬럼이 있는 테이블 간의 조인을 간단히 표현하는 방법
  • 반드시 두 테이블 간의 동일한 컬럼명, 타입을 가진 컬럼이 필요 -> 없을 경우 교차조인이 된다
1
2
3
4

SELECT EMP_NAME, JOB_NAME
FROM EMPLOYEE
--JOIN JOB USING (JOB_CODE);
NATURAL JOIN JOB;

8) 다중 조인

  • N개의 테이블을 조회할 때 사용
  • 순서가 매우 중요!!!!
  • JOIN은 위에서 아래로 차례대로 진행하는데, 다중 JOIN시 앞에서 조인된 결과에 새로운 테이블 내용을 JOIN
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

-- 사원이름, 부서명, 지역명 조회
--> EMPLOYEE, DEPARTMENT, LOCATION

-- < ANSI 표준 > 
SELECT EMP_NAME, DEPT_TITLE , LOCAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE);

SELECT *
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE);

-- 순서 오류
SELECT *
FROM EMPLOYEE
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);
-- SQL Error [904] [42000]: ORA-00904: "LOCATION_ID": 부적합한 식별자
--> EMPLOYEE 테이블에 LOCATION_ID 컬럼이 없어서 오류 발생
--> 해결 방법 : DEPARTMENT와 LOCATION 조인 순서를 바꿔서
--			    EMPLOYEE와 DEPARTMENT가 조인된 결과를 먼저 만들어
--				LOCATION_ID 컬럼이 존재할 수 있도록 만든다

-- < 오라클 전용 >
SELECT EMP_NAME, DEPT_TITLE , LOCAL_NAME
FROM EMPLOYEE, DEPARTMENT, LOCATION
WHERE DEPT_CODE = DEPT_ID -- EMPLOYEE + DEPARTMENT 조인
AND LOCATION_ID = LOCAL_CODE; -- (EMPLOYEE + DEPARTMENT) + LOCATION 조인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- < 연습문제 !! >
-- 직급이 대리이면서 아시아 지역에 근무하는 직원 조회
-- 사번, 이름, 직급명, 부서명, 근무지역명, 급여를 조회하세요

-- < ANSI 표준 >
SELECT EMP_ID , EMP_NAME , JOB_NAME , DEPT_TITLE , LOCAL_NAME , SALARY  
FROM EMPLOYEE
NATURAL JOIN JOB
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
WHERE JOB_NAME = '대리' AND SUBSTR(LOCAL_NAME, 1, 4) = 'ASIA';

-- < 오라클 >
SELECT EMP_ID , EMP_NAME , JOB_NAME , DEPT_TITLE , LOCAL_NAME , SALARY
FROM EMPLOYEE E , JOB J, DEPARTMENT, LOCATION
WHERE (E.JOB_CODE = J.JOB_CODE) 
AND (DEPT_CODE = DEPT_ID) 
AND (LOCATION_ID = LOCAL_CODE)
AND JOB_NAME = '대리' 
AND SUBSTR(LOCAL_NAME, 1, 4) = 'ASIA';
  • 연습문제

    • ① 주민번호가 70년대 생이면서 성별이 여자이고, 성이 ‘전’씨인 직원들의 사원명, 주민번호, 부서명, 직급명을 조회하시오.

      1
2
3
4
5
6
7

        SELECT EMP_NAME , EMP_NO , DEPT_TITLE, JOB_NAME 
  FROM EMPLOYEE
  LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
  LEFT JOIN JOB USING (JOB_CODE)
  WHERE SUBSTR(EMP_NO, 1, 2) BETWEEN '70' AND '79' 
  	AND SUBSTR(EMP_NO, INSTR(EMP_NO, '-')+1, 1) = '2' 
  	AND SUBSTR(EMP_NAME, 1, 1) = '전';

    • ② 이름에 ‘형’자가 들어가는 직원들의 사번, 사원명, 직급명, 부서명을 조회하시오.

      1
2
3
4
5

        SELECT EMP_ID , EMP_NAME , JOB_NAME , DEPT_TITLE
  FROM EMPLOYEE
  LEFT JOIN JOB USING (JOB_CODE)
  LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
  WHERE EMP_NAME LIKE '%형%';

    • ③ 해외영업 1부, 2부에 근무하는 사원의 사원명, 직급명, 부서코드, 부서명을 조회하시오.

      1
2
3
4
5
6

        SELECT E.EMP_NAME , J.JOB_NAME , E.DEPT_CODE , D.DEPT_TITLE 
  FROM EMPLOYEE E
  JOIN JOB J USING (JOB_CODE)
  JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
  WHERE D.DEPT_TITLE IN ('해외영업1부', '해외영업2부');
  --WHERE D.DEPT_TITLE LIKE '해외영업%' AND D.DEPT_TITLE <= '해외영업2부';

    • ④ 보너스포인트를 받는 직원들의 사원명, 보너스포인트, 부서명, 근무지역명을 조회하시오.

      1
2
3
4
5

        SELECT E.EMP_NAME , E.BONUS , D.DEPT_TITLE , L.LOCAL_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)
  WHERE E.BONUS IS NOT NULL;

    • ⑤ 부서가 있는 사원의 사원명, 직급명, 부서명, 지역명 조회

      1
2
3
4
5
6

        SELECT E.EMP_NAME , J.JOB_NAME , D.DEPT_TITLE , L.LOCAL_NAME 
  FROM EMPLOYEE e 
  LEFT JOIN JOB j 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)
  WHERE D.DEPT_TITLE IS NOT NULL;

    • ⑥ 급여등급별 최소급여(MIN_SAL)를 초과해서 받는 직원들의 사원명, 직급명, 급여, 연봉(보너스포함)을 조회하시오. 연봉에 보너스포인트를 적용하시오.

      1
2
3
4
5

        SELECT E.EMP_NAME , J.JOB_NAME , E.SALARY "급여" , (E.SALARY+NVL(E.SALARY*E.BONUS, 0))*12 "연봉"
  FROM EMPLOYEE e 
  LEFT JOIN JOB j USING (JOB_CODE)
  LEFT JOIN SAL_GRADE sg USING (SAL_LEVEL)
  WHERE SALARY > SG.MIN_SAL;

    • ⑦ 한국(KO)과 일본(JP)에 근무하는 직원들의 사원명, 부서명, 지역명, 국가명을 조회하시오.

      1
2
3
4
5
6

        SELECT E.EMP_NAME "사원명" , D.DEPT_TITLE "부서명" , L.LOCAL_NAME "지역명" , N.NATIONAL_NAME "국가명"
  FROM EMPLOYEE e 
  LEFT JOIN DEPARTMENT d ON (E.DEPT_CODE = D.DEPT_ID)
  RIGHT JOIN LOCATION l ON (D.LOCATION_ID = L.LOCAL_CODE)
  JOIN NATIONAL n USING (NATIONAL_CODE)
  WHERE N.NATIONAL_NAME IN ('한국', '일본');

    • ⑧ 같은 부서에 근무하는 직원들의 사원명, 부서코드, 동료이름을 조회하시오. SELF JOIN 사용

      1
2
3
4
5

        SELECT E.EMP_NAME "사원명" , E.DEPT_CODE "부서코드" , E2.EMP_NAME "동료이름"
  FROM EMPLOYEE e 
  LEFT JOIN EMPLOYEE e2 ON (e.DEPT_CODE = e2.DEPT_CODE)
  WHERE E.EMP_NAME <> E2.EMP_NAME
  ORDER BY E.EMP_NAME ;

    • ⑨ 보너스포인트가 없는 직원들 중에서 직급코드가 J4와 J7인 직원들의 사원명, 직급명, 급여를 조회하시오. 단, JOIN, IN 사용할 것

      1
2
3
4
5

        SELECT E.EMP_NAME , J.JOB_NAME , E.SALARY 
  FROM EMPLOYEE e 
  JOIN JOB j USING (JOB_CODE)
  WHERE E.BONUS IS NULL
  AND JOB_CODE IN ('J4', 'J7');
KH 학원 공부, DB
DB ORACLE