[혼공] 4-2장 두 테이블을 묶는 조인

조인(join)

- 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것

 

1. 내부조인 (inner join)

 

- 테이블이 일대다(one to many)관계로 연결되어야 함

- 일반적으로 조인이라 부르는것은 내부조인

- 내부 조인은 두 테이블에 모두 있는 내용만 조인되는 방식

- 내부 조인은 두 테이블에 모두 데이터가 있어야만 결과가 나옴

 

- 일대다 관계

 - 한쪽 테이블에는 하나의 값만 존재해야하지만, 연결된 다른 테이블에는 여러 개의 값이 존재할 수 있는 관계

 

ex) 

- 회원 테이블 에서 'BLK'는 1명밖에 없음 

- 회원 테이블의 아이디를 기본키(primary key)로 지정

- 구매 테이블의 아이디에서는 3개의 BLK 찾을 수 있음

- 회원은 1명이지만 구매는 여러번(many)할 수 있다

- 구매 테이블의 아이디는 외래키(Foreign Key)로 설정

 

- 내부 조인의 형식

SELECT <열 목록>
FROM <첫 번째 테이블>
    INNER JOIN <두 번째 테이블>
    ON <조인될 조건>
[WHERE 검색 조건]

 

ex) 구매 테이블에서 GRL이라는 아이디를 가진 사람이 구매한 물건을 발송하기 위해 회원 테이블과 조인하여

이름/주소/연락처 등을 검색

USE market_db;
SELECT *
    FROM buy
    INNER JOIN member
    ON buy.mem_id = member.mem_id
WHERE buy.mem_id = 'GRL';

 

- 내부 조인 과정

 

 1) 구매 테이블의 buy.mem_id인 'GRL'추출

 2) 'GRL'와 동일한 값을 회원 테이블의 member.mem_id열에서 검색

 3) 'GRL'이라는 아이디를 찾으면 구매 테이블과 회원 테이블의 두 행을 결합(JOIN)

 

- 내부 조인의 간결한 표현

SELECT buy.mem_id, mem_name, prod_name,addr, CONCAT(phone1,phone2) '연락처'
	FROM buy
        INNER JOIN member
        ON buy.mem_id = member.mem_id;

- 열 이름에도 모두 테이블_이름.열_이름 형식으로 작성

SELECT buy.mem_id, member.mem_name, buy.prod_name,member.addr, CONCAT(member.phone1,member.phone2) '연락처'
	FROM buy
        INNER JOIN member
        ON buy.mem_id = member.mem_id;

- FROM 절에 나오는 테이블의 이름 뒤에 별칭(alias)을 줄 수 있음

SELECT B.mem_id, M.mem_name, B.prod_name,M.addr, CONCAT(M.phone1,M.phone2) '연락처'
	FROM buy B
        INNER JOIN member M
        ON buy.mem_id = member.mem_id;

 

 

2. 외부 조인 (outer join)

 

- 두 테이블을 조인할 때 필요한 내용이 한쪽 테이블에만 있어도 결과 추출 가능

 

- 외부 조인 형식

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

 

ex) 

전체 회원의 구매 기록(구매 기록이 없는 회원의 정보도 포함)출력
 - 왼쪽에 있는 회원 테이블을 기준으로 외부 조인

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 JOIN 

- 왼쪽 테이블의 내용은 모두 출력되어야함

RIGHT OUTER JOIN

- 오른쪽 테이블의 내용은 모두 출력되어야함

FULL OUTER JOIN 

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

 

ex)

회원으로 가입만 하고, 한 번도 구매한적 없는 회원의 목록 추출

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;

 

3. 상호 조인 (cross join)

 

- 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능

- 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수

 (회원 테이블의 10개 행 * 구매 테이블의 12개 행 = 120)

 

SELECT *
   FROM buy
    	CROSS JOIN member;

특징

 1) ON 구문 사용 불가 

 2) 랜덤으로 조인하기 때문에 결과의 내용 의미 없음

 3) 상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때

 

 

4. 자체 조인 (self join)

 

- 자신이 자신과 조인

- 1개의 테이블 사용

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

 

ex) 회사의 조직 관계

- 관리 이사는 직원이므로 직원열(EMP - PK)에 속함

- 관리 이사는 동시에 경리부장과 인사부장의 직속상관이어서 직속상관열(MANAGER)에도 속함

- 경리부장 직속상관의 연락처를 알고싶음

 

USE market_db;
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 = '경리부장';