테이블 결합
- 보통 데이터베이스는 하나의 테이블에 많은 데이터를 저장하지 않고 몇 개의 테이블로 나누어 저장합니다.
- 이처럼 여러 개로 나뉜 데이터를 하나로 묶어 결과를 내는 방법이 바로 테이블 결합이며 이 개념이 집합론에서는
곱집합입니다.
곱집합과 교차결합
- 곱집합은 두 개의 잡합을 곱하는 연산 방법으로 적집합 또는 카티전곱(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명령을 사용하도록 권장합니다.
[DATABASE] 비전공자 출신의 개발 도전!