[SQL] 04-2 두 테이블을 묶는 조인

2024-05-27 6 분 소요

SQL 고급 문법

두 테이블을 묶는 조인

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

내부 조인

두 테이블을 연결할 때 가장 맣이 사용되는 것이 내부 조인이다.
그냥 조인이라 부르면 내부 조인을 의미하는 것이다.

일대다 관계의 이해

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

데이터베이스의 테이블은 하나로 구성되는 것보다는 여러 정보를 주제에 따라 분리해서 저장하는 것이 효율적이다.
이 분리된 테이블은 서로 관계(relation)를 맺고 있다.
이러한 대표적인 사례가 인터넷 마켓 데이터베이스(market_db)의 회원 테이블과 구매 테이블이다.

market_db에서 회원 테이블의 아이디와 구매 테이블의 아이디는 일대다 관계이다.
일대다 관계란 한쪽 테이블에는 하나의 값만 존재해야 하지만, 연결된 다른 테이블에는 여러 개의 값이 존재할 수 있는 관계를 말한다.

예를 들어, 회원 테이블에서 블랙핑크의 아이디는 ‘BLK’로 1명(1, one)밖에 없다.
그래서 회원 테이블의 아이디를 기본키(Primary Key, PK)로 지정해따.
구매 테이블의 아이디에서는 3개의 BLK를 찾을 수 있다.
즉, 회원은 1명이지만 이 회원은 구매를 여러 번(다, many) 할 수 있는 것이다.
그래서 구매 테이블의 아이디는 기본 키가 아닌 외래 키(Foreign Key, FK)로 설정했다.

내부 조인의 기본

일반적으로 조인이라고 부르는 것은 내부 조인(inner join)을 말하는 것으로, 조인 중에서 가장 많이 사용된다.

내부 조인의 형식은 다음과 같다.

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

구매 테이블에는 물건을 구매한 회원의 아이디와 물건 등의 정보만 있다.
이 물건을 배송하기 위해서는 구매한 회원의 주소 및 연락처를 알아야 한다.
이 회원의 주소, 연락처를 알기 위해 정보가 있는 회원 테이블과 결합하는 것이 내부 조인이다.

구매 테이블에서 GRL이라는 아이디를 가진 사람이 구매한 물건을 발송하기 위해 다음과 같이 조인해서 이름/주소/연락처 등을 검색할 수 있다.

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

스크린샷 2024-05-27 214515

구매 테이블의 mem_id(buy.mem_id)인 ‘GRL’을 추출한다.
‘GRL’과 동일한 값을 회원 테이블의 mem_id(member.mem_id) 열에서 검색한다.
‘GRL’이라는 아이디를 찾으면 구매 테이블과 회원 테이블의 두 행을 결합(JOIN)한다.

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)가 회원 테이블, 구매 테이블에 모두 있어서 어느 테이블의 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 절에 나오는 테이블의 이름 뒤에 별칭(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 B.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;

스크린샷 2024-05-27 220602

구매 테이블의 목록이 12건이었으므로 이상 없이 잘 나왔다.
결과는 아무런 이상이 없지만, ‘전체 회원’이 아닌 ‘구매한 기록이 있는 회원들’의 목록이다.

결과에 한 번도 구매하지 않은 회원의 정보는 없다.

내부 조인은 두 테이블에 모두 있는 내용만 조인되는 방식이다.
만약, 양쪽 중에 한곳이라도 내용이 있을 때 조인하려면 외부 조인을 사용해야 한다.

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

내부 조인이 양쪽 모두 있는 내용만 나오기 때문에 유용한 경우도 있다.
예를 들면, 인터넷 마켓 운영자라면 다음과 같이 생각할 수 있다.

“우리 사이트에서 한 번이라도 구매한 기록이 있는 회원들에게 감사의 안내문을 발송합시다.”

이런 경우라면 앞의 SQL처럼 내부 조인을 사용해서 추출한 회원에게만 안내문을 발송하면 된다.
그리고 중복된 이름은 필요 없으므로 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;

LEFT OUTER JOIN 문의 의미를 ‘왼쪽 테이블(member)의 내용은 모두 출력되어야 한다’ 정도로 해석하면 기억하기 쉽다.

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, M.mem_name, B.prod_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;

한 번도 구매하지 않았으므로 조인된 결과의 물건 이름(prod_name)이 당연히 비어있을 것이다.
IS NULL 구문은 널(NULL) 값인지 비교한다.

FULL OUTER JOIN은 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것이라고 생각하면 된다.
왼쪽이든 오른쪽이든 한쪽에 들어 있는 내용이면 출력한다.

기타 조인

내부 조인이나 외부 조인처럼 자주 사용되지는 않지만 가끔 유용하게 사용되는 조인으로 상호 조인과 자체 조인도 있다.

상호 조인

상호 조인(cross join)은 한쪽 테이블의 모든 행과 다른쪽 테이블의 모든 행을 조인시키는 기능을 말한다.
그래서 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수가 된다.

SELECT *
    FROM buy
      CROSS JOIN member;

회원 테이블의 첫 행은 구매 테이블의 모든 행과 조인된다.
나머지 행도 마찬가지이다.
최종적으로 회원 테이블의 10개 행과 구매 테이블의 12개 행을 곱해서 총 120개의 결과가 생성되는 것이다.

상호 조인은 다음과 같은 특징을 갖는다.

ON 구문을 사용할 수 없다.
랜덤으로 조인하기 때문에 결과의 내용은 의미가 없다.
예) BLK를 에이핑크, 잇지, 소녀시대 등과도 조인한다.
상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때이다.

진짜로 대용량 테이블을 만들고 싶으면 CREATE TABLE ~ SELECT 문을 사용한다.

자체 조인

자체 조인(self join)은 자신이 자신과 조인한다는 의미이다.
그래서 자체 조인은 1개의 테이블을 사용한다.
또, 별도의 문법이 있는 것은 아니고 1개로 조인하면 자체 조인이 되는 것이다.

자체 조인의 형식은 다음과 같다.
테이블이 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');

경리부장 직속 상관의 연락처를 알고 싶다면 다음과 같은 SQL을 사용하면 된다.
emp_table을 emp_table A, emp_table B로 별칭을 지정해 각각 별개의 테이블처럼 사용했다.

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

Twitter Facebook LinkedIn

Sieun Kim