[혼공S] 04 - 2 '두 테이블을 묶는 조인' 정리

내부 조인

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

 

일대다 관계의 이해

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

 

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

 

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

 

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

 

 

내부 조인의 기본

일반적으로 조인이라고 부르는 것은 내부 조인(inner join)을 말하는 것으로, 조인 중에서 가장 많이 사용된다. 조인은 3개 이상의 테이블로도 할 수 있지만 대부분은 2개로 조인한다.

 

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

 

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';

 

두 테이블을 내부 조인하는 SQL은 다음 그림과 같은 과정을 거친다. 중요한 개념이므로 잘 기억하자.

 

 

 

만약, WHERE buy.mem_id='GRL'을 생략하면 어떻게 될까? 원래는 구매 테이블의 7번째에 있는 GRL에 대해서만 결합했지만, WHERE 절을 생략하면 1번째 BLK부터 12번째 MMU까지 구매 테이블의 모든 행이 회원 테이블과 결합한다.

 

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

 

 

내부 조인의 간결한 표현

열이 너무 많아 복잡해 보이므로, 이번에는 필요한 아이디/이름/구매 물품/주소/연락처만 추출해보자.

 

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

 

위 처럼 SQL을 작성하면 오류가 등장하는데, 회원 아이디(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 다음의 열 이름(칼럼 이름)에도 모두 테이블_이름.열_이름 형식으로 작성할 수 있지만 이렇게 하면 오히려 코드가 복잡해 보인다. 이를 간결하게 표현하기 위해 다음과 같이 FROM 절에 나오는 테이블의 이름 뒤에 별칭(alias)을 줄 수 있다. 앞으로 여러 개의 테이블이 관련된 조인에서는 이와 같은 방식을 사용하자.

 

SELECT B.mem_id, M.mem_id, 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;

 

 

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

 

결과에 한 번도 구매하지 않은 회원의 정보는없다. 우리가 원하는 결과는 구매한 회원의 구매 기록과 더불어 구매하지 않은 회원의 이름/주소가 같이 검색되도록 하는 것이다.

 

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

 

중복을 없애고 싶다면 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;

 

외부 조인

내부 조인은 두 테이블에 모두 데이터가 있어야만 결과가 나온다. 이와 달리 외부 조인은 한쪽에만 데이터가 있어도 결과가 나온다.

 

외부 조인의 기본

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

 

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

 

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)의 내용은 모두 출력되어야 한다' 정도로 해석하면 기억하기 쉽다.

 

 

외부 조인의 활용

내부 조인으로 구매한 기록이 있는 회원들의 목록만 추출해서 감사문을 보냈었다. 이번에는 반대로 회원으로 가입만 하고, 한 번도 구매한 적이 없는 회원의 목록을 추출해보자.

 

SELECT DISTINCT M.mem_id, M.mem_name, B.prod_name, M.addr
    FROM membe 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 NUL 구문은 널(NULL) 값인지 비교한다. 한 번도 구매하지 않은 6명의 회원이 나온 것을 확인할 수 있다.

 

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

 

상호 조인

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

 

SELECT *
    FROM buy
    	CROSS JOIN member;

 

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

 

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

 

자체 조인

내부 조인, 외부 조인, 상호 조인은 모두 2개의 테이블을 조인했다. 자체 조인(self join)은 자신과 조인한다는 의미이다. 그래서 자체 조인은 1개의 테이블을 사용한다. 또, 별도의 문법이 있는 것은 아니고 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 = '경리부장';

 

 

핵심 정리

일대다 관계란 한쪽 테이블에는 하나의 값만, 다른 쪽 테이블에는 여러 개의 값이 존재할 수 있는 관계를 말한다.

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

내부 조인은 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.

외부 조인은 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다.

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

자체 조인은 자신이 자신과 조인된다는 의미로, 1개의 테이블을 사용한다.

 

관련 중요 용어

용어	설명
관계	두 테이블이 서로 연관되는 것
기본 키 - 외래 키 관계	두 테이블이 일대다 관계로 연결되기 위한 조건
별칭(alias)	조인에서 테이블의 이름을 짧게 표현하는 이름
DISTINCT 문	중복된 열의 값을 1개만 표현하는 구문
LEFT OUTER JOIN	왼쪽 테이블의 모든 값이 출력되는 조인
RIGER OUTER JOIN	오른쪽 테이블의 모든 값이 출력되는 조인
FULL OUTER JOIN	왼쪽 또는 오른쪽 테이블의 모든 값이 출력되는 조인
CREATE TABLE ~ SELECT	SELECT의 결과가 테이블로 생성되는 구문