[SQL] 조인

조인이란?

조인은 두 테이블을 엮어서 정보를 추출하는 것을 말한다.

 

 

 

내부 조인

두 테이블을 연결할 때 가장 많이 사용되는 것이 내부 조인이다.

그냥 조인이라 부르면 내부 조인을 의미한다.

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

 

 

일대다 관계

• 데이터베이스의 테이블은 하나로 구성되는 것보다는
• 여러 정보를 주제에 따라 분리해서 저장하는 것이 효율적이다.
• 이 분리된 테이블은 서로 관계를 맺고 있다.
• 한쪽 테이블에는 하나의 값만 존재해야 하지만, 연결된 다른 테이블에는 여러 개의 값이 존재할 수 있는 관계

 

예시

• 회원 테이블에서 블랙핑크(BLK)의 아이디는 1개 밖에 없다. (기본 키)
• 구매 테이블의 아이디에서는 3개의 BLK가 있을 수 있다. (외래 키)
• 즉, 회원은 1명이지만 이 회원은 구매를 여러 번 할 수 있다.

• 일대다 관계는 주로 기본 키(PK)와 외래 키(FK) 관계로 맺어져 있다.
• 일대다 관계를 'PK-FK 관계" 라고 부르기도 한다.

 

내부 조인의 기본

 

일반적으로 조인은 내부 조인(INNER JOIN)을 뜻한다.

조인은 3개 이상의 테이블로도 할 수 있지만 대부분은 2개로 조인한다.

 

 

내부 조인의 형식

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

 

구매 테이블에는 물건을 구매한 회원의 아이디와 물건 등의 정보만 있다.

구매한 회원의 주소 및 연락처를 알기 위한 정보가 있는 회원테이블과 결합하는 것이 내부 조인이다.

 

구매 테이블에서 GRL 이라는 아이디를 가진 사람이 구매한 물건을 발송하기 위해 다음과 같이 조인한다.

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

 

두 개의 테이블(buy, member)를 조인하는 경우 동일한 열 이름이 존재한다면 꼭 테이블_이름.열_이름 형식으로 표기

 

 

내부 조인 과정

 

만약 WHERE buy.mem_id = 'GRL' 을 생략하면 어떻게 될까?

=> 구매 테이블의 모든 행이 회원 테이블과 결합한다.

 

 

내부 조인의 간결한 표현

열이 너무 많아 복잡해 보인다. 이번에는 필요한 정보만 추출해보겠다.

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

 

오류가 발생했다. 열 이름인 mem_id가 불확실하다는 오류 메세지 이다.

회원 아이디(mem_id)는 회원 테이블, 구매 테이블에 모두 들어 있어서 어느 테이블의 mem_id 인지 헷갈린다는 의미다.

 

이럴 때는 어느 테이블의 mem_id를 추출할지 정확하게 작성해야 한다.

지금은 구매테이블을 기준으로 하므로 buy.mem_id가 논리적으로 더 맞을 것 같다.

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

 

SQL을 좀 더 명확히 하기 위해 SELECT 다음의 열 이름도 모두 테이블_이름.열_이름 형식으로 작성해도 된다.

결과는 동일하다.

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 절 뒤에 나오는 테이블의 이름 뒤에 별칭을 줄 수 있다.

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;

• DISTINCT가 모든 선택된 열을 기준으로 중복을 판단한다.
• M.mem_id, M.mem_name, B.prod_name, M.addr의 조합이 고유해야 그 행이 중복으로 간주되지 않습니다.
• B.prod_name을 제외하고 회원을 고유하게 식별할 수 있는 열만 선택

 

 

 

 

외부 조인

외부 조인은 두 테이블을 조인할 때 필요한 내용이 한쪽 테이블에만 있어도 결과를 추출할 수 있다.

 

외부 조인 형식

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의 의미를 '왼쪽 테이블(member)의 내용은 모두 출력되어야 한다' 로 해석하자.

외부 조인은 한쪽 테이블에만 있는 정보도 출력된다. (구매 기록이 없는 회원도 출력된다!)

 

RIGHT OUTER JOIN으로 동일한 결과를 출력하려면 테이블의 위치만 바꿔주면 된다.

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
    FROM buy B
    RIGHT JOIN member M
    ON B.mem_id = M.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;

• IS NULL 구문은 널(NULL) 값인지 비교한다.

 

FULL OUTER JOIN은 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것.

 

 

 

기타 조인

 

상호 조인

상호 조인은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 것이다.

SELECT * FROM buy
    CROSS JOIN member;

 

상호 조인의 특징

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

 

자체 조인

내부 조인, 외부 조인, 상호 조인은 모두 2개의 테이블을 조인한 것이다.

자체 조인은 자신이 자신과 조인한다는 의미이다.

그래서 자체 조인은 1개의 테이블을 사용한다.

 

자체 조인의 형식

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

 

자체 조인의 활용

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개 이상의 열로 존재할 때 자체 조인을 할 수 있다.

 

 

 

해당 포스팅의 글과 내용은 우재남 저자님의 혼자 공부하는 SQL 책을 재구성하여 만들었습니다.

좀 더 자세한 내용이 필요하시다면 '혼자 공부하는 SQL 책'을 구매해주세요

우재남 저자님 카페: https://cafe.naver.com/thisisMySQL

이것이MySQL이다 : 네이버 카페