여러 테이블의 JOIN
한 순간에는 두 개의 데이터 집합만 조인에 참여한다는 개념으로 접근하면 여러 테이블의 조인도 비교적 쉽게 해결 할 수 있다.
Shop(매장), Ord(주문), Member(회원), 세 개의 테이블을 조인하려고 한다. 아래 내용을 기억하면서 조인을 작성해보자.
•
한 번에는 두 개의 데이터 집합만 조인을 한다.
•
조인할 때는 일반적으로 PK/FK를 조인 조건으로 사용한다.
•
조인 조건은 어느 한쪽 데이터 집합의 PK가 사용된다.
◦
PK 컬럼이 여러 개로 구성되어 있다면, PK를 구성하는 모든 컬럼을 조인 조건으로 사용해야 한다.
•
일반적으로 1:1 또는 1:M 관계에서만 조인이 된다.
◦
1:M 조인이 되면 1의 데이터는 M 건만큼 늘어난다.
매장(Shop), 주문(Ord), 회원(Member)의 ERD를 살펴보면 다음과 같다.
세 테이블을 한 번에 조인하지 말고, 우선 Shop과 Ord만 조인해보자.
-- [SQL-7-7-1] 2019년 12월에 오픈한 매장의 2020년 전체 주문 정보 조회
-- 단 PLAT(플래티넘) 등급 회원의 주문 정보만 조회할것
-- Shop과 Ord만 조인
SELECT T1.ShopId ,T1.ShopStartYmd ,T2.OrdNo ,T2.MemberId ,T2.OrdDtm ,T2.OrdAmt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2 ON (T2.ShopId = T1.ShopId)
WHERE T1.ShopStartYmd LIKE '201912%'
AND T2.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20210101','%Y%m%d')
ORDER BY T1.ShopID ,T2.OrdNo;
ShopId ShopStartYmd OrdNo MemberId OrdDtm OrdAmt
------ ------------ ----- -------- ------------------- --------
S051 20191217 1534 M1318 2020-02-04 13:30:00 4000.000
S051 20191217 1608 M1318 2020-02-06 13:30:00 4500.000
S051 20191217 1632 M1318 2020-02-08 13:30:00 8500.000
S051 20191217 1712 M1318 2020-02-13 13:30:00 8500.000
… 생략 …
SQL
복사
위 SQL을 통해 (Shop+Ord)라는 새로운 데이터 집합이 만들어진 것이다. 이제 이 데이터 집합에 Member를 추가로 조인하면 된다. Ord에 조인을 추가하는 것이 아니라 (Shop+Ord) 데이터 집합에 조인을 추가하는 것이다. (Shop+Ord) 데이터 집합에는 MemberId가 있으며, MemberId는 Member 테이블의 PK다. 그러므로 MemberId 컬럼을 이용해 두 데이터 집합을 조인처리하면 된다. 다음과 같다.
-- [SQL-7-7-2] 2019년 12월에 오픈한 매장의 2020년 전체 주문 정보 조회, 회원 정보도 필요
-- 단 PLAT(플래티넘) 등급 회원의 주문 정보만 조회할것
-- Member 조인 추가
SELECT T1.ShopId ,T1.ShopStartYmd ,T2.OrdNo ,T2.MemberId ,T2.OrdDtm ,T2.OrdAmt
,T3.NickNm ,T3.MemberGd
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2 ON (T2.ShopId = T1.ShopId)
INNER JOIN startdb.Member T3 ON (T3.MemberId = T2.MemberId)
WHERE T1.ShopStartYmd LIKE '201912%'
AND T2.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20210101','%Y%m%d')
AND T3.MemberGD = 'PLAT'
ORDER BY T1.ShopID ,T2.OrdNo;
SQL
복사
위 SQL에 따라 데이터 집합이 변형되는 과정을 그려보면 다음과 같다.
아래 내용을 떠올리면서 위의 조인 SQL과 그림을 다시 살펴보기 바랍니다.
•
한 번에는 두 개의 데이터 집합만 조인을 한다.
•
조인할 때는 일반적으로 PK/FK간에 조인이 이루어진다.
•
조인 조건은 어느 한쪽 데이터 집합의 PK가 사용된다.
◦
Shop과 Ord가 조인 될 때는 Shop의 PK가 사용되었다.
◦
(Shop+Ord) 데이터 집합과 Member가 조인될 때는, Member의 PK가 사용되었다.
이번에는 Shop과 Ord, OrdDet(주문상세)를 조인해보자. 먼저 ERD를 살펴보면 다음과 같다.
마찬가지로 세 테이블의 조인을 동시에 해결하지 말고, 두 테이블만 조인을 하자. Shop과 Ord만 조인해보면 다음과 같다.
-- [SQL-7-7-3] FLAG 매장이면서 2018년 4월 2일 오픈한 매장의,
-- 2020년 5월 1일부터 5월 3일까지의 주문과 주문상세 조회
-- Shop과 Ord를 먼저 조인
SELECT T1.ShopId ,T1.ShopOperTp ,T1.ShopNm, T1.ShopStartYmd
,T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2 ON (T2.ShopId = T1.ShopId)
WHERE T1.ShopOperTp = 'FLAG'
AND T1.ShopStartYmd = '20180402'
AND T2.OrdDtm >= STR_TO_DATE('20200501','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20200503','%Y%m%d')
ORDER BY T1.ShopId ,T2.OrdNo;
ShopId ShopOperTp ShopNm ShopStartYmd OrdNo OrdDtm OrdAmt
------ ---------- ---------------- ------------ ----- ------------------- --------
S003 FLAG Chicago-1st 20180402 2364 2020-05-01 08:00:00 4500.000
S003 FLAG Chicago-1st 20180402 2366 2020-05-02 08:00:00 4000.000
S003 FLAG Chicago-1st 20180402 2372 2020-05-02 10:00:00 6500.000
S003 FLAG Chicago-1st 20180402 2379 2020-05-02 10:00:00 3500.000
S003 FLAG Chicago-1st 20180402 2387 2020-05-02 11:00:00 4000.000
S017 FLAG Indianapolis-1st 20180402 2377 2020-05-02 10:00:00 4500.000
SQL
복사
위 SQL로 우리는 (Shop+Ord) 데이터 집합을 만들어냈다다. 이제 (Shop+Ord) 데이터 집합과 OrdDet를 조인해야 한다. 두 데이터 집합 간에 조인 조건 컬럼은 OrdNo(주문번호)다.
-- [SQL-7-7-4] FLAG 매장이면서 2018년 4월 2일 오픈한 매장의,
-- 2020년 5월 1일부터 5월 3일까지의 주문과 주문상세 조회
-- OrdDet 조인 추가
SELECT T1.ShopId ,T1.ShopOperTp ,T1.ShopNm, T1.ShopStartYmd
,T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
,T3.OrdDetNo ,T3.ItemId ,T3.OrdQty ,T3.SalePrc
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2 ON (T2.ShopId = T1.ShopId)
INNER JOIN startdb.OrdDet T3 ON (T3.OrdNo = T2.OrdNo)
WHERE T1.ShopOperTp = 'FLAG'
AND T1.ShopStartYmd = '20180402'
AND T2.OrdDtm >= STR_TO_DATE('20200501','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20200503','%Y%m%d')
ORDER BY T1.ShopId ,T2.OrdNo;
SQL
복사
(Shop+Ord) 데이터 집합과 OrdDet를 조인할 때, 왜 OrdNo가 조인 조건의 컬럼이 되었을까?
•
조인 컬럼은 어느 한쪽 테이블의 PK여야 한다.
•
조인이 이루어진 (Shop+Ord) 데이터 집합은 물리적인 테이블이 아니다.
•
PK는 물리적으로 구성된 제약 조건이다. 조인으로 만든 데이터 집합에는 물리적인 PK가 없다.
•
하지만 PK의 역할만 생각해보자. 데이터의 중복을 막고, 데이터를 식별하는 역할을 한다.
•
(Shop+Ord) 데이터 집합의 이러한 역할을 하는 컬럼이 바로 OrdNo다.
◦
1:M 관계에서 조인이 이루어지면 M쪽 테이블의 PK가 조인된 결과의 PK 역할을 할 수 있다.
•
그러므로 우리는 OrdNo를 조인 컬럼으로 선정해서 사용할 수 있다.
•
이와 같은 생각이 복잡하다면, 단순하게 Ord쪽의 PK를 조인 컬럼으로 사용했다고 심플하게 생각해도 된다.
위 조인 SQL을 그림으로 그려보면 다음과 같다.
사용자의 언어
사용자의 언어를 듣고, 그에 맞는 테이블과 관계를 떠올리고 SQL을 작성해야 한다. 안타깝게도 현장에서는 그 누구도 우리에게 어떤 테이블의 어떤 컬럼을 사용하라고 친절하게 설명해주지 않는다.
•
FLAG 매장의 2022년 12월 주문수량을 회원등급별, 상품카테고리별 집계해주세요.
요구사항을 들었으니 테이블과 테이블 관계를 분석하기 위해 ERD를 살펴보자.
ERD나 테이블 구조를 확인해 필요한 데이터를 뽑기 위해 어느 테이블에 어떤 컬럼을 사용해야 할지 정리해보자.
•
FLAG 매장 → Shop의 ShopOperTp가 FLAG
•
2022년 12월 주문 → Ord의 OrdDtm이 2022년 12월
•
회원등급별 → Member의 MemberGd
•
상품카테고리별 → Item의 ItemCat
•
주문수량 → OrdDet의 OrdQty
위 내용을 바탕으로 단계적으로 조인 SQL을 작성해보기 바란다. 시간이 걸리더라도 한 번에 SQL을 작성하려 하지 말고 조인이 추가될때마다 데이터를 살펴보고, 조인 컬럼에 고민해보면서 SQL을 작성하도록 하자. 데이터를 다루는 일은, 항상 생각하고 고민해야 하는 지적노동이다.
-- [SQL-7-7-5]
SELECT T3.MemberGd ,T5.ItemCat ,SUM(T4.OrdQty) OrdQty
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
INNER JOIN startdb.Member T3
ON (T3.MemberId = T2.MemberId)
INNER JOIN startdb.OrdDet T4
ON (T4.OrdNo = T2.OrdNo)
INNER JOIN startdb.Item T5
ON (T5.ItemId = T4.ItemId)
WHERE T1.ShopOperTp = 'FLAG'
AND T2.OrdDtm >= STR_TO_DATE('20221201','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230101','%Y%m%d')
GROUP BY T3.MemberGd ,T5.ItemCat
ORDER BY T3.MemberGd ,T5.ItemCat;
SQL
복사
데이터 건수의 변화
조인이 추가되는 과정에서 조인 결과 건수는 늘기도 하고 줄기도 한다. 이러한 특징을 알고 조인을 추가하거나 제거하면서 데이터가 변화되는 것을 살펴볼 필요가 있다. 자신이 작성한 SQL이 생각과 다른 결과가 나온다면 조인을 단계적으로 수행하면서 문제의 원인을 찾아내야 한다. 아래 SQL을 차례대로 실행하면서 결과 건수가 변해가는 것을 살펴보기 바란다.
-- [SQL-7-7-6]
-- S202 매장의 2023년 1월 1일 주문 조회 --> 두 건
SELECT T1.ShopId ,T1.ShopNm
,T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T2.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230102','%Y%m%d')
AND T1.ShopId = 'S202'
ORDER BY T1.ShopId ,T2.OrdNo;
ShopId ShopNm OrdNo OrdDtm OrdAmt
------ ---------------- ----- ------------------- --------
S202 Los Angeles-11th 67283 2023-01-01 19:30:00 4000.000
S202 Los Angeles-11th 67285 2023-01-01 20:30:00 9000.000
-- [SQL-7-7-7]
-- 위 조인 결과에 OrdDet를 조인으로 추가 --> 세 건
SELECT T1.ShopId ,T1.ShopNm
,T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
,T3.OrdDetNo ,T3.ItemId ,T3.OrdQty ,T3.SalePrc
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
INNER JOIN startdb.OrdDet T3
ON (T3.OrdNo = T2.OrdNo)
WHERE T2.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230102','%Y%m%d')
AND T1.ShopId = 'S202'
ORDER BY T1.ShopId ,T2.OrdNo;
ShopId ShopNm OrdNo OrdDtm OrdAmt OrdDetNo ItemId OrdQty SalePrc
------ ---------------- ----- ------------------- -------- -------- ------ ------ --------
S202 Los Angeles-11th 67283 2023-01-01 19:30:00 4000.000 1 LEMR 1 4000.000
S202 Los Angeles-11th 67285 2023-01-01 20:30:00 9000.000 1 AMR 1 4500.000
S202 Los Angeles-11th 67285 2023-01-01 20:30:00 9000.000 2 IAMB 1 4500.000
-- [SQL-7-7-8]
-- 위 조인 결과에, OrdDet에 ItemId에 대한 조건 추가 --> 한 건
SELECT T1.ShopId ,T1.ShopNm
,T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
,T3.OrdDetNo ,T3.ItemId ,T3.OrdQty ,T3.SalePrc
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
INNER JOIN startdb.OrdDet T3
ON (T3.OrdNo = T2.OrdNo)
WHERE T2.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230102','%Y%m%d')
AND T1.ShopId = 'S202'
AND T3.ItemId = 'AMR'
ORDER BY T1.ShopId ,T2.OrdNo;
ShopId ShopNm OrdNo OrdDtm OrdAmt OrdDetNo ItemId OrdQty SalePrc
------ ---------------- ----- ------------------- -------- -------- ------ ------ --------
S202 Los Angeles-11th 67285 2023-01-01 20:30:00 9000.000 1 AMR 1 4500.000
SQL
복사
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 7-7-1
주문번호 1번(OrdNo=1)에 대한 매장, 회원, 상품, 주문수량등 모든 정보를 보여주세요.
•
대상 테이블: 매장(Shop), 회원(Member), 주문(Ord), 주문상세(OrdDet), 상품(Item)
•
조회 조건: OrdNo가 1인 데이터
•
조회 컬럼: OrdNo, OrdDetm, ShopId, ShopNm, MemberId, NickNm, OrdDetNo, OrdQty, ItemId, ItemNm
OrdNo OrdDtm ShopId ShopNm MemberId NickNm OrdDetNo OrdQty ItemId ItemNm
----- ------------------- ------ --------------- -------- ------ -------- ------ ------ -----------------
1 2019-05-04 14:00:00 S047 San Antonio-3rd M0318 Green6 1 1 AMR Americano(R)
1 2019-05-04 14:00:00 S047 San Antonio-3rd M0318 Green6 2 1 IAMB Iced Americano(B)
SQL
복사
BOOSTER QUIZ 7-7-2
직영점이면서 매장면적이 100이하이고 플래티넘 회원에 대한 2022년 12월 24일의 주문을 조회해주세요. 주문, 매장, 회원, 주문상세 등의 모든 정보를 보여주세요.
•
대상 테이블: 매장(Shop), 회원(Member), 주문(Ord), 주문상세(OrdDet), 상품(Item)
•
조회 조건
◦
ShopOperTp가 DRCT(직영점)이고, ShopSzie가 100 이하이면서
◦
MemberGd가 PLAT이고,
◦
OrdDtm이 2022년 12월 24일인 데이터
•
조회 컬럼
◦
OrdNo, OrdDtm, ShopId, ShopNm, ShopOperTp, ShopSize, OrdDetNo, OrdQty, ItemId, ItemNm
•
정렬 기준: OrdNo로 오름차순 후, OrdDetNo로 오름차순하시오.
OrdNo OrdDtm ShopId ShopNm ShopOperTp ShopSize OrdDetNo OrdQty ItemId ItemNm
----- ------------------- ------ ----------------- ---------- -------- -------- ------ ------ -------------------
60163 2022-12-24 10:00:00 S254 Columbus-13th DRCT 93 1 1 CLR Cafe Latte(R)
60176 2022-12-24 10:00:00 S249 Dallas-13th DRCT 83 1 1 CLB Cafe Latte(B)
60202 2022-12-24 10:30:00 S245 Phoenix-13th DRCT 75 1 1 BGLR Bagel(R)
60205 2022-12-24 10:30:00 S240 Washington-12th DRCT 65 1 1 CLR Cafe Latte(R)
60205 2022-12-24 10:30:00 S240 Washington-12th DRCT 65 2 1 HCHB Hot Chocolate(B)
...생략...
SQL
복사
BOOSTER QUIZ 7-7-3
[BOOSTER QUIZ 7-7-2]에서 구한 내용을 사용해 ItemId별로 주문수량 합계를 구하시오.
•
정렬 기준: 주문수량합계로 내림차순, ItemId로 오름차순 조회하시오.
ItemId ItemNm 주문수량합계
------ ------------------- ------------
CLR Cafe Latte(R) 7
HCHR Hot Chocolate(R) 5
BGLR Bagel(R) 4
CLB Cafe Latte(B) 4
HCHB Hot Chocolate(B) 4
LEMR Lemonade(R) 4
BMFR Blueberry Muffin(R) 3
AMR Americano(R) 1
CMFR Chocolate Muffin(R) 1
IAMB Iced Americano(B) 1
ICLB Iced Cafe Latte(B) 1
ICLR Iced Cafe Latte(R) 1
SQL
복사
Upper: 7. JOIN
