[MySQL] 두 테이블을 묶는 조인(JOIN)에 대해 알아보자

조인 (JOIN)

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

 

 

일대다 (one to many) 관계의 이해

market_db의 회원테이블과 구매테이블

 

회원테이블의 아이디는 PRIMARY KEY로 지정해서 1개씩만 존재

구매테이블의 아이디는 FOREIGN KEY로, 여러번 구매를 할 수 있음

→ 일대다 관계를 PK-FK 관계라고도 한다

 

 

내부 조인 (INNER JOIN)

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

// INNER JOIN을 그냥 JOIN이라고 써도 INNER JOIN으로 인식한다

 

 

- 구매 테이블에서 ‘GRL’ 아이디를 가진 사람의 회원 정보를 조회하기 위해 회원 테이블을 조인하기

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

 

 

- 내부 조인의 간결한 표현

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 문에서 mem_id를 추출할 때 어느 테이블에서 추출할지 정확하게 표현해야 한다.

     

 

- 위의 코드를 간략하게 하기 위해 별칭(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 B.mem_id = M.mem_id;

 

 

내부 조인의 활용

- 전체 회원의 아이디/이름/구매 제품/주소를 출력하기

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

결과는 잘 출력되었지만 한 번도 구매하지 않은 회원의 정보는 없다

내부 조인은 두 테이블에 모두 있는 내용만 조인되기 때문에 이 경우 외부 조인을 사용해야 한다.

 

 

- 중복된 결과 1개만 출력하기

  ‘사이트에서 한 번이라도 구매한 기록이 있는 회원 추출’

   내부 조인을 사용해 회원을 추출하고, 중복은 필요 없으므로 DISTINCT 문을 활용

SELECT DISTINCT M.mem_id, M.mem_name, M.addr
   FROM buy B
     INNER JOIN member M
     ON B.mem_id = M.mem_id
   ORDER BY M.mem_id;

 

 

 

 

외부 조인 (OUTER JOIN)

한 쪽 테이블에만 데이터가 있어도 결과가 출력된다.

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;

// 왼쪽에 있는 회원 테이블을 기준으로 외부 조인

 

 

 

 

외부 조인의 활용

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

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

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

왼쪽이든 오른쪽이든 한쪽에 들어있는 내용이면 출력된다.

 

 

 

상호 조인 (CROSS JOIN)

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

전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수가 된다

카티션 곱(Cartesian product) 라고도 한다.

대용량의 테스트용 테이블을 만들 때 사용한다.

 

SELECT *
  FROM buy
    CROSS JOIN member;

 

 

 

 

자체 조인(SELF JOIN)

자기 자신과 조인하기 때문에 1개의 테이블을 사용한다.

하나의 테이블에 서로 다른 별칭을 붙여서 조인한다.

 

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

 

 

 

- 자체 조인을 활용하기 위해 직원 테이블을 만들고 데이터 입력하기

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