두 테이블을 묶는 조인

서론 - 조인이란

두개의 테이블을 묶어서 하나의 결과를 만들어내는 것. 인터넷 마켓 데이터베이스의 회원테이블과 구매 테이블을 예로 들 수 있다.

회원테이블에는 회원의 이름과 연락처가 있고. 구매 테이블에는 회원이 구매한 물건이 있다. 물건을 배송하려면 구매 테이블의 회원이 구매한 물건에 대한 정보가 함께 필요하다. 이렇게 두 테이블을 엮어서 하나의 배송을 위한 정보를 추출하는 것이 대표적인 조인이다.

내부조인

두 테이블을 연결할 때 가장 많이 사용되는 것이다. 실무에서 그냥 조인을 말하는 것은 대부분 내부조인이다

일대다 관계의 이해

두 테이블의 조인을 위해서는 테이블이 **일대다 관계(one to many)**로 연결되어야 한다.

데이터베이스의 테이블은 하나로 구성되는 것 보다는 여러 정보를 주제에 따라 분리해서 저장하는 것이 효율적이다. 이 분리된 테이블은 관계를 맺고 있다.

예를들어 market_db에서 회원 테이블의 아이디와 구매 테이블의 아이디는 일대다 관계이다. 왜냐하면 회원 한명이 여러개의 구매를 가질 수 있기 때문이다. 회원테이블에서 블랙핑크의 아이디는 ‘BLK’로 1명(1, one) 밖에 없다. 그래서 회원 테이블의 pk로 지정 하였다. 구매 테이블의 아이디에서는 3개의 ‘BLK’를 찾을 수 있다. 즉 회원은 1명이지만 회원은 구매를 여러번 할 수 있다. 그래서 구매 테이블의 아이디는 기본키가 아닌 외래키(fk)로 지정하였다.

다른 예로 회원테이블과 급여 테이블, 학생 테이블과 학점 테이블도 같은 원리로 일대다 관계이다.

<aside> 💡 두 테이블의 내부조인을 위해서는 기본키 - 외래키 관계로 맺어져야한다

</aside>

내부 조인의 기본

형식

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

구매 테이블에서 GRL이라는 아이디를 가진 사람이 구매한 물건을 발송위함

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

만약 where절을 삭제한다면 모든 행과 조인된다.

내부 조인의 간결한 표현

아이디, 이름, 구매 물품, 주소, 연락처만 추출

SELECT mem_id, mem_name, prod_name, addr, CONCAT(phone1, phone2) '연락처' --> 에러!!
	FROM buy
		INNER JOIN member
		ON buy.mem_id = member.mem_id

여기서 에러가 나는 이유는 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

근데 이러면 코드가 너무 기니까 줄여보자

SELECT B.mem_id, M.mem_name, B.prod_name, M.member_addr, CONCAT(M.phone_number1, M.phone_number2) '연락처'
	FROM buy B --> 별칭
		INNER JOIN member M --> 별칭
		ON B.member_id = M.member_id;

내부 조인의 활용

전체 회원의 아이디, 이름, 구매한 제품, 주소 출력

SELECT B.mem_id, M.mem_name, B.prod_name, M.member_addr, CONCAT(M.phone_number1, M.phone_number2) '연락처'
	FROM buy B
		INNER JOIN member M
		ON B.member_id = M.member_id
	ORDER BY M.mem_id;

결과는 아무 이상 없지만 전체 회원이 아닌 구매한 기록이 있는 회원들의 목록이 나온다. 구매 테이블과 회원테이블을 내부조인을 했으니까 구매한 회원 테이블의 회원들이 나온것이다. 구매하지 않은 회원들도 나오게 하려면 외부조인을 사용해야한다.

외부조인

내부조인은 두 테이블의 모두 데이터가 있어야 나오지만 외부조인은 한쪽에만 데이터가 있어도 모든 결과가 나온다

외부조인의 기본

형식

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 JOIN 쿼리문의 의미는 왼쪽에 있는 내용은 모두 출력되어야 정도로 이해하면 된다 여기선 회원테이블의 내용은 전부 출력된다.

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;

외부 조인의 활용

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

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

왼쪽이든 오른쪽이든 한쪽에 들어있는 내용이면 출력한다. 잘 사용하지 않는다

기타 조인

내부조인이나 외부조인처럼 자주 사용되진 않지만 가끔 유용하게 사용되는 조인들

상호조인

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

예를 들어 회원테이블의 행 하나는 구매테이블의 모든 행과 조인된다. 즉 회원테이블과 구매 테이블의 행의 개수가 12개라면 12 X 12 개의 행이 완성된다.

상호조인 하는법

SELECT *
FROM buy
CROSS JOIN member;

상호 조인의 특징

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

대용량 데이터 만들어보기

CREATE TABLE cross_table

SELECT *
FROM sakila.actor -- 200건
	CROSS JOIN world.country -- 239건

SELECT *
FROM cross_table

자체 조인

다른 조인들은 모두 2개의 테이블을 조인하지만 자체 조인은 자신과 조인한다는 의미

별도의 문법이 있는 것은 아니고 그냥 테이블 하나로 조인하면 된다. 실무에서 자체 조인을 많이 사용하진 않지만 대표적인 사례로 회사의 조직 관계를 살펴볼 수 있다.

관리이사는 직원이자 경리부장과 인사부장의 상관이어서 직속 상고나 열에도 속한다. 만약 직원 중 경리부장의 직송 상관인 관리 이사의 사내 연락처를 알고싶을 때 사용한다.

즉 이런 트리 구조일때 사용한다.

자체 조인의 형식

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

자체 조인의 활용

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

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