SQL 스터디 - 테이블 결합

이재문·2022년 2월 24일
0
post-thumbnail

테이블 결합이란?

테이블은 행의 방향으로 데이터가 늘어남
테이터베이스는 데이터를 여러 테이블에 나눠서 저장
나눠서 만든 테이블을 하나로 묶어 결과를 나타내는 방법

곱집합과 교차결합

곱집합

  • ‘적집합’, ‘카티전곱’라고도 불림
  • 모든 요소를 다 곱해서 옆으로 나열할 수 있다.

교차집합

FROM 구에 여러 테이블을 지정하면 곱집합 표현 

SELECT * FROM table1, table2, ···

UNION 연결 - 결합 연결 차이

  • UNION 연결은 합집합이 세로로 확대 결합 연결은 새로운 집합을 만들어서 가로로 확대
> UNION 사용
select * from firstname union select * from name
+----+------+
| id | name |
+----+------+
| 1  | 이씨 |
| 2  | 김씨 |
| 3  | 박씨 |
| 4  | 호씨 |
| 1  | 재문 |
| 2  | 유진 |
| 3  | 태연 |
| 4  | 현이 |
+----+------+

> 결합 연결 사용
select * from firstname, name
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
| 4  | 호씨 | 1  | 재문 |
| 3  | 박씨 | 1  | 재문 |
| 2  | 김씨 | 1  | 재문 |
| 1  | 이씨 | 1  | 재문 |
| 4  | 호씨 | 2  | 유진 |
| 3  | 박씨 | 2  | 유진 |
| 2  | 김씨 | 2  | 유진 |
| 1  | 이씨 | 2  | 유진 |
| 4  | 호씨 | 3  | 태연 |
| 3  | 박씨 | 3  | 태연 |
| 2  | 김씨 | 3  | 태연 |
| 1  | 이씨 | 3  | 태연 |
| 4  | 호씨 | 4  | 현이 |
| 3  | 박씨 | 4  | 현이 |
| 2  | 김씨 | 4  | 현이 |
| 1  | 이씨 | 4  | 현이 |
+----+------+----+------+

내부 결합(INNER)

내부결합이란?

앞서 곱집합에서 FROM 구에 여러 테이블을 지정할 수 있다고 했다.
두개의 테이블 뿐 아니라 더 많은 테이블을 지정 할 수 있다.
테이블 수가 많아질수록 결과값은 방대해진다. 이때 내부결합을 사용한다.

데이터베이스 구조

  • 데이터의 집합을 하기위해 PK(유일키)가 필요하다.
  • PK는 구분하기 어렵거나 중복되서는 안된다.
  • 데이터베이스에 같은 데이터를 여러번 저장하면 안된다.
  • 데이베이스명과 관계 없는 내용은 다른 데이터베이스에 저장하는게 좋다.

예시

  • 상품과 카테고리의 교차결합
    mysql> select * from products;
+------+----------+-------+---------+
| prod | name     | price | cate_id |
+------+----------+-------+---------+
|    1 | gtx1050  |   200 |       3 |
|    2 | gtx2060  |   300 |       3 |
|    3 | intel i7 |   250 |       2 |
|    4 | rtx1020  |   100 |       3 |
|    5 | intel i9 |   350 |       2 |
|    6 | g102     |   100 |       1 |
|    7 | g202     |   120 |       1 |
|    8 | g111     |    12 |       4 |
+------+----------+-------+---------+

mysql> select * from category;
+----+-----------+
| id | cate_name |
+----+-----------+
|  1 | mouse     |
|  2 | cpu       |
|  3 | gc        |
+----+-----------+

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

mysql> select * from products, category;
+------+----------+-------+---------+----+-----------+
| prod | name     | price | cate_id | id | cate_name |
+------+----------+-------+---------+----+-----------+
|    1 | gtx1050  |   200 |       3 |  3 | gc        |
|    1 | gtx1050  |   200 |       3 |  2 | cpu       |
|    1 | gtx1050  |   200 |       3 |  1 | mouse     |
|    2 | gtx2060  |   300 |       3 |  3 | gc        |
|    2 | gtx2060  |   300 |       3 |  2 | cpu       |
|    2 | gtx2060  |   300 |       3 |  1 | mouse     |
|    3 | intel i7 |   250 |       2 |  3 | gc        |
|    3 | intel i7 |   250 |       2 |  2 | cpu       |
|    3 | intel i7 |   250 |       2 |  1 | mouse     |
|    4 | rtx1020  |   100 |       3 |  3 | gc        |
|    4 | rtx1020  |   100 |       3 |  2 | cpu       |
|    4 | rtx1020  |   100 |       3 |  1 | mouse     |
|    5 | intel i9 |   350 |       2 |  3 | gc        |
|    5 | intel i9 |   350 |       2 |  2 | cpu       |
|    5 | intel i9 |   350 |       2 |  1 | mouse     |
|    6 | g102     |   100 |       1 |  3 | gc        |
|    6 | g102     |   100 |       1 |  2 | cpu       |
|    6 | g102     |   100 |       1 |  1 | mouse     |
|    7 | g202     |   120 |       1 |  3 | gc        |
|    7 | g202     |   120 |       1 |  2 | cpu       |
|    7 | g202     |   120 |       1 |  1 | mouse     |
|    8 | g111     |    12 |       4 |  3 | gc        |
|    8 | g111     |    12 |       4 |  2 | cpu       |
|    8 | g111     |    12 |       4 |  1 | mouse     |
+------+----------+-------+---------+----+-----------+
  • 카테고리가와 상품이 같은 결과
    mysql> select * from products,category
				where category.id = products.cate_id;

+------+----------+-------+---------+----+-----------+
| prod | name     | price | cate_id | id | cate_name |
+------+----------+-------+---------+----+-----------+
|    1 | gtx1050  |   200 |       3 |  3 | gc        |
|    2 | gtx2060  |   300 |       3 |  3 | gc        |
|    3 | intel i7 |   250 |       2 |  2 | cpu       |
|    4 | rtx1020  |   100 |       3 |  3 | gc        |
|    5 | intel i9 |   350 |       2 |  2 | cpu       |
|    6 | g102     |   100 |       1 |  1 | mouse     |
|    7 | g202     |   120 |       1 |  1 | mouse     |
+------+----------+-------+---------+----+-----------+

    • 위 결과에서 카테고리 이름을 AND문으로 조건 - 결합조건

      mysql> select * from products,category 
				where category.id = products.cate_id // 결합
				and category.cate_name = 'mouse';    // 결합조건
+------+------+-------+---------+----+-----------+
| prod | name | price | cate_id | id | cate_name |
+------+------+-------+---------+----+-----------+
|    6 | g102 |   100 |       1 |  1 | mouse     |
|    7 | g202 |   120 |       1 |  1 | mouse     |
+------+------+-------+---------+----+-----------+

INNER JOIN으로 내부결합하기

위에서 WHERE 과 AND(결합조건)을 지정하여 결합하는 방법은 구식

일반적으로 INNER JOIN 사용

INNER JOIN : ‘안쪽으로 연결시킨다’ 라는 의미

  • INNER JOIN으로 변환

    • 변환 전

      SELECT * FROM products,category 
WHERE category.id = products.cate_id

    • 변환 후

      SELECT * FROM products INNER JOIN category
ON category.id = products.cate_id

      WHERE문을 사용하지 않고 ON문을 사용.

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

  • 위의INNER JOIN 만 사용했을 때 필요한 항목만 보고 싶을 때
    SELECT cloumn1, cloumn2,··· FROM table1
INNER JOIN table2
ON 조건
    mysql> 
SELECT products.name, category.cate_name // 필요한 열(상품명, 카테고리명)
FROM products                            // 메인 테이블
INNER JOIN category                      // 연결시킬 테이블
ON category.id = products.cate_id        // 결합조건
ORDER BY cate_name                       // 정렬조건

+----------+-----------+
| name     | cate_name |
+----------+-----------+
| g111     | case      |
| intel i7 | cpu       |
| intel i9 | cpu       |
| gtx1050  | gc        |
| gtx2060  | gc        |
| rtx1020  | gc        |
| g102     | mouse     |
| g202     | mouse     |
+----------+-----------+

    • 책 예제 (7-12)

      select P.name, C.cate_name 
from products as P 
inner join category as C 
on C.id = P.cate_id

카테고리에 중복 데이터 X, 상품과 카테고리는 N : 1 관계

  • INNER ON / WHERE AND 결합 차이점
    • 가독성 차이
      • FROM 뒤에 조건을 작성하지 않는다.
      • JOIN 조건과 함께 사용한다.
    • OUTER JOIN으로 변경할 때 수정
  • 외부키(FK, ForeignKey)
    • 테이블의 유일한 키는 PK
    • PK를 참조하는 열 → FK
  • 자기결합 ( Self Join)
    • 특별한 경우가 아니면 사용을 잘 하지 않는다.
    • 예시
      mysql> select * from family;
+----+------------+-----------+------+------------+
| id | first_name | last_name | age  | partner_id |
+----+------------+-----------+------+------------+
|  1 | 김         | 유정      |   31 |          2 |
|  2 | 이         | 철수      |   30 |          1 |
|  3 | 김         | 영희      |   21 |          5 |
|  4 | 박         | 동희      |   24 |          6 |
|  5 | 박         | 희철      |   21 |          3 |
|  6 | 안         | 철수      |   21 |          4 |
|  7 | 박         | 석열      |   14 |          8 |
|  8 | 김         | 재명      |   12 |          9 |
+----+------------+-----------+------+------------+
8 rows in set (0.00 sec)

      • Self Join 적용

        mysql> 
SELECT
f.id,
p.partner_id,
f.first_name AS first,
f.last_name AS last,
p.first_name AS partner_first,
p.last_name AS partner_last,
f.age
FROM family AS f
INNER JOIN family AS p 
ON f.id=p.partner_id 
ORDER BY id;

+----+------------+-------+--------+---------------+--------------+------+
| id | partner_id | first | last   | partner_first | partner_last | age  |
+----+------------+-------+--------+---------------+--------------+------+
|  1 |          2 | 김    | 유정   | 이            | 철수         |   31 |
|  2 |          1 | 이    | 철수   | 김            | 유정         |   30 |
|  3 |          5 | 김    | 영희   | 박            | 희철         |   21 |
|  4 |          6 | 박    | 동희   | 안            | 철수         |   24 |
|  5 |          3 | 박    | 희철   | 김            | 영희         |   21 |
|  6 |          4 | 안    | 철수   | 박            | 동희         |   21 |
|  8 |          9 | 김    | 재명   | 박            | 석열         |   12 |
+----+------------+-------+--------+---------------+--------------+------+

외부결합(OUTER JOIN)

내부 결합으로 결합조건(교집합)에 대한 결과를 알 수 있음

위 DB에서

‘카테고리가 없는 상품’(상품에 해당하는 카테고리 없음)과 반대로 ‘상품이 없는 카테고리’ 가 있었다.
하지만 결과에서 한번도 볼 수 없었다.

기준이 되는 테이블과 가져오는 테이블 사이를 중심으로 왼쪽이 기준 오른쪽이 INNER 테이블이 된다.

  • LEFT OUTER JOIN / LEFT JOIN prod 9, 10는 카테고리가 지정되지 않아 카테고리 테이블에 대한 데이터는 NULL 출력
    mysql> 
SELECT * FROM products 
LEFT OUTER JOIN category 
ON category.id = products.cate_id
			
+------+----------+-------+---------+--------+-----------+
| prod | name     | price | cate_id | id     | cate_name |
+------+----------+-------+---------+--------+-----------+
| 1    | gtx1050  | 200   | 3       | 3      | gc        |
| 2    | gtx2060  | 300   | 3       | 3      | gc        |
| 3    | intel i7 | 250   | 2       | 2      | cpu       |
| 4    | rtx1020  | 100   | 3       | 3      | gc        |
| 5    | intel i9 | 350   | 2       | 2      | cpu       |
| 6    | g102     | 100   | 1       | 1      | mouse     |
| 7    | g202     | 120   | 1       | 1      | mouse     |
| 8    | g111     | 12    | 4       | 4      | case      |
| 9    | 빅맥      | 500   | <null>  | <null> | <null>    |
| 10   | 와퍼      | 32    | <null>  | <null> | <null>    |
+------+----------+-------+---------+--------+-----------+
  • RIGTH OUTER JOIN / RIGHT JOIN 카테고리에 해당하는 상품이 없어 카테고리에 맞는 상품 NULL 출력
    mysql> 
SELECT * FROM products 
RIGHT OUTER JOIN category 
ON category.id = products.cate_id;
+------+----------+-------+---------+----+-----------+
| prod | name     | price | cate_id | id | cate_name |
+------+----------+-------+---------+----+-----------+
|    7 | g202     |   120 |       1 |  1 | mouse     |
|    6 | g102     |   100 |       1 |  1 | mouse     |
|    5 | intel i9 |   350 |       2 |  2 | cpu       |
|    3 | intel i7 |   250 |       2 |  2 | cpu       |
|    4 | rtx1020  |   100 |       3 |  3 | gc        |
|    2 | gtx2060  |   300 |       3 |  3 | gc        |
|    1 | gtx1050  |   200 |       3 |  3 | gc        |
|    8 | g111     |    12 |       4 |  4 | case      |
| NULL | NULL     |  NULL |    NULL |  5 | monitor   |
| NULL | NULL     |  NULL |    NULL |  6 | keyboard  |
+------+----------+-------+---------+----+-----------+
  • LEFT JOIN | RIGTH JOIN (합집합)
    • 일반적인 UNION - 사용 안됨
      SELECT * FROM products UNION SELECT * FROM category
    • JOIN UNION JOIN 을 사용한다.
      SELECT * FROM products LEFT JOIN category 
ON [category.id](http://category.id/) = products.cate_id 
UNION
SELECT * FROM products RIGHT JOIN category
ON [category.id](http://category.id/) = products.cate_id
+------+----------+-------+---------+------+-----------+
| prod | name     | price | cate_id | id   | cate_name |
+------+----------+-------+---------+------+-----------+
|    1 | gtx1050  |   200 |       3 |    3 | gc        |
|    2 | gtx2060  |   300 |       3 |    3 | gc        |
|    3 | intel i7 |   250 |       2 |    2 | cpu       |
|    4 | rtx1020  |   100 |       3 |    3 | gc        |
|    5 | intel i9 |   350 |       2 |    2 | cpu       |
|    6 | g102     |   100 |       1 |    1 | mouse     |
|    7 | g202     |   120 |       1 |    1 | mouse     |
|    8 | g111     |    12 |       4 |    4 | case      |
|    9 | 빅맥      |   500 |    NULL | NULL | NULL      |
|   10 | 와퍼      |    32 |    NULL | NULL | NULL      |
| NULL | NULL     |  NULL |    NULL |    5 | monitor   |
| NULL | NULL     |  NULL |    NULL |    6 | keyboard  |
+------+----------+-------+---------+------+-----------+
  • SQL Server에선 특수문자로 외부결합 가능 - 방언 사용 지양
profile
이재문
이제부터 백엔드 개발자
이전 포스트

SQL 스터디 - LIKE(검색, 패턴매칭)

다음 포스트

DRF tutorial

0개의 댓글