[SQL] Table (union all, inner/left/right join)

안녕하세요

공부하는웅이 입니다

계속해서 SQL 개념에대해서 정리해보도록 하겠습니다.

 

이번 포스팅은 테이블에 관한 내용입니다. 테이블에는 여러 엔티티와 속성들을 포함하고 있어서 데이터베이스의 핵심입니다. 

 

그럼 시작해보도록 하겠습니다.

 

---

 

집합 연산

UNION으로 합집합 구하기

SQL에서는 SELECT 명령의 실행 결과를 하나의 집합으로 다룰 수 있다.

합집합을 계산하는 경우 UNION 키워드를 사용한다.

 

select * from new_table_1;	select * from new_table_2	select * from new_table_1,new_table_2;

 

이러한 sample71_a와 sample71_b라는 두 개의 테이블이 있다고 했을 때

UNION을 활용하면 다음과 같은 결과가 나온다.

 

다음 쿼리를 실행하였다.

쿼리 결과

 

한 번의 쿼리 실행으로 두 개의 SELECT 명령이 내부적으로 실행되는 형식이다.

UNION을 이용하면 여러 개의 SELECT 명령을 하나로 묶을 수 있다.

이때 각각의 SELECT 명령의 열의 내용은 서로 일치해야 한다.

앞서 살펴본 두 테이블은 열명은 다르지만 열의 개수와 자료형이 동일하기에 일치하다고 볼 수 있다.

​

SELECT 명령들을 UNION으로 묶을 때 나열 순서는 합집합의 결과에 영향을 주지 않지만, 결괏값의 나열 순서는 달라질 수 있다.

​

UNION을 사용할 때의 ORDER BY

UNION으로 SELECT 명령을 결합해 합집합을 구하는 경우,

각 SELECT 명령에 ORDER BY를 지정해 정렬할 수는 없다.

ORDER BY를 지정할 때는 마지막 SELECT 명령에만 지정한다.

 

 

UNION ALL

UNION은 기본적으로 중복을 제거하여 결과를 출력한다.

그러나 중복을 제거하지 않고 결과를 출력하고 싶다면, UNION ALL을 사용하면 된다.

즉, 중복 값이 없는 경우 UNION ALL을 사용하는 편이 좋은 성능을 보여준다.

 

추가적으로 MySQL에서는 지원되지 않지만, 교집합은 INTERSECT, 차집합은 EXCEPT를 사용한다.

 

 

테이블 결합

 

기존에 집합 연산이 세로 방향(행)으로 데이터가 늘어나거나 줄어드는 것이었다면, 결합은 가로(열) 방향으로 데이터가 늘어나는 계산이다.

보통 데이터베이스는 하나의 테이블에 많은 데이터를 저장하지 않고 몇 개의 테이블로 나누어 저장한다.

여러 개로 나뉜 데이터를 하나로 묶어내는 방법이 테이블 결합이다.

​

곱집합과 교차결합

곱집합은 합집합이나 교집합처럼 집합의 연산 방법 중 하나로,

두 개의 집합을 곱하는 연산 방법으로 '적집합' 또는 '카티전곱'. '카티시안 제곱'이라고 불린다.

 

출처 : SQL 첫걸음

 

 

교차결합(Cross Join)

지금까지 SELECT 명령에는 FROM 구에 하나의 테이블만 지정했다.

만약 FROM 구에 테이블을 두 개 지정한다면 이들은 곱집합으로 계산된다.

앞에서 한번 실행해보였다. 두 개의 열이 한번에 보인다.

이때 FROM 구에 복수의 테이블을 지정하면 교차결합을 한다.

 

UNION으로 합집합을 구했을 경우에는 세로 방향으로 더해지게 되는 반면

FROM 구로 테이블을 결합할 경우에는 가로 방향으로 더해지게 된다(이떄 from은 where의 from이다)

 

 

내부 결합

테이블을 세 개, 네 개로도 지정해서 교차결합을 할 수도 있지만 이럴 경우 조합 수가 엄청나게 늘어나 집합이 거대해지므로 많은 테이블을 교차결합하는 경우는 드물다.

즉, 결합 방법으로는 교차결합보다 내부 결합이 자주 사용된다.

관계형 데이터베이스는 테이블의 데이터가 유일한 값을 가지도록 권장한다.

즉, 기본 키(primary key)를 가지도록 하는 게 좋다는 것이다.

​

내부 결합을 위해 두 가지 테이블을 만들었다.

 

# 상품 테이블 만들기
CREATE TABLE '상품' (
        '상품코드' CHAR(4) NOT NULL,
        '상품명' VARCHAR2(20),
        '메이커명' VARCHAR(20),
        '가격' INT,
        '상품분류' VARCHAR2(20)
        PRIMARY KEY('상품코드')
);

 

[상품 테이블]

상품코드	상품명	메이커명	가격	상품분류
0001	상품a	ㄱ메이커	1000	생필품
0002	상품b	ㄴ메이커	2000	생필품
0003	상품c	ㄷ메이커	19800	생활용품

 

# 재고수 테이블 작성하기
CREATE TABLE '재고수'(
         '상품코드' CHAR(4),
         '입고날짜' DATE,
         '재고수' INTEGER
);

 

[재고수 테이블]

상품코드	입고날짜	재고수
0001	2021-09-10	200
0002	2021-10-10	500
0003	2021-11-10	10

 

다른 테이블의 데이터를 참조해야 하는 경우, 참조할 테이블의 기본 키('상품코드')와

동일한 이름과 자료형으로 열을 만들어서 행을 연결하는 경우가 많다.

​

이 두 테이블을 가지고 내부 결합을 해보는데, 원하는 결과는 다음과 같다.

 

첫번째, 교차 결합 시행한다.

 

select * from '상품', '재고수';

 

상품코드	상품명	메이커명	가격	상품분류	상품코드	재고수
0001	상품a	ㄱ메이커	1000	생필품	0001	200
0002	상품b	ㄴ메이커	2000	생필품	0001	200
0003	상품c	ㄷ메이커	19800	생활용품	0001	200
0001	상품a	ㄱ메이커	1000	생필품	0002	500
0002	상품b	ㄴ메이커	2000	생필품	0002	500
0003	상품c	ㄷ메이커	19800	생활용품	0002	500
0001	상품a	ㄱ메이커	1000	생필품	0003	10
0002	상품b	ㄴ메이커	2000	생필품	0003	10
0003	상품c	ㄷ메이커	19800	생활용품	0003	10

 

왼쪽이 상품 테이블의 데이터, 오른쪽이 재고수 테이블의 데이터이다.

 

지금보면, 상품코드가 서로 맞지 않고 있음을 볼 수 있다. 두번째행, 0002에서 0001의 상품코드가 연결되어있다. 

 

이렇게 생성된 집합에서 원하는 데이터를 검색하기 위해 WHERE 구로 상품코드가 같다는 조건을 지정한다.

 

SELECT * FROM '상품', '재고수'

WHERE '상품'.'상품코드' = '재고수'.'상품코드';

내부결합

상품코드	상품명	메이커명	가격	상품분류	상품코드	재고수
0001	상품a	ㄱ메이커	1000	생필품	0001	200
0002	상품b	ㄴ메이커	2000	생필품	0002	500
0003	상품c	ㄷ메이커	19800	생활용품	0003	10

이렇게 교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것을 내부 결합(Inner Join)이라 부른다.

 

마지막으로 상품 분류가 생필품이라는 조건이 필요하다.

SELECT A.'상품명', B.'재고수'
FROM '상품' AS A,
         '재고수' AS B
WHERE A.'상품코드' = B.'상품코드'
AND A.'상품분류 ' = '식료품'

 

상품명	재고수
상품a	200
상품b	500

 

첫 번째 조건식은 교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것이기 때문에 결합 조건이다.

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

​

 

INNER JOIN으로 내부 결합하기

위에서 사용한 결합 방법을 간단히 정리하면 다음과 같다.

​

- FROM 구에 테이블을 복수 지정해 가로 방향으로 테이블을 결합할 수 있다.

- 교차결합을 하면 곱집합으로 계산된다.

- WHERE 조건을 지정해 곱집합에서 필요한 조합만 검색할 수 있다.

​

이와 같은 결합 방법은 사실 구식이다.

최근에는 INNER JOIN 키워드를 사용한 결합 방법이 일반적으로 통용되고 있다고 하니, 숙지하자.

SELECT *
FROM 테이블명1 

INNER JOIN 테이블명2
ON 결합조건 # 결합 조건 지정

WHRE 조건 # 검색 조건

 

 

 

외부 결합

외부 결합이라고 해도 교차결합으로 결합 조건을 지정하여 검색한다는 기본적인 사고방식은 같다.

외부 결합은 '어느 한 쪽에만 존재하는 데이터행을 어떻게 다룰지'를 변경할 수 있는 결합 방법이다.

상품코드	상품명	메이커명	가격	상품분류
0001	상품a	ㄱ메이커	1000	생필품
0002	상품b	ㄴ메이커	2000	생필품
0003	상품c	ㄷ메이커	19800	생활용품
0004	추가상품 d	ㄹ메이커	3000	생필품

기존에 사용하던 상품 테이블에 상품코드가 0004인 새로운 상품을 추가했다.

재고수 테이블에는 아직 이 상품에 대한 데이터가 없다.

따라서 내부 결합 결과에서도 상품코드 0004 = 0004가 되는 행이 존재하지 않으므로

상품코드가 0004인 상품은 제외된다.

 

이렇듯 상품 테이블과 재고수 테이블 중 상품 테이블에만 행이 존재하는 경우에 외부 결합을 사용하면 된다.

외부 결합은 결합하는 테이블 중에 어느 쪽을 기준으로 할지 결정할 수 있다.

 

SELECT A.'상품명', B.'재고수'
FROM '상품' A

LEFT JOIN '재고수' B
ON A.'상품코드' = B.'상품코드'

WHERE A.'상품분류' = '생필품';

 

 

상품명	재고수
상품a	200
상품b	500
추가상품	NULL #추가상품에 대한 재고수의 값이 없으므로 NULL이 출력된다. #만약 상품 테이블을 오른쪽에 지정하는 경우나 재고 테이블을 기준으로 하고 싶은 경우에는 #RIGHT JOIN을 사용해 외부 결합을 시행한다.

 

결론  :
아직 JOIN에 대해서 정확한 개념이 잡히진 않았지만, 반복하다보면 터득할 것 같다. 특히 특정 예를 머릿속에 두고 암기하는 것이 개념을 더 잘 이해하는 방법일 것 같다.