Skip to content

[ SQL 첫걸음 ] 제 7장 복수의 테이블 다루기

참고

SQL 첫걸음을 참고로 공부한 내용입니다. 따라서 아래 명령어를 통해 나오게 되는 결괏값은 전부 해당 책을 통해 다운로드한 파일의 결과물입니다.

집합 연산

관계형 모델을 채택한 데이터베이를 관계형 데이터베이스라 부릅니다. 이때 관계형 모델에서의 관계형은 수학 집합론의 관계형 이론에서 유래했습니다. 쉽게 설명하면 관계형 데이터베이스는 결국 데이터를 집합으로 간주해 다루기 쉽게 하는 목적에서 나온 개념입니다.

SQL과 집합

SQL에서 하나의 행을 하나의 요소, 그리고 복수의 행을 반환한 결과 전체를 하나의 집합이라 생각하면 됩니다.

UNION으로 합집합 구하기

합집합이란 집합 연산에서 집합끼리 서로 더한 것을 의미합니다.

UNION

SQL에서는 합집합을 계산하기 위해 UNION 키워드를 사용합니다.

아래와 같이 테이블 sample71_asample71_b가 존재한다고 가정해봅시다.

--sample71_a
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+

--sample71_b
+------+
| b    |
+------+
|    2 |
|   10 |
|   11 |
+------+

UNION 키워드를 사용하여 두 테이블 sample71_asample71_b의 합집합을 구하는 방법은 아래와 같습니다.

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

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

이때 유의할 점은 세미콜론(;)은 명령문의 종료를 의미하기 때문에 꼭 맨 마지막에 붙여야 한다는 것입니다.

애스터리스크(*)를 사용하지 않고 아래와 같이 열을 따로 지정하여 집합의 요소가 될 데이터를 맞춰줄 수도 있습니다.

$ mysql > SELECT a FROM sample71_a UNION SELECT b FROM sample71_b UNION SELECT age FROM sample31;

+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|   11 |
|   36 |
|   18 |
|   25 |
+------+
8 rows in set (0.00 sec)

UNION 명령을 사용할 때 나열 순서는 합집합의 결과에 영향을 주지 않습니다. 따라서 결괏값 자체는 동일하지만 결괏값의 나열 순서는 달라질 수 있습니다.

UNION을 사용할 때의 ORDER BY

UNION 키워드를 사용하면 ORDER BY 명령을 가장 마지막 SELECT 명령에만 사용할 수 있습니다. 또한 합집합의 결과를 정렬하는 것이기 때문에 지정한 열의 별명(AS)을 동일하게 지정하여 어떤 열을 정의해야 하는 지 정확하게 ORDER BY 명령으로 넘겨줘야 합니다.

$ mysql > SELECT a AS age FROM sample71_a UNION SELECT b AS age FROM sample71_b ORDER BY age DESC;

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

이는 결국 SELECT 구의 내부처리 순서가 ORDER BY 구보다 빠르기 때문입니다.

UNION ALL

UNION 키워드는 기본적으로 수학의 합집합과 동일하기 때문에 중복된 요소를 제거하여 결괏값을 반환합니다. 그러나 만약 중복된 요소도 결괏값으로 얻고 싶다면 UNION ALL 키워드를 사용하면 됩니다.

이는 중복을 제거하는 DISTINCT 키워드와 반대입니다. 기본적으로 ALL 키워드가 생략되어 있기 때문에 SELECT 명령을 사용할 때 DISTINCT 키워드를 추가하지만 중복된 값을 반환합니다. 반대로 UNION의 경우 DISTINCT 키워드가 기본이라고 생각하면 편합니다.

그러나 실제로 DISTINCT 명령이 생략되어 있는 것은 아니기 때문에 UNION DISTINCT와 같은 명령은 오류를 반환하니 유의해야 합니다.

테이블 sample71_asample71_b의 중복된 요소를 포함한 합집합 결과를 확인하는 방법은 아래와 같습니다. 그러면 앞서 중복되어 제거되었던 값 2가 이번에는 중복되어 결괏값으로 반횐된 것을 확인할 수 있습니다.

$ mysql > SELECT a AS age FROM sample71_a UNION ALL SELECT b AS age FROM sample71_b ORDER BY age DESC;

+------+
| age  |
+------+
|   11 |
|   10 |
|    3 |
|    2 |
|    2 |
|    1 |
+------+
6 rows in set (0.00 sec)

UNION 키워드는 내부적으로 결국 중복된 값이 있는 지 검사하는 처리가 필요하기 때문에 중복값이 없는 경우에는 UNION 키워드보다 UNION ALL 키워드를 사용하는 게 성능이 더 좋습니다.

교집합과 차집합

SQL에서 합집합과 마찬가지로 교집합차집합을 구할 수 있습니다.

교집합이란 두 개의 집합이 겹치는 부분, 다시 말해 공통 부분을 의미하며 차집합이란 하나의 집합을 기준으로 다른 쪽의 집합을 제거하고 남은 부분, 다시 말해 기준이 되는 집합에서 다른 집합과 공통된 부분을 제거하고 남은 순수 부분을 의미합니다.

정보

차집합의 결과가 아무런 요소도 존재하지 않는 공집합인 경우 이는 곧 두 개의 집합이 완전히 같다는 걸 의미합니다.

교집합을 구하기 위해서는 INTERSECT 키워드를 사용하며 차집합을 구하기 위해서는 EXCEPT 키워드를, Oracle에서는 MINUS 키워드를 사용합니다. MySQL에는 별도의 키워드가 존재하지 않기 때문에 직접 이를 계산하여 구해야 합니다.

MySQL에서 교집합과 차집합을 아래와 같이 EXISTSNOT EXISTS 키워드를 통한 상관 서브쿼리를 활용하여 구현할 수 있습니다.

mysql> SELECT * FROM sample71_a WHERE EXISTS ( SELECT * FROM sample71_b WHERE sample71_b.b = sample71_a.a );
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM sample71_a WHERE NOT EXISTS ( SELECT * FROM sample71_b WHERE sample71_a.a = sample71_b.b );
+------+
| a    |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.00 sec)

테이블 결합

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

곱집합과 교차결합

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

교차결합(Cross Join)

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

만약 아래와 같은 테이블 sample72_xsample72_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_asample71_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)

테이블 productsproduct_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 구를 사용할 수 있습니다.

$ 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_no0004인 행이 존재하는데 테이블 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 명령을 사용하도록 권장합니다.

관계형 모델

관계형 데이터베이스는 관계형 모델(Relational Model)을 기반으로 작성된 데이터베이스입니다. 그러나 관계형 모델에서 사용하는 용어와 SQL의 용어가 완벽하게 일치하지는 않습니다.

관계형 모델

관계형 모델(Relational Model)의 기본적인 요소는 관계, 릴레이션(Relation)입니다. 관계형 모델에서 이 릴레이션은 약간 다른 의미를 가집니다. 관계형 모델의 릴레이션은 SQL에서 말하는 테이블(Table에 해당합니다.

관계형 모델의 릴레이션에는 몇 가지 속성(Attribute)이 있습니다. 이 속성은 속성 이름형 이름으로 구성됩니다. 속성은 SQL에서 말하는 열(Column)에 해당합니다. 그리고 튜플(Tuple)이 곧 SQL에서 말하는 행(Row)을 의미합니다.

정보

튜플(Tuple), 다시 말해 행(Row)속성(Attribute)의 집합, 다시 말해 열(Column)의 집합으로 레코드(Record)라고도 부릅니다.

관계형 모델은 데이터 구조에 관해 정의합니다. 릴레이션은 결국 튜플의 집합이기 때문에 따라서 릴레이션에 대한 연산이 집합의 대한 연산에 대응된다는 이론을 관계대수(Relational Algebra)라 합니다. 이를 풀어서 설명하면 테이블은 결국 행들의 집합이기 때문에 테이블에 대한 연산이 결국 행에 대한 연산과 대응된다는 의미입니다.

이러한 관계대수의 기본 규칙은 다음과 같습니다.

  • 하나 이상의 관계를 바탕으로 연산한다.
  • 연산한 결과, 반환되는 것 또한 관계이다.
  • 연산을 중첩 구조로 실행해도 상관없다.

하나씩 SQL과 비교하여 설명해보겠습니다.

  • 앞서 살펴본 일대일, 일대다, 다대다 관계처럼 하나 이상의 관계를 바탕으로 연산합니다.
  • UNION 명령, JOIN 명령을 통한 테이블 결합처럼 연산한 결과와 반환되는 것도 관계(Relation), 다시 말해 테이블입니다.
  • 서브쿼리를 활용하여 연산을 중첩 구조로 실행할 수 있습니다.

관계형 모델과 SQL

관계대수에서는 자주 사용될 것 같은 릴레이션의 연산 방법을 미리 규정합니다. SQL 명령과 한 번 비교해보겠습니다.

합집합

합집합(Union)은 릴레이션끼리의 덧셈을 의미합니다. SQL에서는 UNION에 해당합니다. 예시는 아래와 같습니다.

SELECT * FROM a UNION SELECT * FROM b

차집합

차집합(Difference)은 릴레이션끼리의 뺄셈을 의미합니다. SQL에서는 EXCEPT에 해당합니다. 예시는 아래와 같습니다.

SELECT * FROM a EXCEPT SELECT * FROM b

그러나 MySQL의 경우 EXCEPT 키워드가 존재하지 않기 때문에 아래와 같이 NOT EXISTS 키워드를 WHERE 구의 서브쿼리 조건으로 활용하여 차집합을 계산할 수 있습니다.

SELECT * FROM a WHERE NOT EXISTS ( SELECT * FROM b WHERE a.no = b.no )

교집합

교집합(Intersection)은 릴레이션끼리의 공통부분(교집합)을 의미합니다. SQL에서는 INTERSECT에 해당합니다. 예시는 아래와 같습니다.

SELECT * FROM a INTERSECT SELECT * FROM b

그러나 MySQL의 경우 INTERSECT 키워드가 존재하지 않기 때문에 아래와 같이 EXISTS 키워드를 WHERE 구의 서브쿼리 조건으로 활용하여 교집합을 계산할 수 있습니다.

SELECT * FROM a WHERE EXISTS ( SELECT * FROM b WHERE a.no = b.no )

곱집합

곱집합(Cartesian Product)은 릴레이션끼리의 대전표를 조합하는 연산을 의미합니다. SQL에서는 FROM 구에 복수의 테이블을 지정하거나 CROSS JOIN을 활용하여 계산할 수 있습니다. 예시는 아래와 같습니다.

-- FROM (복수 테이블)
SELECT * FROM a, b

-- CROSS JOIN
SELECT * FROM a CROSS JOIN b

선택

선택(Selection)은 튜플의 추출을 의미합니다. 제한이라고 불리기도 합니다. 튜플은 앞서 SQL에서는 행을 의미한다고 언급했습니다. 따라서 SQL에서는 WHERE 구에 조건을 지정해 데이터를 검색(SELECT)하는 것에 해당합니다. 예시는 아래와 같습니다.

SELECT * FROM a WHERE no < 3

투영

투영(Projection)은 속성의 추출을 의미합니다. 속성은 앞서 SQL에서는 열을 의미한다고 언급했습니다. 따라서 SQL에서는 SELECT 구에 결과로 반환할 열을 지정하는 것에 해당합니다. 예시는 아래와 같습니다.

SELECT no, nickname FROM a

결합

결합(Join)은 릴레이션끼리 교차결합해 계산된 곱집합에서 결합조건을 만족하는 튜플을 추출하는 연산입니다. SQL에서는 내부결합에 해당합니다. 예시는 아래와 같습니다.

-- 구식방법
SELECT * FROM a, b WHERE a.no = b.no

-- INNER JOIN
SELECT * FROM a INNER JOIN b ON a.no = b.no

관계대수에도 내부결합과 외부결합이 존재합니다. SQL에서의 외부결합 예시는 아래와 같습니다.

-- LEFT JOIN
SELECT * FROM a LEFT JOIN b ON a.no = b.no

-- RIGHT JOIN
SELECT * FROM b RIGHT JOIN a ON b.no = a.no