[ SQL 스터디 - 2주차 ] 두 테이블을 묶는 조인

 
위 포스트는 "혼자 공부하는 SQL" 책 Chapter 04-2 내용을 정리한 글입니다.

한빛미디어 - 혼자 공부하는 SQL (우재남 저) : 무료 동영상 강의 제공

---

 

Chapter 04-2 . 두 테이블을 묶는 조인

 

조인이란?

두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 의미한다.
 
종류로는 크게 내부 조인과, 외부 조인, 상호 조인, 자체 조인이 있는데,
두 개의 테이블이 서로 관계되어 있는 상태를 고려하며 하나씩 학습을 진행해보자.
 
 
 

** 내부 조인

 
01. 일대다 관계의 이해

---

 

일대다(one to many) 관계는

주로 기본 키(PK)와 외래 키(FK)로 맺어진 'PK-FK'라고 불리는 관계이다.
 
즉, 한쪽 테이블에는 하나의 값(PK)만 존재해야 하지만
연결된 다른 테이블에는 여러 개의 값(FK)이 존재할 수 있는 관계를 말한다.

** 기본 키 ( Primary Key, PK )

** 외래 키 ( Foreign Key, FK )

 
데이터베이스의 테이블은 하나로 구성되는 것보다
여러 정보를 주제에 따라 분리해서 저장하는 것이 효율적이다.
꼭 PK-FK 관계가 아니어도 가능한 조인도 있지만, 대부분의 조인은 이 관계가 핵심 요소이다.
 
 
 

02. 내부 조인의 기본

---

 

내부 조인은

우리가 일반적으로 말하는 조인이며, 조인 중에서 가장 많이 사용된다.
3개 이상의 테이블로 조인을 할 수 있지만, 대부분은 2개로 조인한다.
 
 
내부 조인의 형식은 다음과 같다.

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

 
 
이전 포스트에서 만들었던 market_db 데이터베이스의 테이블을 다시 살펴보자.

[ 1주차 ] SQL 기본 문법

 

member 테이블

buy 테이블

 
 
물건을 배송하는 상황을 가정해보자.
하나의 테이블만 사용한다면, 배송 정보를 완전하게 수집할 수 없을 것이다.
buy 테이블에 있는 mem_id, prod_name 정보와 mem ~~ 이 부분 도식화 하기

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

 
 
SELECT문의 논리적 수행 순서에 따라
위 SELECT문은 FROM -> WHERE -> INNER JOIN 순서로 실행된다.

                            <SELECT문의 논리적 수행 순서>
                            
SELECT 열_이름          ----------- 5 
    FROM 테이블_이름    ----------- 1
    WHERE 조건식        ----------- 2
    GROUP BY 열_이름    ----------- 3
    HAVING 조건식       ----------- 4
    ORDER BY 열_이름    ----------- 6
    LIMIT 숫자          ----------- 7

 
여기서 WHERE 절의 내용을 생략하여 아래와 같이 코드를 짠다면,

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

 
buy 테이블의 모든 행이 회원 테이블과 결합된다.
 
 
 
 
 

03. 내부 조인의 간결한 표현

---

 
위에서 사용한 GRL을 추출해 내부 조인한 코드는

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

 
WHERE절을 이용하여
GRL이라는 데이터를 가진 buy 테이블의 행을 추출하여 내부 조인을 하였다.
 
 

이번에는 테이블의 열을 일부 추출하여 필요한 데이터만 추출해 보겠다.

mem_id, mem_name, prod_name, addr에 해당하는 열을 추출해보자.

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

 
member 테이블과 buy 테이블 모두 mem_id라는 열 이름을 가지고 있기에,
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;

 
'FROM buy B', 'INNER JOIN member M'과 같이
테이블 이름에 별칭을 붙여 코드를 간결하게 표현하자.
 

 
별칭을 사용한 쿼리문 역시 위의 결과와 같음을 확인할 수 있다.
 
 
 
 

04. 내부 조인의 활용

---

 
 
 
 
 
 
 

** 외부 조인

 

01. 외부 조인의 기본

---

 
내부 조인과 외부 조인의 차이가 무엇일까?

** 내부 조인
두 테이블에 모두 데이터가 있어야만 결과를 추출할 수 있다.

** 외부 조인
필요한 데이터 내용이 한쪽 테이블에만 있어도 결과를 추출할 수 있다.

 
주로 내부 조인이 사용되긴 하지만, 외부 조인에 대해서도 알아보자.
외부 조인은 다음과 같은 형식을 가진다.

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

 
 
 
 

LEFT OUTER JOIN문은

왼쪽 테이블을 기준으로 외부 조인하는 것을 의미한다.
즉, 왼쪽 테이블의 내용이 모두 출력되는 것을 말한다.
 

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;

 
위 코드는 왼쪽에 위치한 member 테이블을 기준으로 buy 테이블과 외부 조인한다.
 

LEFT OUTER JOIN을 LEFT JOIN이라고만 해도 됨

 
해당하는 출력값은 다음과 같다.
외부 조인은 한쪽 테이블에만 있는 내용도 출력된다.
 
 
 
 

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;

 
LEFT OUTER JOIN문 예시와 동일한 결과를 출력하려면
위의 코드의 왼쪽과 오른쪽 테이블 위치를 바꿔주면 된다.
 

 
LEFT OUTER JOIN문과 동일한 출력값이 나오는 것을 확인할 수 있다.
RIGHT OUTER JOIN 역시 한쪽 테이블에만 있는 내용도 출력된다.
 
 
 
 
 

 
02. 외부 조인의 활용

---

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;

 
cf 1) FULL OUTER JOIN
cf 2) IS NULL
 
 
 

** 기타 조인 

 
내부 조인과 외부 조인 이외에 가끔 유용하게 사용되는 조인으로

"상호 조인"과 "자체 조인"이 있다.

이에 대해 하나씩 알아보자. 
 
 
 

01. 상호 조인

---

 

상호 조인은

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

대용량의 테스트용 테이블을 만들기 위해 사용하는 조인이라고 기억하면 된다.

 
 
 
아래의 예시를 살펴보자.

SELECT *
    FROM buy
        CROSS JOIN member;

member 테이블은 총 10개의 행을 가지고 있는데,
각각의 행들은 buy 테이블의 12개의 행과 1:1로 연결된다.
그래서 상호 조인을 하게 된다면 총 120개의 행이 생성된다.
 

 
아래에 더 많은 행이 존재하지만, 코드에 대한 출력값은 위과 같다.
 
 

그럼 "상호 조인의 특징"에 대해 정리해보자.

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

 
 
대용량의 테스트 테이블을 만들기 위해서는
아래와 같이 CREATE TABLE ~ SELECT문을 사용하면 된다.

CROSS TABLE cross_table
    SELECT *
        FROM sakila.actor   --200건
            CROSS JOIN world.country;   --239건

 
위 코드는 총 47,800건의 행이 생성된다.
 
 
 
 
 

02. 자체 조인

---

 
내부 조인, 외부 조인, 상호 조인과 달리

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