데이터베이스(OUTER JOIN)

◎ 외부조인(OUTER JOIN)

- INNER JOIN + 외부에 남아있는 테이블의 내용 을 포함하는 것에 대한 질의 표현
- 내부조인이 두 테이블에서 조인 컬럼 값이 같은 경우를 조회하는 것이라면, 외부조인은 두 테이블 중 한 테이블의 조인 컬럼 값이 없더라도(null) 없는 경우의 데이터까지 모두 포함해 조회(질의)하는 조인이다.

- 조인하는 여러테이블에서 한 쪽에는 데이터가 있고, 한 쪽에는 데이터가 없는 경우, 데이터가 있는 쪽 테이블의 내용을 모두 출력하는 것
-> 조건에 맞지 않아도 해당하는 행을 출력하고 싶을 때 사용

 

LEFT OUTER JOIN : 무조건 왼쪽 테이블의 값이 모두 출력
- 왼쪽에 조인 컬럼 값이 없는 경우(NULL)를 포함

- 조인문의 왼쪽에 있는 테이블의 모든 결과를 가져 온 후 오른쪽 테이블의 데이터를 매칭하고, 매칭되는 데이터가 없는 경우 NULL로 표시
- 오른쪽 테이블(두 번째 테이블)의 조인 컬럼에 데이터가 없어서 왼쪽 테이블의 조인 컬럼에 NULL값이 추가되는 레코드를 포함하여 가져와라
ex) 아직 소속된 부서가 없는 '왕건' 사원을 포함하여 모든 사원을 구하는데 활용

RIGHT OUTER JOIN : 무조건 오른쪽 테이블의 값이 모두 출력
- 오른쪽에 조인 컬럼 값이 없는 경우(NULL)를 포함

- 조인문의 오른쪽에 있는 테이블의 모든 결과를 가져온 후 왼쪽의 테이블의 데이터를 매칭하고, 매칭되는 데이터가 없는 경우 NULL을 표시

 

(UNION)FULL OUTER JOIN : 두  가지 경우를 합함, 전체 출력

- 양쪽 모두 조건이 일치하지 않는 것까지 모두 결합해 출력

 

* 두 테이블의 조인을 위해서는 기본키(PRIMARY KEY, PK)와 외래키(FOREIGN KEY, FK) 관계로 맺어져야 하고, 이를 일대다 관계라고 한다

INNER JOIN은 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.
OUTER JOIN은 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다.

---

▷ 1. 오라클(outer join)

- 두 번째 테이블(or 첫 번째 테이블)에 데이터가 없어서 LEFT(or RIGHT) JOIN의 결과가 나타난다
- 구하고자 하는 조인 컬럼이 NULL인 쪽에 '(+)'를 붙인다
- (+)의 의미는 두 번째 테이블에 테이터가 없으니 이것도 가져와라
[구문]
1) LEFT

SELECT 
        t1.*, t2.*
    FROM 
        테이블1 t1,
        테이블2 t2
    WHERE
        t1.조인컬럼 = t2.조인컬럼 (+)
    ORDER BY
        t1.정렬컬럼;

2) RIGHT

SELECT 
        t1.*, t2.*
    FROM 
        테이블1 t1,
        테이블2 t2
    WHERE
        t1.조인컬럼 (+) = t2.조인컬럼
    ORDER BY
        t1.정렬컬럼;

---

 

▷ 2. ANSI(outer join)

- 데이터가 전부 포함되어야 하는 쪽을 기준으로 'LEFT JOIN', 'RIGHT JOIN' 표시

- ON' 다음에는 기준이 되는 컬럼의 관계를 설정

[구문]

SELECT 
        t1.*, t2.*
    FROM 
        테이블1 t1
      LEFT/RIGHT JOIN
        테이블2 t2
       ON t1.조인컬럼 = t2.조인컬럼
    WHERE
        제한조건
    ORDER BY
        t1.정렬컬럼;

-> INNER JOIN의 INNER부분을 LEFT or RIGHT로 변경

---

▷ 3. FULL (OUTER) JOIN
오라클에서는 지원이 안됨
outer-join된 테이블은 1개만 지정할 수 있습니다
-> ANSI구문으로 작성

SELECT 
    t1.*, t2.*
  FROM 
    테이블1 t1
    FULL OUTER JOIN 
      테이블2 t2
    ON 테이블1.컬럼 = 테이블2.컬럼;

---

예시)

1. LEFT JOIN 적용

ㄱ. 부서가 없는 직원 자료 추가

-- LEFT JOIN용 자료 추가(부서를 배정 받지 않은 신입)
INSERT INTO TBL_EMPLOYEE
	( emp_id, emp_name, gender, age, 
	hire_date, dept_id, address_id)
	VALUES ( 6, '왕건', '남성', 35, 
			to_date('2023-07-01', 'YYYY-MM-DD'), NULL, 4); 
-- 확인            
SELECT * FROM TBL_EMPLOYEE te WHERE EMP_ID = 6;

ㄴ. LEFT OUTER JOIN으로 검색하기('왕건'사원이 포함)

-- 1. 오라클(LEFT JOIN)
-- 부서가 없는 '왕건' 사원의 정보를 포함하여 출력
SELECT 
	te.*, td.*
	FROM
		TBL_EMPLOYEE te,
		TBL_DEPT td
	WHERE 
		te.DEPT_ID = td.DEPT_DESC (+) AND te.GENDER = '남성'
	ORDER BY 
		te.EMP_ID;
	
		
-- 2. ANSI(LEFT JOIN)
SELECT 
	te.*, td.*
	FROM
		TBL_EMPLOYEE te
	  LEFT JOIN 
		TBL_DEPT td
	  ON te.DEPT_ID = td.DEPT_DESC 
	WHERE 
		te.GENDER = '남성'
	ORDER BY 
		te.EMP_ID;

부서 정보가 없는 직원까지 추출

---

2. RIGHT JOIN 적용

ㄱ. 오른쪽에만 포함된 자료(부서) 추가

-- RIGHT JOIN용 자료 추가	
-- 오른쪽(부서테이블)에만 포함된 자료 질의
INSERT INTO TBL_DEPT (dept_id, dept_name)
	VALUES (5, '인사팀');
-- 확인
SELECT * FROM TBL_DEPT;

ㄴ. RIGHT OUTER JOIN으로 검색하기

-- 사원-부서 정보 테이블
-- 1. 오라클(RIGHT JOIN)
SELECT 
	te.EMP_ID, te.EMP_NAME,
	td.DEPT_ID, td.DEPT_NAME 
	FROM 
		TBL_EMPLOYEE te,
		TBL_DEPT td
	WHERE 
		te.DEPT_ID (+) = td.DEPT_ID
	ORDER BY
		te.EMP_ID;
-- 2. ANSI(RIGHT JOIN)	
SELECT 
	te.EMP_ID, te.EMP_NAME, te.DEPT_ID,
	td.DEPT_ID, td.DEPT_NAME
	FROM 
		TBL_EMPLOYEE te
	  RIGHT JOIN 
	  	TBL_DEPT td
	  ON te.DEPT_ID = td.DEPT_ID
	ORDER BY 
		te.EMP_ID;

직원 정보가 없는 부서까지 출력

---

3. FULL OUTER JOIN 적용

- 부서가 정해지지 않은 사원도 포함이고 소속된 사원이 없는 부서도 포함하여 레코드 출력

-- ANSI(FULL OUTER JOIN)
SELECT 
	te.*, td.*
	FROM 
		TBL_EMPLOYEE te
	  FULL OUTER JOIN
		TBL_DEPT td
		ON
			te.DEPT_ID = td.DEPT_ID
	ORDER BY 
		te.EMP_ID;

전체 출력

 

 

* 내부조인 참고

2023.08.01 - [백엔드/데이터베이스] - 데이터베이스(INNER JOIN)

데이터베이스(INNER JOIN)