[오라클] 조인(JOIN) 정리

조인(JOIN)

• 한 개 이상의 테이블에서 원하는 결과를 얻기 위한 조인을 학습
• 한 개 이상의 테이블에서 데이터를 조회하기 위해서 사용
• SQL에서는 두 개 이상의 테이블을 결합해야만 원하는 결과를 얻을 수 
• 있을 때 한 번의 질의로 원하는 결과를 얻을 수 있는 조인 기능 제공
• where절에 명시하는 조건이 from 절에 명시한 여러 Table을 묶는 join 조건이 됨
•  join 조건은 반드시 묶어야 할 table 수보다 하나가 적음
• table 수가 n개라면 join 조건은 n-1이 됨

Equi join	동일 칼럼을 기준으로 조인(inner join, simple join)
NonEqui Join	동일 칼럼이 없이 다른 조건을 사용하여 조인
Outer Join	조인 조건에 만족하지 않는 행도 나타낸다
Self Join	한 테이블 내에서 조인한다.

기본적으로 다음과 같은 규칙을 준수

•  PRIMARY KEY와 FOREIGN KEY 컬럼을 통한 다른 테이블의 행과 연결
• 연결 KEY 사용으로 테이블과 테이블 결합
•  WEHRE 절에서 조인 조건을 사용. (조인 조건 개수 = 연결 테이블 수 - 1)
• 명확성을 위해 칼럼 이름 앞에 테이블명 또는 테이블 별칭 

조인이 수행될 때는 두개 이상의 테이블이 사용되는데 이때 둘 중 하나의 테이블을 먼저 읽고
조인 조건 절을 확인하여 나머지 테이블에 가서 데이터를 가져오게 된다.
이 때 먼저 읽는 테이블을 선행 테이블(driving table 또는 inner table) 이라고 하고 뒤에 읽는
테이블을 후행 테이블(driven table  또는   Outer table) 이라고 한다. 그리고 선행 테이블은
조회할 데이터가 적은 테이블로 선택해야 속도면에서 유리 하다.

EMPLOYEES 테이블과 DEPARTMENTS 테이블의 공통 칼럼인 DEPARTMENT_ID의 값이 일치(=)되는
조건을 WHERE 절에 사용한다. 두 테이블을 조인하려면 일치되는 공통 칼럼을 사용해야 한다. 

1. Equi join(inner join)

오라클 전용 select 컬럼 from 테이블명, 테이블명 where join 조건문
EX)  사원명, 부서이름을 조회하시오

SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;

 
공통 컬럼 조회 시
EX) 사원명, 부서이름, 부서ID를 조회하시오

SELECT FIRST_NAME, DEPARTMENT_NAME, DEPARTMENT_ID  /  명시 없이 양쪽 공통 컬럼을 조회하면 오류
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;

> 오류 :  ORA-00918: 열의 정의가 애매합니다
 
공통 컬럼을 조회할 때는 반드시 조회하고자 하는 테이블명. 컬럼 또는 테이블 별칭.컬럼으로 명시.

테이블명. 컬럼

SELECT FIRST_NAME, DEPARTMENT_NAME, EMPLOYEES.DEPARTMENT_ID
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;

테이블별칭.컬럼

SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, E.DEPARTMENT_ID
FROM EMPLOYEES E, DEPARTMENTS D   > 별칭 설정
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

 

• 조인한 결과를 살펴 보면 부서번호를 기준으로 같은 값을 가진 사원 테이블과 부서 테이블이 결합.
• 부서 테이블의 Primary Key인 부서번호가 사원 테이블의 Foreign Key로 설정 됨
• 연결 Key를 WHERE 절에서 조인 조건에 사용함.
• 비교 연산자로 "=" 사용했으므로 Equi join(inner join)

 
EX) INNER JOIN 예제
사원명, 직무id, 직무명(Job_title), 부서번호, 부서명 출력 하시오

SELECT E.FIRST_NAME, E.job_id, J.JOB_TITLE, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, JOBS J 
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND E.JOB_ID = J.JOB_ID;

EX) INNER JOIN 예제 CASE

1. 부서별에 따라 급여를 인상하도록 하자. (직원번호, 직원명, 직급, 급여)
2. 부서명이 'Marketing'인 직원은 5%, 'Purchasing'인 사원은 10%, 'Human Resources'인 사원은 15%, 'IT'인 직원은 20%인 인상한다.

SELECT EMPLOYEE_ID, FIRST_NAME, E.DEPARTMENT_ID, JOB_ID, SALARY,
    CASE DEPARTMENT_NAME WHEN 'marketing' THEN SALARY*1.05
                         WHEN 'purchasing'  THEN SALARY*1.10
                         WHEN 'human resources' THEN SALARY*1.15
                         WHEN 'IT' THEN SALARY*1.20
         ELSE SALARY
    END UPSAL   
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY EMPLOYEE_ID;

 

2. Non-Equi Join

• Non-Equi Join은 칼럼의 값이 직접적으로 일치하지 않을 시 " = "을 

EX1-1) 급여 등급 테이블(SALARYGRADE)을 생성

CREATE TABLE SALARYGRADE(
    GRADE NUMBER,
    MINSALARY NUMBER,
    MAXSALARY NUMBER
);

INSERT INTO SALARYGRADE(GRADE, MINSALARY, MAXSALARY)
VALUES(1, 2000,3000);
INSERT INTO SALARYGRADE(GRADE, MINSALARY, MAXSALARY)
VALUES(2, 3001,4500);
INSERT INTO SALARYGRADE(GRADE, MINSALARY, MAXSALARY)
VALUES(3, 4501,6000);
INSERT INTO SALARYGRADE(GRADE, MINSALARY, MAXSALARY)
VALUES(4, 6001,8000);
INSERT INTO SALARYGRADE(GRADE, MINSALARY, MAXSALARY)
VALUES(5, 8001,10000);
INSERT INTO SALARYGRADE(GRADE, MINSALARY, MAXSALARY)
VALUES(6, 10001,13000);
INSERT INTO SALARYGRADE(GRADE, MINSALARY, MAXSALARY)
VALUES(7, 13001,20000);
INSERT INTO SALARYGRADE(GRADE, MINSALARY, MAXSALARY)
VALUES(8, 20001,30000);

• 급여 등급을 8개로 나누어 놓은 salarygrade 테이블에서 정보를 얻어 와서 각 사원의 급여등급을 지정.
• 이를 위해서는 employees 테이블과 salarygrade 조인해야함

EX 1-2) 사원명, 급여, 등급을 출력해주세요

select E.FIRST_NAME, E.SALARY, S.GRADE
FROM EMPLOYEES E, SALARYGRADE S
WHERE E. SALARY BETWEEN S.MINSALARY AND S.MAXSALARY;

 
 
EX 1-3) 

SELECT E.FIRST_NAME, E.SALARY, S.GRADE
FROM EMPLOYEES E, SALARYGRADE S
WHERE E.SALARY >= S.MINSALARY AND E.SALARY <= S.MAXSALARY;

 

3. Outer Join

부서 테이블의 120번 부서와 조인할 직원 테이블의 부서번호가 없지만,
120번 이상의 부서도 출력되도록 하려면 Outer Join을 사용해야함.
Outer Join을 하기 위해서 사용하는 기호는(+)이며 조인 조건에서 정보가 부족한 컬럼명 뒤에 위치하게 해야함.
즉, 사원 테이블에 부서번호 120번 이상의 부서번호가 없기 때문에 E.DEPARTMENT_ID(+)쪽에 + 덧붙임

EX)

SELECT E.FIRST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME 
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID;

 
EX) Outer Join 예제 BETWEEN

1. 2007년도 상반기에 입사한 사원번호, 사원명, 부서명을 구하시오

SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+) AND HIRE_DATE BETWEEN '2007/01/01' AND '2007/06/30';

 

4. Self Join

• EMPLOYEES 테이블에 별칭을 사용하여 하나의 테이블을 두 개의 테이블인 것처럼 사용하려면
• WORK(사원 테이블)과 MANAGER(매니저 테이블로)로 별칭 부여.

EX 1-1 ) Self Join 예제
사원명과 사원의 매니저(상사), 이름을 출력하기

SELECT WORK.FIRST_NAME 사원명, MANAGER.FIRST_NAME 매니저명
FROM EMPLOYEES WORK, EMPLOYEES MANAGER
WHERE WORK.MANAGER_ID = MANAGER.EMPLOYEE_ID;

사원테이블

SELECT EMPLOYEE_ID, FIRST_NAME, MANAGER_ID
FROM EMPLOYEES;

관리자 테이블(관리자 번호가 사원번호이므로 관리자 사원번호, 관리자명)

SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES ORDER BY EMPLOYEE_ID;

EX 1-2)

SELECT RPAD(WORK.FIRST_NAME, 11,' ') || '의 매니저는 ' || MANAGER.FIRST_NAME || '이다.' AS "그 사원의 매니저"
FROM EMPLOYEES WORK, EMPLOYEES MANAGER
WHERE WORK.MANAGER_ID = MANAGER.EMPLOYEE_ID;

 

 

 * ANSI CROSS JOIN

-- ANSI (미국표준연구소) SQL은 대부분의 상용 데이터베이스 시스템에서 표준 언어이다.
-- 다른 DBMS와의 호환성을 위해서는 ANSI 조인을 사용하는 것이 좋다
 
-- ANSI INNER JOIN
-- 앞서 배운 조인 구문 중 공통 칼럼을 '='(EQUAL) 비교연산자를 통해 같은 값을 가지는
-- 로우를 연결하는 형태이나 ANSI INNER JOIN은 다음과 같은 형식으로 작성
 

•  JOIN만 작성 시 기본값은 INNER JOIN

EX) INNER JOIN 

1. 사원테이블과 직무테이블 => 공통컬럼 : JOB_ID
2. 사원테이블과 부서테이블 => 공통컬럼: DEPARTMENT_ID
3. 사원명, 직무ID, 직무명(JOB_TITLE), 부서번호, 부서명을 출력해주세요

SELECT E.FIRST_NAME 사원명, E.JOB_ID 직무ID, J.JOB_TITLE 직무명, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E INNER JOIN JOBS J ON E.JOB_ID = J.JOB_ID
    INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

 
* 연결에 사용하려는 컬럼 명이 같은경우 USING() 사용, 다른경우 ON() 사용

SELECT EMPLOYEES.FIRST_NAME, DEPARTMENTS.DEPARTMENT_NAME
FROM EMPLOYEES INNER JOIN DEPARTMENTS
USING(DEPARTMENT_ID);

*  조인의 조건과 데이터 검색을 위한 조건 부여

SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES INNER JOIN DEPARTMENTS
ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
WHERE FIRST_NAME = 'Susan';