관계형 데이터베이스는 데이터의 중복을 피하기 위해 여러개의 테이블에 데이터를 나누어 저장한다.
나누어진 데이터를 필요에 따라 가져오기 위해서 데이터를 결합해주는 기능이 JOIN이다.
JOIN의 종류는 교차조인(CROSS JOIN)과 내부조인 (INNER JOIN)과
외부조인 (LEFT OUTER JOIN, RIGHT OUTER, FULL OUTER JOIN)등이 있다.
※ 예제 데이터
-- 상품 테이블(product)
product_id|product_name|price|create_date|
----------+------------+-----+-----------+
1|product_1 | 100|2023-06-01 |
2|product_2 | 200|2023-07-01 |
3|product_3 | 300|2023-07-10 |
-- 재고 테이블 (store)
store_id|product_id|store_count|update_date|
--------+----------+-----------+-----------+
1| 1| 5| 2023-06-01|
2| 2| 5| 2023-07-01|
3| 2| 3| 2023-07-03|
4| 4| 3| 2023-07-04|
-- 재고이력 테이블(store_history)
store_history_id|store_id|update_count|update_reason|update_date|
----------------+--------+------------+-------------+-----------+
1| 1| 5|납품 | 2023-06-01|
2| 2| 5|납품 | 2023-07-01|
3| 2| -2|반품(파손) | 2023-07-01|
4| 3| 2|납품 | 2023-07-03|
5| 3| 1|납품 | 2023-07-03|
6| 2| 2|납품 | 2023-07-02|
1. 교차조인(CROSS JOIN)
교차조인은 두 테이블을 결합했을 때 얻을 수 있는 모든 행의 데이터를 가져올 수 있다.
나의 경우 실무에서는 거의 써본적인 없는 것 같다.
select
p.*, s.*
from product p , store s
order by p.product_id , s.store_id
;
-- 결과
product_id|product_name|price|create_date|store_id|product_id|store_count|update_date|
----------+------------+-----+-----------+--------+----------+-----------+-----------+
1|product_1 | 100|2023-06-01 | 1| 1| 5| 2023-06-01|
1|product_1 | 100|2023-06-01 | 2| 2| 5| 2023-07-01|
1|product_1 | 100|2023-06-01 | 3| 2| 3| 2023-07-03|
1|product_1 | 100|2023-06-01 | 4| 4| 3| 2023-07-04|
2|product_2 | 200|2023-07-01 | 1| 1| 5| 2023-06-01|
2|product_2 | 200|2023-07-01 | 2| 2| 5| 2023-07-01|
2|product_2 | 200|2023-07-01 | 3| 2| 3| 2023-07-03|
2|product_2 | 200|2023-07-01 | 4| 4| 3| 2023-07-04|
3|product_3 | 300|2023-07-10 | 1| 1| 5| 2023-06-01|
3|product_3 | 300|2023-07-10 | 2| 2| 5| 2023-07-01|
3|product_3 | 300|2023-07-10 | 3| 2| 3| 2023-07-03|
3|product_3 | 300|2023-07-10 | 4| 4| 3| 2023-07-04|위의 SQL은 암시적 표현이고, 명시적 표현의 SQL은 아래와 같다.
select
p.*, s.*
from product p cross join store s
order by p.product_id , s.store_id
;
-- 결과
product_id|product_name|price|create_date|store_id|product_id|store_count|update_date|
----------+------------+-----+-----------+--------+----------+-----------+-----------+
1|product_1 | 100|2023-06-01 | 1| 1| 5| 2023-06-01|
1|product_1 | 100|2023-06-01 | 2| 2| 5| 2023-07-01|
1|product_1 | 100|2023-06-01 | 3| 2| 3| 2023-07-03|
1|product_1 | 100|2023-06-01 | 4| 4| 3| 2023-07-04|
2|product_2 | 200|2023-07-01 | 1| 1| 5| 2023-06-01|
2|product_2 | 200|2023-07-01 | 2| 2| 5| 2023-07-01|
2|product_2 | 200|2023-07-01 | 3| 2| 3| 2023-07-03|
2|product_2 | 200|2023-07-01 | 4| 4| 3| 2023-07-04|
3|product_3 | 300|2023-07-10 | 1| 1| 5| 2023-06-01|
3|product_3 | 300|2023-07-10 | 2| 2| 5| 2023-07-01|
3|product_3 | 300|2023-07-10 | 3| 2| 3| 2023-07-03|
3|product_3 | 300|2023-07-10 | 4| 4| 3| 2023-07-04|
2. 내부조인 (INNER JOIN)
교차조인의 경우에는 조건에 상관없이 결합될수 있는 모든 경우의 수를 가져왔지만,
내부조인의 경우 두 테이블 모두에 데이터가 있는 경우의 행만을 가져온다. (교집합)
나의 경우 내부조인은 외부 조인과 함께 실무에서 가장많이 사용하고 있는 조인이다.
아래의 SQL은 product_id를 키로 상품 테이블과 재고 테이블을 결합하여 데이터를 출력하는 SQL이다.
-- 명시적 SQL
select
p.*, s.*
from product p inner join store s on p.product_id = s.product_id
order by p.product_id , s.store_id
;
-- 명시적 SQL : 양쪽 테이블에 같은 컬럼명이 있다면 on대신 using키워드를 사용해 결합할 수 있다.
select
p.*, s.*
from product p inner join store s using(product_id)
order by p.product_id , s.store_id
;
-- 암시적 SQL
select
p.*, s.*
from product p , store s
where p.product_id = s.product_id
order by p.product_id , s.store_id
;
-- 결과
product_id|product_name|price|create_date|store_id|product_id|store_count|update_date|
----------+------------+-----+-----------+--------+----------+-----------+-----------+
1|product_1 | 100|2023-06-01 | 1| 1| 5| 2023-06-01|
2|product_2 | 200|2023-07-01 | 2| 2| 5| 2023-07-01|
2|product_2 | 200|2023-07-01 | 3| 2| 3| 2023-07-03|결과 데이터를 보면
- product_id의 값이 1인 재고 테이블의 행은 1개이므로 product_id의 값이 1인 경우의 행은 1개 행이 출력되었고,
- product_id의 값이 2인 재고 테이블의 행은 2개이므로 product_id의 값이 2인 경우의 행은 1개 행이 출력되었다.
- 또한 product_id의 값이 3인 행이 재고 테이블에 없고 product_id의 값이 4인 경우의 행은 상품 테이블에 없으므로
product_id의 값이3, 4의 행은 출력되지 않았다.
3. 외부조인 (LEFT OUTER JOIN, RIGHT OUTER, FULL OUTER JOIN)
외부조인의 경우에는 기준이 되는 테이블의 모든 데이터와 두 테이블 모두에 데이터가 있는 경우의 행을 가져온다.
결합하려는 테이블에 기준이 되는 데이터가 존재하지 않을 경우 결합하려는 테이블의 행은 NULL이 출력된다.
1) LEFT OUTER JOIN
상품정보를 등록과 함께 재고 정보가 등록 되는 것이 아니라 납품과 함께 재고 정보가 등록 된다고 가정하자,
이때 현황 파악을 위해 재고 정보가 있는지 없는 지에 상관없이 모든 상품과 재고의 정보를 보고 싶을 수 있다.
이럴때는 아래와 같은 SQL을 사용하여 원하는 결과를 얻을 수 있다.
select
p.*, s.*
from product p left outer join store s on p.product_id = s.product_id
order by p.product_id , s.store_id
;
-- 결과
product_id|product_name|price|create_date|store_id|product_id|store_count|update_date|
----------+------------+-----+-----------+--------+----------+-----------+-----------+
1|product_1 | 100|2023-06-01 | 1| 1| 5| 2023-06-01|
2|product_2 | 200|2023-07-01 | 2| 2| 5| 2023-07-01|
2|product_2 | 200|2023-07-01 | 3| 2| 3| 2023-07-03|
3|product_3 | 300|2023-07-10 | | | | |내부조인시에는 출력되지 않았던 product_id의 값이 3인 행도 출력이 되는 것을 알 수 있다.
2) RIGHT OUTER JOIN
재고 정리를 하다 상품정보 등록이 누락된 상품이 재고에 있는것을 발견했다고 가정하자.
이때 현황 파악을 위해 상품 정보가 있는지 없는 지에 상관없이 모든 상품과 재고의 정보를 보고 싶을 수 있다.
이럴때는 아래와 같은 SQL을 사용하여 원하는 결과를 얻을 수 있다.
select
p.*, s.*
from product p right outer join store s on p.product_id = s.product_id
order by p.product_id , s.store_id
;
-- 결과
product_id|product_name|price|create_date|store_id|product_id|store_count|update_date|
----------+------------+-----+-----------+--------+----------+-----------+-----------+
1|product_1 | 100|2023-06-01 | 1| 1| 5| 2023-06-01|
2|product_2 | 200|2023-07-01 | 2| 2| 5| 2023-07-01|
2|product_2 | 200|2023-07-01 | 3| 2| 3| 2023-07-03|
| | | | 4| 4| 3| 2023-07-04|내부조인시에는 출력되지 않았던 product_id의 값이 4인 행도 출력이 되는 것을 알 수 있다.
3) FULL OUTER JOIN
LEFT OUTER JOIN과 RIGHT OUTER JOIN의 결과를 한번에 보려면 FULL OUTER JOIN을 사용하면 된다.
select
p.*, s.*
from product p full outer join store s on p.product_id = s.product_id
order by p.product_id , s.store_id
;
-- 결과
product_id|product_name|price|create_date|store_id|product_id|store_count|update_date|
----------+------------+-----+-----------+--------+----------+-----------+-----------+
1|product_1 | 100|2023-06-01 | 1| 1| 5| 2023-06-01|
2|product_2 | 200|2023-07-01 | 2| 2| 5| 2023-07-01|
2|product_2 | 200|2023-07-01 | 3| 2| 3| 2023-07-03|
3|product_3 | 300|2023-07-10 | | | | |
| | | | 4| 4| 3| 2023-07-04|※ MySQL의 경우 FULL OUTER JOIN이 없다고 한다.
이경우 LEFT와 RIGHT 조인의 결과를 UNION하면 같은 결과를 얻을 수 있다.
4. N개의 테이블의 결합할 때의 데이터 출력
상품, 재고, 재고이력 총 3개의 테이블을 외부조인 한다고 하면 데이터의 출력은 어떻게 될까?
select
p.*, s.*, sh.*
from product p left outer join store s on p.product_id = s.product_id
left outer join store_history sh on s.store_id = sh.store_id
order by p.product_id , s.store_id
;
-- 결과
product_id|product_name|price|create_date|store_id|product_id|store_count|update_date|store_history_id|store_id|update_count|update_reason|update_date|
----------+------------+-----+-----------+--------+----------+-----------+-----------+----------------+--------+------------+-------------+-----------+
1|product_1 | 100|2023-06-01 | 1| 1| 5| 2023-06-01| 1| 1| 5|납품 | 2023-06-01|
2|product_2 | 200|2023-07-01 | 2| 2| 5| 2023-07-01| 6| 2| 2|납품 | 2023-07-02|
2|product_2 | 200|2023-07-01 | 2| 2| 5| 2023-07-01| 2| 2| 5|납품 | 2023-07-01|
2|product_2 | 200|2023-07-01 | 2| 2| 5| 2023-07-01| 3| 2| -2|반품(파손) | 2023-07-01|
2|product_2 | 200|2023-07-01 | 3| 2| 3| 2023-07-03| 5| 3| 1|납품 | 2023-07-03|
2|product_2 | 200|2023-07-01 | 3| 2| 3| 2023-07-03| 4| 3| 2|납품 | 2023-07-03|
3|product_3 | 300|2023-07-10 | | | | | | | | | |결과를 보면 재고 이력의 store_id가 2인 행 수 3만큼, store_id가 3인 행 수 2만큼 결과로 출력되는 데이터의 행 수가 늘어 났다.
이처럼 출력되는 데이터 행의 수는 조건에 일치하는 (상품테이블의 행수 * 재고 테이블의 행수 * 재고 이력 테이블의 행수)가 된다.
'데이터베이스 > SQL' 카테고리의 다른 글
| GROUP BY 절 정리 (0) | 2023.07.22 |
|---|---|
| 자주쓰는 WHERE문 정리 (0) | 2023.07.16 |
댓글