SQL 고급 문법 - 두 테이블을 묶는 조인(내부,외부,상호,자체)

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

 

 

 

일대다 관계의 이해

두 테이블의 조인을 위해서는 기본 키 - 외래 키 관계로 맺어져야 하고, 이를 '일대다 관계'라고 부른다.

 

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

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

 

예를 들어, 회원테이블의 아이디는 기본 키(Primary Key)이고, 구매 테이블의 아이디는 외래 키(Foreign Key)이다. 

 

 

 

내부 조인

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

 

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

 

 

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

★ 내부 조인의 과정

1. buy 테이블의 mem_id(buy.mem_id)인 'GRL'을 추출한다. 

2. 'GRL'과 동일한 값을 회원 테이블의 mem_id(member.mem_id) 열에서 검색한다. 

3. 'GRL'이라는 아이디를 찾으면 구매 테이블과 회원 테이블의 두 행을 결합(JOIN)한다. 

 

 

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

 

SQL문을 더 명확히 하기 위해서 열 이름에도 모두 테이블_이름.열_이름 형식으로 작성해보면, 

각 열이 어느 테이블에 속한 것인지 명확해졌지만, 너무 길어져서 오히려 복잡해 보이기도 한다. 

 

이를 간결하게 표현하기 위해서는 FROM 절에 나오는 테이블의 이름 뒤에 alias를 줄 수 있다. 

 

 

 

지금까지 사용한 내부 조인은 두 테이블에 모두 있는 내용만 조인되는 방식이다. 

만약, 양쪽 중에 한 곳이라도 내용이 있을 때 조인하려면 외부 조인을 사용해야 한다. 

 

 

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

예시 상황 : "우리 사이트에서 한 번이라도 구매한 기록이 있는 회원들에게 메일을 보낸다."

이런 경우에 SQL의 내부 조인을 사용해서 추출한 회원에게만 안내문을 발송하면 된다. 

중복은 필요 없으므로 DISTINCT 문을 활용해서 회원의 주소를 조회할 수 있다. 

 

 

 

 

외부 조인

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

 

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

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
    <LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
    ON <조인될 조건>
[WHERE 검색 조건];

 

 

'전체 회원의 구매 기록(구매 기록이 없는 회원 정보도 함께) 출력' 을 외부 조인으로 만들어 보자.

 

 

이번에는 회원가입만 하고, 한 번도 구매한 적 없는 회원의 목록을 추출해보자.

 

 

 

상호 조인

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

따라서 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수가 된다. 

 

* 결과의 내용은 의미가 없다. 랜덤으로 조인하기 때문이다.

* 상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때이다. 

 

대용량의 테이블 만들기

CREATE TABLE cross_table
    SELECT *
        FROM sakila.actor
            CROSS JOIN world.country;
            
SELECT * FROM cross_table LIMIT 5;

 

 

 

자체 조인

자체 조인(self join) 은 자신이 자신을 조인한다는 뜻이며, 따라서 1개의 테이블을 사용한다. 

별도의 문법이 있는 것은 아니고 1개로 조인하면 자체 조인이 된다. 

 

대표적인 사례로 회사의 조직관계가 있다. 

회사 조직 테이블

 

자체 조인의 형식은 다음과 같다. 

테이블이 1개지만 다른 별칭을 사용해서 서로 다른 것처럼 사용하면 된다. 

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