[혼자공부하는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 검색 조건]