[SQL] 외부 조인, 상호 조인, 자체 조인

외부 조인

내부 조인은 두 테이블에 모두 데이터가 있어야 결과가 나온다.

'외부 조인'은 한쪽에만 데이터가 있어도 결과가 나온다.

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
    <LEFT : RIGHT : FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
    ON <조인될 조건>
[WHERE 검색 조건] ;

 

내부 조인으로 출력하지 못한

전체 회원의 구매 기록( 구매 기록이 없는 회원의 정보도 함께 ) 출력해보겠다.

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
	FROM member M
	LEFT OUTER JOIN buy B
        ON M.mem_id = B.mem_id
        ORDER BY M.mem_id;

LEFT OUTER은 왼쪽에 있는 회원 테이블을 기준으로 외부 조인한다는 의미로

member M을 왼쪽 테이블로 뜻한다.

( LEFT OUTER JOIN을 줄여서 LEFT JOIN이라고만 써도 된다. )

중간에 물건을 구매하지 않아서 회원 정보는 뜨지만 NULL로 표시가 되는 행이 생겼다.

 

RIGHT OUTER JOIN

으로 동일한 결과를 출력하려면 다음과 같이 단순히 왼쪽과 오른쪽 테이블의 위치만 바꿔주자.

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
	FROM buy B
	RIGHT OUTER JOIN member M
        ON M.mem_id = B.mem_id
        ORDER BY M.mem_id;

오른쪽에 있는 member 테이블을 기준으로 외부 조인을 했다.

 

 

외부 조인의 활용

내부 조인으로 구매한 기록이 있는 회원들의 목록만 추출했었다.

이번에는 회원으로 가입만 하고 구매 기록이 없는 회원을 추출해보겠다.

SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr
	FROM member M
	LEFT OUTER JOIN buy B
        ON M.mem_id = B.mem_id
        WHERE B.prod_name IS NULL
        ORDER BY M.mem_id;

 

FULL OUTER JOIN

왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것이다.

왼쪽이나 오른쪽 한쪽에 들어 있는 내용이면 출력하는 것이다.

 

상호 조인

한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인한다.

그러므로, 상호 조인 결과의 전체 행은 두 테이블의 각 행의 개수를 곱한 개수이다.

SELECT * FROM buy CROSS JOIN member;

 

상호 조인 특징

• ON 구문 사용 불가
• 결과의 내용이 의미가 없다. 랜덤으로 조인하기 때문.
• 상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때.

 

자체 조인

자신이 자신과 조인한다. 그래서 테이블을 하나 사용한다.

SELECT <열 목록>
FROM <테이블> 별칭A
	INNER JOIN <테이블> 별칭B
	ON <조인될 조건>
[WHERE 검색 조건]

 

예제

위의 조직도를 통해 하나의 테이블을 만듭니다.

직원(EMP) - 기본키	직속 상관(MANAGER)	사내 연락처
대표	없음	0000
영업이사	대표	1111
관리이사	대표	2222
정보이사	대표	3333
영업과장	영업이사	1111-1
경리부장	관리이사	2222-1
인사부장	관리이사	2222-2
개발팀장	정보이사	3333-1
개발주임	정보이사	3333-1-1

위의 직원 테이블을 통해

'경리부장'의 직속상관인 관리이사의 사내 연락처를 알고 싶다면

EMP열과 MANAGER 열을 조인해야 한다.

 

일단 직원 테이블을 만들어보겠다.

CREATE TABLE emp_table (emp CHAR(4), manager CHAR(4), phone VARCHAR(8));

INSERT INTO emp_table VALUES('대표', NULL, '0000');
INSERT INTO emp_table VALUES('영업이사', '대표', '1111');
INSERT INTO emp_table VALUES('관리이사', '대표', '2222');
INSERT INTO emp_table VALUES('정보이사', '대표', '3333');
INSERT INTO emp_table VALUES('영업과장', '영업이사', '1111-1');
INSERT INTO emp_table VALUES('경리부장', '관리이사', '2222-1');
INSERT INTO emp_table VALUES('인사부장', '관리이사', '2222-2');
INSERT INTO emp_table VALUES('개발팀장', '정보이사', '3333-1');
INSERT INTO emp_table VALUES('개발주임', '정보이사', '3333-1-1');

 

이제 경리부장의 상관의 연락처를 알아보겠다.

SELECT A.emp "직원", B.emp "직속상관", B.phone "직속상관연락처"
    FROM emp_table A
    INNER JOIN emp_table B
    ON A.manager = B.emp
WHERE A.emp='경리부장';

 

아래와 같은 그림과 같이 두 개의 테이블이 조인되는 것처럼 구성한 것이다.

이것이 자체 조인이다.

 

모든 내용은 "혼자 공부하는 SQL" 교재를 통해 학습한 후, 정리한 글입니다.