8. JOIN

<JOIN>

두 개의 이상의 테이블에서 데이터를 조회하고자 할 때 사용하는 구문이다.

 

 

 

<등가 조인(EQUAL JOIN) / 내부 조인(INNER JOIN)>

연결시키는 컬럼의 값이 일치하는 행들만 조인되서 조회한다.(일치하는 값이 없는 행은 조회 X)

 

1) 오라클 전용 구문

FROM 절에 조회하고자 하는 테이블들을 콤마(,)로 구분하여 나열한다.

WHERE 절에 매칭 시킬 컬럼명에 대한 조건을 제시한다.

[표현법]
SELECT 컬럼, 컬럼, ...
FROM 테이블1, 테이블2
WHERE 테이블1.컬럼명 = 테이블2.컬럼명;

 

2) ANSI 표준 구문

FROM 절에 기준이 되는 테이블을 기술한다.

JOIN 절에 같이 조회하고자 하는 테이블을 기술 후 매칭 시킬 컬럼에 대한 조건을 기술한다.

연결에 사용하려는 컬럼명이 같은 경우 ON 구문 대신에 USING(컬럼명) 구문을 사용한다.

[표현법]
SELECT 컬럼, 컬럼, ...
FROM 테이블1
[INNER] JOIN 테이블2 ON (테이블1.컬럼명 = 테이블2.컬럼명);

-- 각 사원들의 사번, 사원명, 부서 코드, 부서명을 조회
SELECT EMP_ID, EMP_NAME, DEPT_CODE
  FROM EMPLOYEE
 ORDER BY DEPT_CODE;

SELECT DEPT_ID, DEPT_TITLE
  FROM DEPARTMENT;

-- 각 사원들의 사번, 사원명, 직급 코드, 직급명을 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE
  FROM EMPLOYEE;

SELECT JOB_CODE, JOB_NAME
  FROM JOB;
  

-- 오라클 구문
-- 1) 연결할 두 컬럼명이 다른 경우
-- EMPLOYEE 테이블과 DEPARTMENT 테이블을 조인하여 사번, 사원명, 부서 코드, 부서명을 조회
-- 일치하는 값이 없는 행은 조회에서 제외된다. (DEPT_CODE NULL 인 사원, DEPT_ID가 D3, D4, D7인 사원)
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE
  FROM EMPLOYEE, DEPARTMENT
 WHERE DEPT_CODE = DEPT_ID;

-- 2) 연결할 두 컬럼명이 같은 경우
-- EMPLOYEE 테이블과 JOB 테이블을 조인해서 사번, 사원명, 직급 코드, 직급명을 조회
-- 방법 1) 테이블명을 이용하는 방법 
SELECT EMP_ID, EMP_NAME, EMPLOYEE.JOB_CODE, JOB_NAME
  FROM EMPLOYEE, JOB
 WHERE EMPLOYEE.JOB_CODE = JOB.JOB_CODE;

-- 방법 2) 테이블의 별칭을 이용하는 방법
SELECT E.EMP_ID, E.EMP_NAME, E.JOB_CODE, J.JOB_NAME
  FROM EMPLOYEE E, JOB J
 WHERE E.JOB_CODE = J.JOB_CODE;

-- ANSI 구문
-- 1) 연결할 두 컬럼명이 다른 경우
-- EMPLOYEE 테이블과 DEPARTMENT 테이블을 조인하여 사번, 사원명, 부서 코드, 부서명을 조회
-- 일치하는 값이 없는 행은 조회에서 제외된다. (DEPT_CODE NULL 인 사원, DEPT_ID가 D3, D4, D7인 사원)
SELECT E.EMP_ID, E.EMP_NAME, E.DEPT_CODE, D.DEPT_TITLE
  FROM EMPLOYEE E
/*INNER*/ JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID);

-- 2) 연결할 두 컬럼명이 같은 경우
-- EMPLOYEE 테이블과 JOB 테이블을 조인해서 사번, 사원명, 직급 코드, 직급명을 조회
-- 방법 1) USING 구문을 이용하는 방법
SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
  FROM EMPLOYEE
 INNER JOIN JOB USING(JOB_CODE); 

-- 방법 2) 테이블의 별칭을 이용하는 방법
SELECT E.EMP_ID, E.EMP_NAME, E.JOB_CODE, J.JOB_NAME
  FROM EMPLOYEE E
 INNER JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE); 

-- 방법 3) NATURAL JOIN을 이용하는 방법 (참고만 하세요~!)
SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
  FROM EMPLOYEE
 NATURAL JOIN JOB;

-- EMPLOYEE 테이블과 JOB 테이블을 조인하여 직급이 대리인 사원의 사번, 사원명, 직급명, 급여를 조회
-- 오라클 구문
SELECT E.EMP_ID, E.EMP_NAME, J.JOB_NAME, E.SALARY
  FROM EMPLOYEE E, JOB J
 WHERE E.JOB_CODE = J.JOB_CODE
   AND J.JOB_NAME = '대리';
  
-- ANSI 구문
SELECT E.EMP_ID, E.EMP_NAME, J.JOB_NAME, E.SALARY
  FROM EMPLOYEE E
 INNER JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 WHERE J.JOB_NAME = '대리';

 

 

 

<다중 JOIN>

여러 개의 테이블 조인하는 경우에 사용한다.

-- EMPLOYEE, DEPARTMENT, LOCATION 테이블을 다중 JOIN 하여 사번, 사원명, 부서명, 지역명 조회
SELECT * FROM EMPLOYEE;     -- DEPT_CODE
SELECT * FROM DEPARTMENT;   -- DEPT_ID      LOCATION_ID
SELECT * FROM LOCATION;     --              LOCAL_CODE

-- ANSI 구문
SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE, L.LOCAL_NAME
  FROM EMPLOYEE E
 INNER JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
 INNER JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE);
-- 다중 조인은 순서가 중요하다.

-- 오라클 구문
SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE, L.LOCAL_NAME
  FROM  EMPLOYEE E, DEPARTMENT D, LOCATION L
 WHERE D.LOCATION_ID = L.LOCAL_CODE 
   AND E.DEPT_CODE = D.DEPT_ID;

 

 

 

<외부 조인 (OUTTER JOIN)>

테이블 간의 JOIN 시 일치하지 않는 행도 포함시켜서 조회가 가능하다.

단, 반드시 기준이되는 테이블(컬럼)을 지정해야 한다. (LEFT/RIGHT/(+))

-- 부서가 지정되지 않는 사원 2명에 대한 정보가 조회되지 않는다.
-- 부서가 지정되어 있어서 DEPARTMENT에 정보가 없는 조회되지 않는다.
SELECT E.EMP_NAME, D.DEPT_TITLE, E.SALARY, E.SALARY * 12
FROM EMPLOYEE E
/*INNER*/ JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID);

-- 1) LEFT [OUTER] JOIN : 두 테이블 중 왼편에 기술된 테이블의 컬럼을 기준으로 JOIN을 진행한다.
-- ANSI 구문
SELECT E.EMP_NAME, D.DEPT_TITLE, E.SALARY, E.SALARY * 12
  FROM EMPLOYEE E 
  LEFT /*OUTER*/ JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID);

-- 오라클 구문
SELECT E.EMP_NAME, D.DEPT_TITLE, E.SALARY, E.SALARY * 12
  FROM EMPLOYEE E , DEPARTMENT D
 WHERE E.DEPT_CODE = D.DEPT_ID(+);

-- 2) RIGHT [OUTER] JOIN : 두 테이블 중 오른편에 기술된 테이블의 컬럼을 기준으로 JOIN을 진행한다.
-- ANSI 구문
SELECT E.EMP_NAME, D.DEPT_TITLE, E.SALARY, E.SALARY * 12
  FROM EMPLOYEE E 
 RIGHT /*OUTER*/ JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID);

-- 오라클 구문
SELECT E.EMP_NAME, D.DEPT_TITLE, E.SALARY, E.SALARY * 12
  FROM EMPLOYEE E , DEPARTMENT D
 WHERE E.DEPT_CODE(+) = D.DEPT_ID;

-- 3) FULL [OUTER] JOIN : 두 테이블이 가지는 모든 행을 조회할 수 있다. (단, 오라클 구문은 지원하지 않는다.)
SELECT E.EMP_NAME, D.DEPT_TITLE, E.SALARY, E.SALARY * 12
  FROM EMPLOYEbE E 
  FULL /*OUTER*/ JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID);

-- 오라클 구문
SELECT E.EMP_NAME, D.DEPT_TITLE, E.SALARY, E.SALARY * 12
  FROM EMPLOYEE E , DEPARTMENT D
 WHERE E.DEPT_CODE(+) = D.DEPT_ID(+);

 

 

<카테시안곱(CARTESIAN PRODUCT) / 교차 조인(CROSS JOIN)>

조인되는 모든 테이블의 각 행들이 서로서로 모두 매핑된 데이터가 검색된다.

테이블의 행들이 모두 곱해진 행들의 조합이 출력 -> 과부화의 위험

-- ANSI
SELECT EMP_NAME, DEPT_TITLE
  FROM EMPLOYEE
 CROSS JOIN DEPARTMENT
 ORDER BY EMP_NAME;

-- 오라클 구문
SELECT EMP_NAME, DEPT_TITLE
  FROM EMPLOYEE, DEPARTMENT
 ORDER BY EMP_NAME;

 

 

<비등가 조인(NON EQUAL JOIN)>

조인 조건에 등호(=)를 사용하지 않는 조인문을 비등가 조인이라고 한다.

지정한 컬럼 값이 일치하는 경우가 아닌, 값의 범위에 포함되는 행들을 연결하는 방식이다.

( = 이외에 비교 연산자 >, <, >=, <=, BETWEEN AND, IN, NOT IN 등을 사용한다.)

ANSI 구문으로는 JOIN ON 구문으로만 사용이 가능하다. (USING 사용 불가)

-- EMPLOYEE 테이블과 SAL_GRADE 테이블을 비등가 조인하여 사원명, 급여, 급여 등급 조회
-- ANSI 구문
SELECT E.EMP_NAME, E.SALARY, S.SAL_LEVEL
  FROM EMPLOYEE E
  JOIN SAL_GRADE S ON (E.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL);

-- 오라클 구문
SELECT E.EMP_NAME, E.SALARY, S.SAL_LEVEL
  FROM EMPLOYEE E, SAL_GRADE S
 WHERE E.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL;

 

 

<자체 조인(SELF JOIN)>

같은 테이블을 다시 한번 조인하는 경우에 사용한다.

-- EMPLOYEE 테이블을 SELF JOIN 하여 사번, 사원명, 부서 코드, 사수 사번, 사수 이름 조회
-- ANSI 구문
SELECT E1.EMP_ID "사번",
       E1.EMP_NAME "사원명",
       E1.DEPT_CODE "부서 코드",
       E1.MANAGER_ID "사수 사번",
       E2.EMP_NAME "사수 이름"
  FROM EMPLOYEE E1
  LEFT OUTER JOIN EMPLOYEE E2 ON (E1.MANAGER_ID = E2.EMP_ID);

-- 오라클 구문
SELECT E1.EMP_ID "사번",
       E1.EMP_NAME "사원명",
       E1.DEPT_CODE "부서 코드",
       E1.MANAGER_ID "사수 사번",
       E2.EMP_NAME "사수 이름"
  FROM EMPLOYEE E1, EMPLOYEE E2
 WHERE E1.MANAGER_ID = E2.EMP_ID(+);

여기서 잠깐!

↓실습문제

-- 1. DEPARTMENT 테이블과 LOCATION 테이블의 조인하여 부서 코드, 부서명, 지역 코드, 지역명을 조회
-- 오라클 구문

-- ANSI 구문


-- 2. EMPLOYEE 테이블과 DEPARTMENT 테이블을 조인해서 보너스를 받는 사원들의 사번, 사원명, 보너스, 부서명을 조회
-- 오라클 구문

-- ANSI 구문


-- 3. EMPLOYEE 테이블과 DEPARTMENT 테이블을 조인해서 인사관리부가 아닌 사원들의 사원명, 부서명, 급여를 조회
-- 오라클 구문

-- ANSI 구문 


-- 4. EMPLOYEE 테이블, DEPARTMENT 테이블, LOCATION 테이블의 조인해서 사번, 사원명, 부서명, 지역명 조회
-- 오라클 구문

-- ANSI 구문


-- 5. 사번, 사원명, 부서명, 지역명, 국가명 조회
-- 오라클 구문

-- ANSI 구문


-- 6. 사번, 사원명, 부서명, 지역명, 국가명, 급여 등급 조회 (NON EQUAL JOIN 후에 실습 진행)
-- 오라클 구문

-- ANSI 구문





















/* 정답
-- 1. DEPARTMENT 테이블과 LOCATION 테이블의 조인하여 부서 코드, 부서명, 지역 코드, 지역명을 조회
-- 오라클 구문

-- ANSI 구문
SELECT D.DEPT_ID, D.DEPT_TITLE, D.LOCATION_ID, L.NATIONAL_CODE
FROM DEPARTMENT D
JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE);

-- 2. EMPLOYEE 테이블과 DEPARTMENT 테이블을 조인해서 보너스를 받는 사원들의 사번, 사원명, 보너스, 부서명을 조회
-- 오라클 구문

-- ANSI 구문
SELECT E.EMP_ID, E.EMP_NAME, E.BONUS, D.DEPT_TITLE
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
WHERE BONUS IS NOT NULL;

-- 3. EMPLOYEE 테이블과 DEPARTMENT 테이블을 조인해서 인사관리부가 아닌 사원들의 사원명, 부서명, 급여를 조회
-- 오라클 구문

-- ANSI 구문 
SELECT E.EMP_NAME, D.DEPT_TITLE, E.SALARY
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
WHERE D.DEPT_ID != 'D1';

-- 4. EMPLOYEE 테이블, DEPARTMENT 테이블, LOCATION 테이블의 조인해서 사번, 사원명, 부서명, 지역명 조회
-- 오라클 구문

-- ANSI 구문
SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE, L.LOCAL_NAME
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
JOIN LOCATION L ON( D.LOCATION_ID = L.LOCAL_CODE);

-- 5. 사번, 사원명, 부서명, 지역명, 국가명 조회
-- 오라클 구문

-- ANSI 구문
SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE, L.LOCAL_NAME, N.NATIONAL_NAME
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
INNER JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
INNER JOIN NATIONAL N ON(L.NATIONAL_CODE = N.NATIONAL_CODE);

-- 6. 사번, 사원명, 부서명, 지역명, 국가명, 급여 등급 조회 (NON EQUAL JOIN 후에 실습 진행)
-- 오라클 구문

-- ANSI 구문
SELECT E.EMP_ID AS "사번", 
       E.EMP_NAME AS "사원명", 
       D.DEPT_TITLE AS "부서명",
       L.LOCAL_NAME AS "근무지역명",
       N.NATIONAL_NAME AS "근무국가명",
       S.SAL_LEVEL AS "급여등급"
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
JOIN NATIONAL N ON(L.NATIONAL_CODE = N.NATIONAL_CODE)
JOIN SAL_GRADE S ON(E.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL);
*/