choice blog

JOIN(만나다)

두 개 이상의 테이블을 연결해서 데이터를 검색하는 방법
보통 두개이상의 행(ROW)들의 공통된 값 기본키(PRIMARY KEY), 외래키를 사용해서 JOIN 한다.
    
현재 테이블의 외래키(model F)를 통해 상대 테이블의 기본키(model P & U)에 연결
    
목적 : 현재 테이블에 없는 값을 상대 테이블의 정보(칼럼)에서 산출하기 위한 것.
      
기본키(Primary key) : 테이블에서 중복되지 않는 키 (model P)
외래키(Foreign key) : 다른 테이블에서 기본키이거나 고유키인 경우가 많다. (model F)

JOIN 종류

inner join = 교집합  ★★★★★ (basic)

full outer join = 합집합

cross join      
outer join ★★★   
        left
        right
self join ★★★★★   

형식

ANSI문법

 SELECT 검색컬럼명

 FROM 테이블명1 별명1 JOIN 테이블명2 별명2

 ON 별명1.외래키 = 별명2.기본키(OR 고유키);

오라클문법

 SELECT 검색컬럼명

 FROM 테이블명1 별명1, 테이블명2 별명2

 WHERE 별명1.외래키 = 별명2.기본키(OR 고유키);

inner join : 기준테이블과 대상 테이블에 매칭하는 필드값이 있는 경우에 검색한다.

참고

 2개이상의 테이블도 JOIN 가능하다.

 ANSI문법 사용시 FROM절에 INNER 생략 가능하다.

 별명은 양쪽 다 가지고 있지 않는 컬럼일 경우 생략가능하다.

예시

-- hr 스키마

-- ansi SQL (표준, MySQL도 가능)
-- 목적 : 부서명 얻기(d.department_name )

SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name 
FROM employees e INNER JOIN departments d
    ON e.department_id = d.department_id; -- 연결
    
    
-- oracle   (위와 결과값이 같음)

SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name 
FROM employees e, departments d
WHERE e.department_id = d.department_id; -- 같을때 연결하라


-- 목적 : JOB_ID == IT_PROG 업무명을 꺼내고 싶다
-- 데이터를 가지고 가서 맞춰보고 상대방의 정보를 가져올 수 있다.

SELECT e.employee_id, e.first_name, e.job_id,
       j.job_id, j.job_title 
FROM employees e, jobs j
WHERE e.job_id = j.job_id
AND e.job_id = 'IT_PROG';


-- 3개 테이블을 조인
-- NULL은 JOIN 불가!

SELECT e.employee_id, e.first_name,
       d.department_name , j.job_title
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
AND e.employee_id = 101;

cross join : 2개의 테이블을 아무 조건없이 연결시키는 방법(ALL JOIN)

참고

 실행시 1번테이블 * 2번테이블 결과값을 가져온다.

 거의 사용하지 않는다.

예시

-- ansi

SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name 
FROM employees e CROSS JOIN departments d;


-- oracle

SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e, departments d;

full outer join : 기준테이블과 대상 테이블에 매칭하는 필드값이 있는 경우 모든 필드값으로 테이블을 만들어서 데이터를 검색하는 방법

참고

 data 전체를 뽑을 때 (간혹 사용)

 결과값 : left outer join + right outer join

예시

-- ansi

SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

/* oracle : 결론적으론 안됨 == error.
SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id(+); -- left
*/

-- 굳이 사용하겠다면 UNION 사용.
SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+) -- left join
UNION
SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id; -- right join


-- e.department_id, d.department_id null 가져와라
-- ansi

SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL
OR d.department_id IS NULL;


-- oracle

SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
AND e.department_id IS NULL
UNION
SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
AND e.department_id IS NULL;

outer(외부) join : 기준테이블과 대상 테이블에 매칭하는 필드값이 있는 경우 동일한 필드값이 없는 행도 검색하는 방법

left join(left outer join) : 좌측  테이블(기준 테이블)의 모든 필드값을 가져온다.

참고

 실행시 e.department_id의 null의 값도 가져오게 된다.

예시

-- ansi

SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;


-- oracle

SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);


-- 차집합 (e.department_id의 NULL을 가져와라)

SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
AND e.department_id IS NULL;

right join(right outer join) : 우측 테이블(대상 테이블)의 모든 필드값을 가져온다.

참고

 실행시 d.department_id의 null의 값도 가져오게 된다.

예시

-- ansi

SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;


-- oracle

SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;


-- 차집합 (d.department_id NULL을 가져와라)

SELECT e.employee_id, e.first_name, e.department_id,
       d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
AND e.department_id IS NULL;

self join : 자기 자신과 join 한다.

참고

 테이블 내 동일한 값이 다른 의미를 가지는 즉, 다른 컬럼에 존재하는 경우 

예시

SELECT emp.employee_id, emp.first_name, emp.manager_id, 
       mgr.employee_id, mgr.first_name
FROM employees emp, employees mgr -- emp:사원, mgr:상사
WHERE emp.manager_id = mgr.employee_id;


-- null값까지 뽑기(self join + left outer join)

SELECT emp.employee_id, emp.first_name, emp.manager_id, 
       mgr.employee_id, mgr.first_name
FROM employees emp, employees mgr -- emp:사원, mgr:상사
WHERE emp.manager_id = mgr.employee_id(+);