BE
[혼자공부하는SQL] 10강
04-2. 두 테이블을 묶는 조인
0. 시작하기 전에
조인(join) : 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어내는 것
예시
: 회원 테이블에는 회원의 이름과 연락처가 있고, 구매 테이블에는 회원이 구매한 테이블이 있다. 물건을 배송하려면, 회원 테이블의 회원 이름과 연락처, 구매 테이블의 회원이 구매한 물건에 대한 정보가 함께 필요하다.
1. 내부 조인
조인이라 부르면, 내부 조인을 의미한다.
1) 일대다 관계의 이해 (one to many)
(1) 예시
일대다 관계 = PK-FK 관계
(2) 기본키 / 외래키
기본키 (Primary key)
외래키 (Foreign key)
2) 내부 조인의 기본
(1) 내부 조인의 형식
SELECT <열 목록>
FROM <첫 번째 테이블>
INNER JOIN <두 번째 테이블>
ON <조인될 조건>
[WHERE 검색 조건]INNER JOIN을 JOIN이라고만 써도 INNER JOIN으로 인식한다.
(2) 예시
-- GRL 멤버가 구입했던 물건들에 대한 정보 + GLS 멤버 정보를 JOIN한 결과
USE market_db;
SELECT *
FROM buy
INNER JOIN member
ON buy.mem_id = member.mem_id
WHERE buy.mem_id = 'GRL';buy 테이블에서 검색하는데, member 테이블에 JOIN하여 검색한다.
(3) 특정 열에 대한 지정
-- 오류 발생
SELECT mem_id, mem_name, prod_name, addr, CONCAT(phone1, phone2) AS '연락처'
FROM buy
INNER JOIN member
ON buy.mem_id = member.mem_id;
-- 해결
SELECT buy.mem_id, mem_name, prod_name, addr, CONCAT(phone1, phone2) AS '연락처'
FROM buy
INNER JOIN member
ON buy.mem_id = member.mem_id;3) 테이블의 별칭
SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(M.phone1, M.phone2) AS '연락처'
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;4) 내부 조인의 한계
: 구매한 사람들만 나왔다. 구매하지 않은 경우도 봐야 하는데 말이다. 구매 안 했으면 구매한 적 없다고 해야 하지 않을까? → 이것이 바로 외부 조인
2. 외부 조인 (Outer JOIN)
내부 조인은 두 테이블에 모두 데이터가 있어야만 결과가 나온다.
하지만, 외부 조인은 한 쪽에만 데이터가 있어도 결과가 나온다.
1) 외부 조인의 기본
외부 조인은 두 테이블을 조인할 때 필요한 내용이 한쪽 테이블에만 있어도 결과를 추출할 수 있다.
(1) 형식
SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
<LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
ON <조인될 조건>
[WHERE 검색 조건](2) 예시
-- LEFT JOIN
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;
-- RIGHT 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;2) FULL OUTER JOIN
왼쪽 외부 요인과 오른쪽 외부 조인이 합쳐진 것
왼쪽이든 오른쪽이든 한 쪽에 들어 있는 내용이면 출력한다.
3. 기타 조인
1) 상호 조인 (CORSS JOIN)
한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능
그래서 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수가 된다.
카티션 곱(cartesian product)이라고도 부른다.
(1) 형태
SELECT *
FROM 테이블
CROSS JOIN 테이블;(2) 예시
SELECT *
FROM buy
CROSS JOIN member;(3) 특징
ON 구문을 사용할 수 없다.
결과의 내용은 의미가 없다. 랜덤으로 조인하기 때문이다.
상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때이다.
2) 자체 조인 (Seft JOIN)
자체조인은 자신이 자신과 조인한다는 의미이다.
그래서 자체 조인은 1개의 테이블을 사용한다.
별도의 문법이 있는 것은 아니고, 1개로 조인하면 자체 조인이 된다.
(1) 예시
: 조직도에서 직속 상관에 대한 열을 작성해보자. 경리부장의 직속상관인 관리이사의 연락처를 알고 싶다.
경리부장의 직속상관을 안다.
직속상관의 연락처를 안다.
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 = '경리부장';(2) 형식
SELECT <열 목록>
FROM <테이블> 별칭A
INNER JOIN <테이블> 별칭 B
ON <조인될 조건>
[WHERE 검색 조건]