<SQL첫걸음> 7장 복수의 테이블다루기

📌 목차

• 31강 집합 연산
1. SQL과 집합
2. UNION으로 합집합 구하기
3. 교집합과 차집합
• 32강 테이블 결합
1. 곱집합과 교차결합
2. 내부결합
3. INNER JOIN으로 내부결합하기
4. 내부결합을 활용한 데이터 관리
5. 외부결합
• 33강 관계형 모델
1. 관계형 모델
2. 관계형 모델과 SQL

KEYWORD : 집합 연산, 곱집합과 교차결합, 내부결합, 외부결합, 서브쿼리, 관계형 모델

31강 - 집합 연산 (세로)

 1. SQL과 집합

테이블 = 집합

행 = 집합의 요소

2. UNION으로 합집합 구하기 

합집합을 계산할 때, 수학에서 사용하는 ∪ 기호 대신, UNION 키워드를 사용한다.

A ∪ B = A UNION B

테이블 A = {1,2,3} , B = {2,10,11}

mysql> SELECT * FROM sample71_a
    -> UNION
    -> SELECT * FROM sample71_b;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|   11 |
+------+

UNION으로 두 개의 SELECT 명령을 하나로 연계해 질의 결과를 얻을 수 있다.

단, 명령의 열의 내용은 서로 일치해야한다.

(∵ 열 이름은 다르나, 열 개수와 자료형이 서로 같기 때문에 일치한다고 할 수 있다.)

mysql> SELECT * FROM sample71_a 
	-> UNION 
    -> SELECT * FROM sample71_b 
    -> UNION 
    -> SELECT age FROM sample31;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|   11 |
|   36 |
|   18 |
|   25 |
+------+

위와 같은경우는, sample31에서 집합의 요소가 될 데이터를 열 지정을 통해 맞춰주었다.

mysql> SELECT * FROM sample31;
+------+------+-------------------+
| name | age  | address           |
+------+------+-------------------+
| A씨  |   36 | 대구광역시 중구   |
| B씨  |   18 | 부산광역시 연제구 |
| C씨  |   25 | 서울특별시 중구   |
+------+------+-------------------+
3 rows in set (0.00 sec)

단, 결괏값의 나열 순서는 매번 달라질 수 있음. 이는 ORDER BY로 지정가능, 그러나 마지막 SELECT구의 맨 끝에 지정해야한다는 것을 유의한다.

 

mysql> SELECT a AS c FROM sample71_a UNION SELECT b AS c FROM sample71_b ORDER BY c;
+------+
| c    |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|   11 |
+------+

AS를 통해 별명을 지정해주고, ORDER BY구에 지정하는 열은 별명을 붙여 이름을 일치시킨다.

 

UNION ALL

: 중복을 제거하지 않고, 2개의 SELECT명령의 결과를 그냥 합치고 싶을 때

(기본값이 DISTINCT 이기 때문)

mysql> SELECT * FROM sample71_a
    -> UNION ALL
    -> SELECT * FROM sample71_b;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    2 |
|   10 |
|   11 |
+------+

만일, 중복값이 없는 두 set일 경우, 연산이 들어가지 않는 UNION ALL이 성능측면에서 좋다.

 

3. 교집합과 차집합

MySQL에서 지원되지 않는다.

교집합 : INTERSECT

차집합 : EXCEPT (Oracle - MINUS)

 

32강 - 테이블 결합 (join) (가로)

가로(열) 방향으로 데이터 추가

1. 곱집합과 교차 결합

곱집합 = 적집합 = 카티전곱. (=데카르트곱)

교차결합

: 만일, SELECT 명령의 FROM 구에 테이블을 두 개 지정하면 이들은 곱집합으로 계산된다.

SELECT * FROSM 테이블명1, 테이블명2

두 테이블 sample72_x = {A, B, C} sample72_y = {1,2,3}에 대하여

mysql> SELECT * FROM sample72_x, sample72_y;
+------+------+
| x    | y    |
+------+------+
| C    |    1 |
| B    |    1 |
| A    |    1 |
| C    |    2 |
| B    |    2 |
| A    |    2 |
| C    |    3 |
| B    |    3 |
| A    |    3 |
+------+------+

FROM 구에 복수의 테이블 지정 = 교차결합을 한다 (데카르트 곱 연산을 한다.)

UNION	교차 결합
세로 방향 확대	가로 방향 확대

 

2. 내부 결합

: FROM 구에 테이블 복수(3개 이상) 지정 시 교차 결합 대신 사용하는 방법

: 교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것

상품 속성으로는 상품코드를 '기본 키'로 작성하는 경우가 많다.

 

다른 테이블의 데이터를 참조해야하는 경우, 참조할 테이블의 기본키와 동일한 이름과 자료형으로 열을 만들어 연결하는 경우가 많다.

mysql> SELECT * FROM 상품,재고수;
+----------+--------+----------+------+----------+----------+------------+--------+
| 상품코드 | 상품명 | 메이커명 | 가격 | 상품분류 | 상품코드 | 입고일     | 재고수 |
+----------+--------+----------+------+----------+----------+------------+--------+
| 0003     | 상품3  | 메이커3  | 1980 | 생활용품 | 0001     | 2014-01-03 |    200 |
| 0002     | 상품2  | 메이커2  |  200 | 식료품   | 0001     | 2014-01-03 |    200 |
| 0001     | 상품1  | 메이커1  |  100 | 식료품   | 0001     | 2014-01-03 |    200 |
| 0003     | 상품3  | 메이커3  | 1980 | 생활용품 | 0002     | 2014-02-10 |    500 |
| 0002     | 상품2  | 메이커2  |  200 | 식료품   | 0002     | 2014-02-10 |    500 |
| 0001     | 상품1  | 메이커1  |  100 | 식료품   | 0002     | 2014-02-10 |    500 |
| 0003     | 상품3  | 메이커3  | 1980 | 생활용품 | 0003     | 2014-02-14 |     10 |
| 0002     | 상품2  | 메이커2  |  200 | 식료품   | 0003     | 2014-02-14 |     10 |
| 0001     | 상품1  | 메이커1  |  100 | 식료품   | 0003     | 2014-02-14 |     10 |
+----------+--------+----------+------+----------+----------+------------+--------+

테이블 상품/ 재고수를 교차 결합함. 

 

상품코드가 같은 행을 검색하기

mysql> SELECT * FROM 상품, 재고수 WHERE 상품.상품코드 = 재고수.상품코드;
+----------+--------+----------+------+----------+----------+------------+--------+
| 상품코드 | 상품명 | 메이커명 | 가격 | 상품분류 | 상품코드 | 입고일     | 재고수 |
+----------+--------+----------+------+----------+----------+------------+--------+
| 0001     | 상품1  | 메이커1  |  100 | 식료품   | 0001     | 2014-01-03 |    200 |
| 0002     | 상품2  | 메이커2  |  200 | 식료품   | 0002     | 2014-02-10 |    500 |
| 0003     | 상품3  | 메이커3  | 1980 | 생활용품 | 0003     | 2014-02-14 |     10 |
+----------+--------+----------+------+----------+----------+------------+--------+

상품분류가 식료품인 행만 찾아 검색하기 ,,, 조건이 두개이므로 AND 사용

mysql>  SELECT * FROM 상품, 재고수 WHERE 상품.상품코드 = 재고수.상품코드 AND 상품.상품분류 = '식료품';
+----------+--------+----------+------+----------+----------+------------+--------+
| 상품코드 | 상품명 | 메이커명 | 가격 | 상품분류 | 상품코드 | 입고일     | 재고수 |
+----------+--------+----------+------+----------+----------+------------+--------+
| 0001     | 상품1  | 메이커1  |  100 | 식료품   | 0001     | 2014-01-03 |    200 |
| 0002     | 상품2  | 메이커2  |  200 | 식료품   | 0002     | 2014-02-10 |    500 |
+----------+--------+----------+------+----------+----------+------------+--------+

문자는  ' ' 로 감싸는 것 잊지말기!

첫 번째 조건식은 결합 조건 ( 교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것)

두 번째 조건식은 결합조건이 아닌 검색 조건 

 

지금까지의 모든 것들은 잊어라! 구식이다! 자 그럼 신식이다! ▼

3. INNER JOIN으로 내부결합하기

SYNTAX

SELECT * FROM 테이블명1 INNER JOIN 테이블명2 ON 결합조건

 

mysql> SELECT 상품.상품명, 재고수.재고수
    -> FROM 상품 INNER JOIN 재고수
    -> ON 상품.상품코드 = 재고수.상품코드
    -> WHERE 상품.상품분류 = '식료품';
+--------+--------+
| 상품명 | 재고수 |
+--------+--------+
| 상품1  |    200 |
| 상품2  |    500 |
+--------+--------+

INNER JOIN을 통해 두 개 테이블을 가로로 결합할 수 있다.  WHERE이 ON이 됨.

 

그냥 혼자 심심해서해봄

mysql> SELECT 상품.상품명, 재고수.재고수
    -> FROM 상품 INNER JOIN 재고수
    -> ON 상품.상품코드 = 재고수.상품코드
    -> WHERE 상품.상품분류 = '식료품';
+--------+--------+
| 상품명 | 재고수 |
+--------+--------+
| 상품1  |    200 |
| 상품2  |    500 |
+--------+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM 상품 INNER JOIN 재고수 ON 상품.상품코드 = 재고수.상품코드;
+----------+--------+----------+------+----------+----------+------------+--------+
| 상품코드 | 상품명 | 메이커명 | 가격 | 상품분류 | 상품코드 | 입고일     | 재고수 |
+----------+--------+----------+------+----------+----------+------------+--------+
| 0001     | 상품1  | 메이커1  |  100 | 식료품   | 0001     | 2014-01-03 |    200 |
| 0002     | 상품2  | 메이커2  |  200 | 식료품   | 0002     | 2014-02-10 |    500 |
| 0003     | 상품3  | 메이커3  | 1980 | 생활용품 | 0003     | 2014-02-14 |     10 |
+----------+--------+----------+------+----------+----------+------------+--------+
3 rows in set (0.00 sec)

mysql>  SELECT * FROM 상품 INNER JOIN 재고수 ON 상품.상품코드 = 재고수.상품코드 WHERE 상품.상품분류 = '식료품';
+----------+--------+----------+------+----------+----------+------------+--------+
| 상품코드 | 상품명 | 메이커명 | 가격 | 상품분류 | 상품코드 | 입고일     | 재고수 |
+----------+--------+----------+------+----------+----------+------------+--------+
| 0001     | 상품1  | 메이커1  |  100 | 식료품   | 0001     | 2014-01-03 |    200 |
| 0002     | 상품2  | 메이커2  |  200 | 식료품   | 0002     | 2014-02-10 |    500 |
+----------+--------+----------+------+----------+----------+------------+--------+
2 rows in set (0.00 sec)

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

테이블명 지정하기 번거로우므로, 별명을 붙여주었다.

mysql> SELECT S.상품명, M.메이커명 FROM 상품2 S INNER JOIN 메이커 M ON S.메이커코드 = M.메이커코드;
+--------+----------+
| 상품명 | 메이커명 |
+--------+----------+
| 상품1  | 메이커1  |
| 상품2  | 메이커1  |
| 상품3  | 메이커2  |
+--------+----------+

상품 테이블에서는 메이커가 기본 키가 아니지만, 메이커 테이블에는 메이커명이 기본 키이다.

A테이블과 B테이블 결합 시, A와 B 중 어느 쪽이 하나의 행만 가지는지(일대다,다대일) 아니면 양쪽모두 하나의 행을 가지는지(일대일) 등과 같은 서로 결합하는 테이블 간의 관계가 중요하다.

 

-외부키

위와 같은 예시에서, 테이블의 메이커 코드는  '외부키'라고 불린다. (메이커 테이블의 메이커코드는 기본키_

즉, 다른 테이블의 기본키를 참조하는 열이 외부키가 된다.

 

-자기결합(Self Join)

테이블에 별명을 붙일 수 있는 기능을 이용해 같은 테이블끼리 결합하는 것

mysql> SELECT S1.상품명, S2.상품명 FROM 상품 S1 INNER JOIN 상품 S2 ON S1.상품코드 = S2.상품코드;
+--------+--------+
| 상품명 | 상품명 |
+--------+--------+
| 상품1  | 상품1  |
| 상품2  | 상품2  |
| 상품3  | 상품3  |
+--------+--------+
3 rows in set (0.00 sec)

상품테이블을 가로로 두개 나열해 상품코드로 결합함. 

자기결합에서는 좌우가 같은 테이블이 된다. 따라서 반드시 별명 붙여야한다. 언제사용?

: 자기 자신의 기본키를 참조하는 열을 자기 자신이 가지는 데이터 구조로 되어 있을 경우

 

5. 외부결합 LEFT JOIN, RIGHT JOIN

: 어느 한 쪽에만 존재하는 데이터행을 어떻게 다룰지 변경할 수 있는 결합 방법(교차 결합으로 결합 조건 지정)

 

    JOIN의 왼쪽 => LEFT JOIN 

    JOIN의 오른쪽 => RIGHT JOIN

mysql>  SELECT 상품3.상품명, 재고수.재고수 FROM 상품3 LEFT JOIN 재고수
    ->  ON 상품3.상품코드 = 재고수.상품코드 WHERE 상품3.상품분류 = '식료품';
+----------+--------+
| 상품명   | 재고수 |
+----------+--------+
| 상품1    |    200 |
| 상품2    |    500 |
| 추가상품 |   NULL |
+----------+--------+

 

 

상품 테이블(결합의 왼쪽)이 기준이다. 재고수 테이블에는 상품코드 0009에 대한 데이터가 없으므로 값이 NULL로 표기됨.

상품 테이블을 오른쪽에 지정하는 경우나, 재고 테이블을 기준으로 삼고싶은 경우에는 RIGHT JOIN을 사용

예>

mysql> SELECT 상품3.상품명, 재고수.재고수 FROM 상품3 RIGHT JOIN 재고수 ON 상품3.상품코드 = 재고수.상품코드 WHERE 상품3.상품분류 = '식료품';
+--------+--------+
| 상품명 | 재고수 |
+--------+--------+
| 상품1  |    200 |
| 상품2  |    500 |
+--------+--------+

 

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

MySQL은 비교적 최신 RDBMS, 구식방법을 이용한 내부결합은 가능하나 외부결합은 불가능. Oracle의 경우를 다뤄보겠다.

 

구식 결합 방법에서는 FROM구에 결합 조건을 기술 X

=> WHERE구로 결합 조건 지정

 

 Oracle에서는 데이터가 존재하지 않을 수도 있는 테이블의 열에 (+)라는 특수 기호를 붙인다.

SELECT 상품3.상품명, 재고수.재고수 FROM 상품3,재고수 WHERE 상품3.상품코드 = 재고수.상품코드 (+) AND 상품3.상품분류='식료품';

 

그 외에도, SQL server애소눈 *= or =*를 사용해 외부결합을 한다. 이처럼 서로 다른 방법으로 하는 것은 SQL방언에 속한다. 

 

구식결합방법은 사용하지않으니 참고 하기만한다. 

 

33강  - 관계형 모델

1. 관계형 모델

관계형 모델(Relational Model)의 기본 요소는 릴레이션(Relation)이다. 관계형 모델의 릴레이션은 '테이블'에 해당한다.

테이블끼리의 관계가 아니다.

 

관계형 모델의 릴레이션에는 몇 가지 속성(attribute)가 존재한다. consist of 속성 이름, 행 이름

속성은 SQL의 열, SQL에서의 행은 관계형 모델에서 '튜플(tuple)'라고 불린다.

 

관계대수 : 릴레이션은 튜플의 집합이며, 릴레이션에 대한 연산이 집합에 대한 연산에 대응된다는 이론

규칙

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

2. 관계형 모델과 SQL

관계대수에서 규정하는 릴레이션의 몇 가지 연산 방법을 SQL과 비교

합집합

합집합(union)은 릴레이션끼리 덧셈 (SQL : UNION)

SELECT * FROM UNION SELECT * FROM B

차집합

차집합(difference)은 릴레이션끼리의 뺄셈 (SQL : EXCEPT)

SELECT * FROM A EXCEPT SELECT * FROM B

교집합

교집합(intersection)은 릴레이션끼리의 공통부분(교집합) (SQL : INTERSECT)

SELECT * FROM A INTERSECT SELECT * FROM B

곱집합

곱집합(cartesian product)은 릴레이션끼리의 대전표를 조합하는 연산 (SQL : FROM 구에 복수의 테이블을 지정하는 경우가 곱집합으로 계산된다)

 

CROSS JOIN으로 교차결합을 하면 곱집합을 구할 수 있다.

SELECT * FROM A,B
SELECT * FROM A CROSS JOIN B

선택

선택(selection)은 튜플의 추출 (=제한)

튜플은 SQL에서 행을 말하기에, WHERE 구에 조건을 지정해 데이터를 검색하는 것에 해당한다.

SELECT * FROM A WHERE no<3

투영

투영(projection)은 속성의 추출을 의미한다. 

속성은 SQL에서 열을 말하기에, SELECT 구에 결과로 반환할 열을 지정하는 것에 해당한다.

SELECT a FROM A

 

결합

결합(join)은 릴레이션끼리 교차결합해 계산된 곱집합에서 결합 조건을 만족하는 튜플을 추출하는 연산

(SQL : 내부 결합, 관계대수: 내부결합&외부결합)

SELECT a FROM A INNER JOIN B ON A.no = B.no

 

 

========================

집합 연산

: 테이블은 데이터행의 집합으로 간주할 수 있다. SQL에서는 UNION으로 합집합을 구할 수 있습니다. 데이터베이스의 지원 여부에 따라 차집합이나 교집합을 구할 수도 있습니다.

교차 결합, 곱집합

: FROM 구에 테이블을 복수로 지정하여 교차결합으로 곱집합을 구할 수 있습니다. 곱집합은 대전표를 조합하는 것과 같은 방법으로 연산되며 내부결합이나 외부결합의 기반이 됩니다.

내부결합

: 내부결합은 곱집합에서 필요한 행만 검색하도록 조건을 지정해 결합하는 것을 말합니다. 이때 지정하는 조건을 결합조건이라고 부릅니다.

외부결합

:내부결합으로 결합되지 않는 행을 강제적으로 결과에 포함하는 방법을 말합니다.

관계형 모델

: 관계형 모델은 관계형 데이터베이스의 기반이 되는 이론적 개념으로, 사용되는 용어는 SQL과 일치하지 않는다.