[SQL 첫걸음] 32강. 테이블 결합

테이블 결합

• 보통 데이터베이스는 하나의 테이블에 많은 데이터를 저장하지 않고 몇 개의 테이블로 나누어 저장합니다.
• 이처럼 여러 개로 나뉜 데이터를 하나로 묶어 결과를 내는 방법이 바로 테이블 결합이며 이 개념이 집합론에서는 곱집합입니다.

---

곱집합과 교차결합

• 곱집합은 두 개의 잡합을 곱하는 연산 방법으로 적집합 또는 카티전곱(Cartesian Product)라고도 불립니다.

교차결합(Cross Join)

• 데이터베이스 테이블은 집합의 한 종류이기 때문에 SELECT 명령에서 FROM 구에 두 개의 집합, 다시 말해 두 개의 테이블을 지정하면 이들은 곱집합으로 계산이 됩니다.

만약 아래와 같은 테이블 sample72_x와 sample72_y가 존재한다고 가정해봅시다.

--sample72_x
+------+
| x    |
+------+
| A    |
| B    |
| C    |
+------+

--sample72_y
+------+
| y    |
+------+
|    1 |
|    2 |
|    3 |
+------+

이를 곱집합하는 방법은 아래와 같습니다.

mysql > SELECT * FROM sample72_x, sample72_y;

+------+------+
| x    | y    |
+------+------+
| A    |    1 |
| B    |    1 |
| C    |    1 |
| A    |    2 |
| B    |    2 |
| C    |    2 |
| A    |    3 |
| B    |    3 |
| C    |    3 |
+------+------+
9 rows in set (0.00 sec)

• 이처럼 SELECT 명령에서 FROM 구에 복수의 테이블을 지정하면 교차결합을 하여 두 개의 테이블을 곱집합으로 계산합니다.

UNION 연결과 결합 연결의 차이

• UNION 키워드와 FROM 구에서의 복수 테이블 지정은 동일한 결괏값을 반환할 것처럼 보이지만 둘은 확대 방향이 다릅니다.
• UNION 키워드의 경우 합잡합이기 때문에 세로 방향으로 더해지고 반대로 FROM 구에 복수 테이블을 지정하는 것은 곱집합이기 때문에 가로 방향으로 더해집니다.
• 조금 더 직관적으로 이를 비교하기 위해 앞서 UNION 키워드를 통해 합집합을 계산했던 테이블 sample71_a와 sample71_b를 통해 확인해보겠습니다.
• 아래와 같이 FROM 구에 두 테이블을 지정한 것과 UNION 키워드를 사용한 결과가 다른 것을 확인할 수 있습니다.
• FROM 구에 복수의 테이블을 지정한 결과는 가로 방향으로 확장이 되었고 UNION 키워드를 사용한 결과는 세로 방향으로 확장이 되었습니다.

$ mysql > SELECT * FROM sample71_a, sample71_b;

+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
|    1 |   10 |
|    2 |   10 |
|    3 |   10 |
|    1 |   11 |
|    2 |   11 |
|    3 |   11 |
+------+------+
9 rows in set (0.00 sec)

$ mysql > SELECT * FROM sample71_A 
           UNION 
          SELECT * FROM sample71_b;

+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|   11 |
+------+
5 rows in set (0.00 sec)

---

내부결합

• 교차결합을 사용할 경우 결합해야 하는 테이블의 수가 늘어날 수록 집합 자체도 무척 거대해집니다. 그래서 보통 내부결합을 사용합니다.
• 수학에서의 집합은 유일한 요소로 구성됩니다. 이와 마찬가지로 데이터베이스 또한 중복된 값이 없게, 테이블의 데이터가 유일한 값을 가지도록 권장합니다.
• 이런 유일성과 연관된 기본키(Primary Key) 개념을 접했습니다. 이때 기본키는 하나의 데이터행을 대표하게 됩니다.
• 중복된 값을 지양하는 이유는 하나의 데이터에 변경이 발생할 때 다른 데이터도 모두 변경해줘야 하고 이 과정에서 오류가 발생할 수 있기 때문입니다.
• 내부결합에 관해 살펴보기 이전 아래와 같이 product_no를 기본키로 가지는 테이블 products를 우선 생성해줍니다.

mysql > CREATE TABLE products (
    -> product_no CHAR(4) NOT NULL,
    -> product_name VARCHAR(32) NOT NULL,
    -> price DECIMAL(6, 2) NOT NULL,
    -> CONSTRAINT pkey_products PRIMARY KEY (product_no)
    -> );

Query OK, 0 rows affected (0.00 sec)

mysql > DESC products;

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| product_no   | char(4)      | NO   | PRI | NULL    |       |
| product_name | varchar(32)  | NO   |     | NULL    |       |
| price        | decimal(6,2) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

그리고 아래와 같이 상품의 재고를 관리할 수 있는 테이블 product_stocks를 생성합니다.

mysql > CREATE TABLE product_stocks (
    -> product_no CHAR(4) NOT NULL,
    -> received_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    -> stock INTEGER NOT NULL
    -> );

Query OK, 0 rows affected (0.01 sec)

mysql > DESC product_stocks;

+---------------+----------+------+-----+-------------------+-------------------+
| Field         | Type     | Null | Key | Default           | Extra             |
+---------------+----------+------+-----+-------------------+-------------------+
| product_no    | char(4)  | NO   |     | NULL              |                   |
| received_date | datetime | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| stock         | int      | NO   |     | NULL              |                   |
+---------------+----------+------+-----+-------------------+-------------------+
3 rows in set (0.00 sec)

• 테이블 products와 product_stocks는 동일하게 product_no열을 통해 연결될 수 있습니다. - 이처럼 다른 테이블의 데이터를 참조해야 할 때 참조할 테이블의 기본키와 동일한 이름과 자료형으로 열을 만들어서 행을 연결할 수 있습니다.

두 테이블을 단순히 FROM 구의 복수 테이블로 지정하여 교차결합하면 그 결과는 아래와 같습니다.

mysql > SELECT * FROM products, product_stocks;

+------------+--------------+--------+------------+---------------------+-------+
| product_no | product_name | price  | product_no | received_date       | stock |
+------------+--------------+--------+------------+---------------------+-------+
| 0001       | 신발         |  24.00 | 0001       | 2021-12-09 18:06:12 |    20 |
| 0002       | 코트         | 100.50 | 0001       | 2021-12-09 18:06:12 |    20 |
| 0003       | 스웨터       |  49.50 | 0001       | 2021-12-09 18:06:12 |    20 |
| 0001       | 신발         |  24.00 | 0002       | 2021-12-09 18:06:12 |    30 |
| 0002       | 코트         | 100.50 | 0002       | 2021-12-09 18:06:12 |    30 |
| 0003       | 스웨터       |  49.50 | 0002       | 2021-12-09 18:06:12 |    30 |
| 0001       | 신발         |  24.00 | 0001       | 2021-10-09 08:06:12 |    50 |
| 0002       | 코트         | 100.50 | 0001       | 2021-10-09 08:06:12 |    50 |
| 0003       | 스웨터       |  49.50 | 0001       | 2021-10-09 08:06:12 |    50 |
| 0001       | 신발         |  24.00 | 0003       | 2021-10-09 08:06:12 |    70 |
| 0002       | 코트         | 100.50 | 0003       | 2021-10-09 08:06:12 |    70 |
| 0003       | 스웨터       |  49.50 | 0003       | 2021-10-09 08:06:12 |    70 |
+------------+--------------+--------+------------+---------------------+-------+
12 rows in set (0.00 sec)

product_no에 따라서 묶이지 않고 단순한 곱집합의 결과를 반환하여 상품 별 재고를 한 눈에 볼 수 없습니다. 이럴 때 아래와 같이 WHERE 구의 조건을 활용하여 상품 코드가 동일한 것만 묶어서 반환할 수 있습니다.

mysql > SELECT * 
          FROM products, product_stocks 
         WHERE products.product_no = product_stocks.product_no;

+------------+--------------+--------+------------+---------------------+-------+
| product_no | product_name | price  | product_no | received_date       | stock |
+------------+--------------+--------+------------+---------------------+-------+
| 0001       | 신발         |  24.00 | 0001       | 2021-12-09 18:06:12 |    20 |
| 0002       | 코트         | 100.50 | 0002       | 2021-12-09 18:06:12 |    30 |
| 0001       | 신발         |  24.00 | 0001       | 2021-10-09 08:06:12 |    50 |
| 0003       | 스웨터       |  49.50 | 0003       | 2021-10-09 08:06:12 |    70 |
+------------+--------------+--------+------------+---------------------+-------+
4 rows in set (0.00 sec)

이처럼 교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것을 내부결합(Inner Join)이라하며 이때 사용된 조건을 결합조건이라 합니다.

---

INNER JOIN으로 내부결합하기

위의 내부결합은 INNER JOIN 명령을 사용하여 더 간단하게 구할 수 있습니다. 그 방법은 아래와 같습니다.

mysql > SELECT * 
          FROM products 
         INNER JOIN product_stocks 
            ON products.product_no = product_stocks.product_no;

+------------+--------------+--------+------------+---------------------+-------+
| product_no | product_name | price  | product_no | received_date       | stock |
+------------+--------------+--------+------------+---------------------+-------+
| 0001       | 신발         |  24.00 | 0001       | 2021-12-09 18:06:12 |    20 |
| 0002       | 코트         | 100.50 | 0002       | 2021-12-09 18:06:12 |    30 |
| 0001       | 신발         |  24.00 | 0001       | 2021-10-09 08:06:12 |    50 |
| 0003       | 스웨터       |  49.50 | 0003       | 2021-10-09 08:06:12 |    70 |
+------------+--------------+--------+------------+---------------------+-------+
4 rows in set (0.00 sec)

• 앞서 WHERE 구를 통해 지정했던 결합조건을 INNER JOIN 명령의 ON 구에 사용하면 됩니다.
• 물론 INNER JOIN을 사용해도 아래와 같이 여전히 WHERE, ORDER BY 구를 사용할 수 있습니다.

// 제품테이블에서 재고테이블을 내부결합하고 제품테이블에서 
//가격이 50보다 작거나 같은 리스트를 불러오며 정렬은 재고의 입고일자를 내림차순하여 보여준다.
$ mysql > SELECT * 
            FROM products 
           INNER JOIN product_stocks 
              ON products.product_no = product_stocks.product_no 
           WHERE products.price <= 50 
           ORDER BY product_stocks.received_date DESC;

+------------+--------------+-------+------------+---------------------+-------+
| product_no | product_name | price | product_no | received_date       | stock |
+------------+--------------+-------+------------+---------------------+-------+
| 0001       | 신발         | 24.00 | 0001       | 2021-12-09 18:06:12 |    20 |
| 0001       | 신발         | 24.00 | 0001       | 2021-10-09 08:06:12 |    50 |
| 0003       | 스웨터       | 49.50 | 0003       | 2021-10-09 08:06:12 |    70 |
+------------+--------------+-------+------------+---------------------+-------+
3 rows in set (0.01 sec)

---

내부결합을 활용한 데이터 관리

• 위 예시에서는 product_no가 기본키가 되어 하나의 상품이 독립적인 제품이 되는 예시였습니다.
• 그러나 아래와 같이 각 상품 별로 메이커가 존재하는 경우 중복되는 메이커코드가 존재할 수 있습니다.

--상품 테이블
+--------------+-----------+-----------------+--------+--------------+
| 상품코드     | 상품명    | 메이커코드      | 가격   | 상품분류     |
+--------------+-----------+-----------------+--------+--------------+
| 0001         | 상품1     | M001            |    100 | 식료품       |
| 0002         | 상품2     | M001            |    200 | 식료품       |
| 0003         | 상품3     | M002            |   1980 | 생활용품     |
+--------------+-----------+-----------------+--------+--------------+

이때 메이커코드를 관리하는 테이블 메이커를 아래와 같이 독립적으로 생성하여 관리할 수 있습니다.

--메이커 테이블
+-----------------+--------------+
| 메이커코드      | 메이커명     |
+-----------------+--------------+
| M001            | 메이커1      |
| M002            | 메이커2      |
+-----------------+--------------+

• 이때 하나의 메이커코드에는 여러 상품이 존재할 수 있지만 반대로 하나의 상품은 하나의 메이커코드에 종속됩니다.
• 이러한 관계를 상품을 기준으로 봤을 때 일대다(1:N) 관계라 합니다.
• 이처럼 데이터베이스에서는 관계가 무척 중요합니다.
  • 일대일(1:1)
  • 일대다(1:N)
  • 다대다(N:M)

외부키

위 예시에서 메이커코드는 테이블 메이커에서 기본키입니다.

• 테이블 상품에서 외부의 기본키인 메이커코드를 참조하였습니다. 이처럼 외부의 기본키를 참조하는 걸 외부키(Foreign Key)라 합니다.

자기결합(Self Join)

• 자기결합(Self Join)은 테이블에 별명을 붙일 수 있는 기능을 이용해 같은 테이블끼리 결합하는 것을 의미합니다.
• 이때 유의할 점은 동일한 테이블을 참조하기 때문에 INNER JOIN 구에서 참조하는 테이블에 별명(AS)을 통해 테이블명을 다르게 해야 한다는 것입니다.

mysql> SELECT products.product_name AS first_name, 
              products.product_name AS second_name 
         FROM products 
        INNER JOIN products AS prod 
           ON prod.product_no = products.product_no;

+------------+-------------+
| first_name | second_name |
+------------+-------------+
| 신발       | 신발        |
| 코트       | 코트        |
| 스웨터     | 스웨터      |
+------------+-------------+
3 rows in set (0.01 sec)

자기결합은 자기 자신의 기본키를 참조하는 열을 자기 자신이 가지는 데이터 구조로 되어 있을 경우에 사용됩니다.

---

외부결합

• 앞선 결합 방법은 모두 내부결합이었습니다.
• 결합의 종류에는 외부결합도 있습니다.
• 외부결합은 교차결합으로 결합 조건을 지정하여 검색한다는 부분이 동일하지만 어느 한 쪽에만 존재하는 데이터행을 어떻게 다룰지를 변경할 수 있는 결합 방법입니다.
• 테이블 products에는 product_no가 0004인 행이 존재하는데 테이블 product_stocks에는 존재하지 않는다고 가정해봅시다.
• INNER JOIN 명령을 사용하여 내부결합을 하면 아래와 같이 product_no의 값이 0004인 행은 검색되지 않습니다.

mysql > SELECT * 
          FROM products 
          JOIN product_stocks 
            ON products.product_no = product_stocks.product_no;

+------------+--------------+--------+------------+---------------------+-------+
| product_no | product_name | price  | product_no | received_date       | stock |
+------------+--------------+--------+------------+---------------------+-------+
| 0001       | 신발         |  24.00 | 0001       | 2021-12-09 18:06:12 |    20 |
| 0002       | 코트         | 100.50 | 0002       | 2021-12-09 18:06:12 |    30 |
| 0001       | 신발         |  24.00 | 0001       | 2021-10-09 08:06:12 |    50 |
| 0003       | 스웨터       |  49.50 | 0003       | 2021-10-09 08:06:12 |    70 |
+------------+--------------+--------+------------+---------------------+-------+
4 rows in set (0.00 sec)

---

MySQL에서 단순히 JOIN만 사용할 경우 내부적으로 내부결합인 INNER JOIN 명령으로 인식합니다.

그러나 아래와 같이 LEFT JOIN 명령을 사용하여 이를 외부결합할 경우 product_no의 값이 0004인 행도 정상적으로 출력되는 것을 확인할 수 있습니다.

mysql > SELECT * 
          FROM products 
          LEFT JOIN product_stocks 
            ON products.product_no = product_stocks.product_no;

+------------+--------------+--------+------------+---------------------+-------+
| product_no | product_name | price  | product_no | received_date       | stock |
+------------+--------------+--------+------------+---------------------+-------+
| 0001       | 신발         |  24.00 | 0001       | 2021-12-09 18:06:12 |    20 |
| 0001       | 신발         |  24.00 | 0001       | 2021-10-09 08:06:12 |    50 |
| 0002       | 코트         | 100.50 | 0002       | 2021-12-09 18:06:12 |    30 |
| 0003       | 스웨터       |  49.50 | 0003       | 2021-10-09 08:06:12 |    70 |
| 0004       | 니트         |  24.50 | NULL       | NULL                |  NULL |
+------------+--------------+--------+------------+---------------------+-------+
5 rows in set (0.00 sec)

이때 우측 테이블인 product_stocks를 기준으로 하고 싶으면 LEFT JOIN 명령 대신 RIGHT JOIN을 사용하면 됩니다.

그러면 아래와 같이 내부결합을 한 것과 동일한 결괏값을 반환합니다.

mysql > SELECT * 
          FROM products 
         RIGHT JOIN product_stocks 
            ON products.product_no = product_stocks.product_no;

+------------+--------------+--------+------------+---------------------+-------+
| product_no | product_name | price  | product_no | received_date       | stock |
+------------+--------------+--------+------------+---------------------+-------+
| 0001       | 신발         |  24.00 | 0001       | 2021-12-09 18:06:12 |    20 |
| 0002       | 코트         | 100.50 | 0002       | 2021-12-09 18:06:12 |    30 |
| 0001       | 신발         |  24.00 | 0001       | 2021-10-09 08:06:12 |    50 |
| 0003       | 스웨터       |  49.50 | 0003       | 2021-10-09 08:06:12 |    70 |
+------------+--------------+--------+------------+---------------------+-------+
4 rows in set (0.00 sec)

---

구식방법에서의 외부결합과 표준 SQL

• 구식 결합방법에서는 FROM 구에 결합 조건을 기술하지 않고 WHERE 구로 결합 조건을 지정합니다.
• 그리고 이때 특수한 연산자를 붙이지 않으면 내부결합으로 인지하고 특수기호를 붙여서 조건식을 지정하면 외부결합으로 인지합니다.
• 아래는 Oracle에서의 외부결합 예시입니다.
• Oracle의 경우 외부결합을 위해 특수 기호 (+)를 붙이고 SQL Server의 경우 *= 또는 =*를 붙입니다.

SELECT * FROM products
  FROM products, product_stocks
 WHERE products.no = product_stocks.product_no (+)

• 현재는 표준화로 인해 내부결합은 INNER JOIN 명령을, 외부결합은 LEFT JOIN 또는 RIGHT JOIN 명령을 사용하도록 권장합니다.