누가? 누구랑? 무엇으로 조인하는가?
“누가? 누구랑? 무엇으로 조인하는가?.”의 기본원칙은 다음과 같다.
•
관계가 있는 테이블 간에 관계 컬럼으로 조인이 이루어진다.
“일반적”으로 조인은 관계가 있는 테이블 간에, 관계 컬럼(PK/FK)을 통해 이루어진다. “일반적”을 강조한 이유는 반대로 꼭 그렇지 않은 경우도 있기 때문이다. DBMS는 조인 조건만 만족하면 무조건 데이터를 결합(조인)한다. 실제 테이블 간에 관계(PK/FK)가 있건 없건 상관없다. 하지만 조인을 배우기 시작한 단계에서는 ‘관계’가 있는 테이블 간에 ‘관계’ 컬럼으로 조인이 이루어진다고 생각하는 것이 이해에 도움이 된다.
위의 ERD를 살펴보면 Shop(매장)과 Ord(주문)간에 관계선이 그려져 있다. 그러므로 두 테이블은 서로 조인을 할 수 있다. Shop 테이블의 PK인 ShopId가 Ord 테이블에서는 FK로 사용되고 있다. PK/FK 컬럼인 ShopId 컬럼을 사용해 조인을 처리하면 된다. 아래와 같이 조인 SQL을 작성할 수 있다.
-- [SQL-7-3-1]
-- Ord 테이블의 ShopId는 Shop 테이블을 참조한다.
-- Shop 테이블의 ShopId는 Ord 테이블에 참조된다.
SELECT T1.ShopId ,T1.ShopNm, T1.ShopSize
,T2.OrdNo ,T2.OrdAmt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T1.ShopId = 'S200'
AND T2.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230102','%Y%m%d')
ORDER BY T1.ShopId;
SQL
복사
이번에는 Memberd와 Ord를 살펴보자. 두 테이블 간에 관계선이 그려져 있고, MemberId가 Member의 PK이면서 Ord에서는 FK다. 두 테이블을 연결하는 조건 컬럼으로 MemberId를 사용할 수 있다는 뜻이다.
-- [SQL-7-3-2]
-- Ord 테이블의 MemberId는 Member 테이블을 참조한다.
-- Member 테이블의 MemberId는 Ord 테이블에 참조된다.
SELECT T1.MemberId ,T1.NickNm
,T2.OrdNo ,T2.MemberId ,T2.OrdAmt
FROM startdb.Member T1
INNER JOIN startdb.Ord T2
ON (T2.MemberId = T1.MemberId)
WHERE T1.MemberId = 'M0001'
AND T2.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230102','%Y%m%d');
SQL
복사
다양하게 조인해보기
ERD를 살펴보고, 다양한 테이블 간에 조인을 해보자. 조인에 익숙해지기 위해 필요한 과정이다.
위 ERD를 참고하고, 아래 조인 SQL의 괄호 부분을 채워서 조인 SQL을 완성하고 실행해보기 바란다.
-- [SQL-7-3-3]
-- Shop과 Ord 조인, 두 테이블 간에 PK/FK 컬럼인 ( )를 사용
SELECT T1.ShopId ,T1.ShopNm ,T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON ( )
WHERE T1.ShopNm = 'Seattle-1st'
AND T2.OrdDtm >= STR_TO_DATE('20191001','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20191101','%Y%m%d');
-- Member와 Ord 조인, 두 테이블 간에 PK/FK 컬럼인 ( )를 사용
SELECT T1.MemberId ,T1.NickNm ,T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
FROM startdb.Member T1
INNER JOIN startdb.Ord T2
ON ( )
WHERE T1.NickNm = 'Air'
AND T2.OrdDtm >= STR_TO_DATE('20191001','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20191101','%Y%m%d')
AND T2.OrdAmt >= 7000;
-- [SQL-7-3-4]
-- Ord와 OrdDet 조인, 두 테이블 간에 PK/FK 컬럼인 ( )를 사용
SELECT T1.OrdNo ,T1.OrdDtm ,T1.OrdAmt ,T2.OrdDetNo ,T2.ItemId ,T2.OrdQty ,T2.SalePrc
FROM startdb.Ord T1
INNER JOIN startdb.OrdDet T2
ON ( )
WHERE T1.ShopId = 'S010'
AND T1.OrdDtm >= STR_TO_DATE('20191001','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20191101','%Y%m%d');
-- [SQL-7-3-5]
-- OrdDet와 Item 조인, 두 테이블 간에 PK/FK 컬럼인 ( )를 사용
SELECT T1.OrdNo ,T1.OrdDetNo ,T1.ItemId ,T1.OrdQty ,T1.SalePrc ,T2.ItemNm ,T2.ItemCat ,T2.HotColdCd
FROM startdb.OrdDet T1
INNER JOIN startdb.Item T2
ON ( )
WHERE T1.OrdNo = 100;
-- [SQL-7-3-6]
-- Item과 ItemCat 조인, 두 테이블 간에 PK/FK 컬럼인 ( )를 사용
SELECT T1.ItemCat ,T1.ItemCatNm ,T2.ItemId ,T2.ItemNm ,T2.HotColdCd
FROM startdb.ItemCat T1
INNER JOIN startdb.Item T2
ON ( )
WHERE T2.HotColdCd = 'HOT'
AND T2.ItemSizeCd = 'BIG';
SQL
복사
1:1, 1:M, M:M
두 테이블이 조인하면 데이터 건수가 늘어날 수도 있고 줄어들 수도 있다. 또는 원래의 건수가 그대로 유지될 수도 있다. 이러한 조인 결과의 건수 변화는 조인에 참여하는 레코드간의 관계 차수와 관련이 있다.
•
1:1 의 관계 차수
◦
A쪽 레코드 한 건이 B쪽 레코드 한 건과 조인한다.
◦
B쪽 레코드 한 건도 A쪽 레코드 한 건과 조인한다.
◦
조인 결과의 건수가 A나 B의 건수보다 절대 클 수 없다.
•
1:M 의 관계 차수
◦
A쪽 레코드 한 건이 B쪽 레코드 여러 건과 조인한다.
◦
B쪽 레코드 한 건은 A쪽 레코드 한 건과 조인한다.
◦
조인 결과는 1쪽의 데이터가 M쪽 건수만큼 늘어날 수 있다.
▪
조인 조건에 만족했다면, 1쪽 데이터가 M쪽 건수만큼 늘어날 수 있다.
▪
단, M쪽 데이터 건수를 넘어설 수는 없다.
•
M:M 의 관계 차수
◦
A쪽 레코드 한 건이 B쪽 레코드 여러 건과 조인한다.
◦
B쪽 레코드 한 건이 A쪽 레코드 여러 건과 조인한다.
◦
조인 건수가 예상할 수 없을 만큼 늘어날 수 있다.
▪
조인 결과가 중복되어 정확한 데이터 활용이 어려워진다.
1:1 조인의 예를 살펴보자. 아래는 넷플릭스의 컨텐츠와 컨텐츠추가정보를 가상으로 설계해본 것이다. 두 테이블은 1:1 관계다.
두 테이블의 조인 컬럼은 컨텐츠ID다. 해당 컬럼으로 조인을 하면 결과 건수는 조인에 참여하는 건수 이상으로 늘어날 수 없다. 두 테이블은 1:1 관계이기 때문이다. 단, 조인 조건에 만족하지 못한다면 결과 건수가 줄어들 수는 있다.
아래는 1:M 조인의 예를 위한 넷플릭스의 회원과 프로필 테이블이다. 두 테이블은 회원ID로 조인을 할 수 있다. 넷플릭스를 사용해봤다면 한 명의 회원이 여러 개 프로필을 사용할 수 있다는 점을 알 것이다.
1:M 관계를 조인하면 1인 데이터는 M쪽의 건수만큼 늘어날 수 있다. 정상적으로 조인이 이루어졌다면 결과 건수는 M 건 이상으로 늘어날 수는 없다. 단, 1:1과 마찬가지로 조인 조건을 만족하지 않으면 결과 건수가 줄어들 수는 있다.
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 7-3-1
S062 매장의 2023년 3월 31일 7시부터 12시 사이 주문과 주문 상세 내역을 출력해주세요.
•
대상 테이블: 주문(Ord)과 주문상세(OrdDet)
•
조회 조건: ShopId가 S062만, OrdDtm이 2023년 3월 31일 07시부터 OrdDtm이 2023년 3월 31일 12시 이전
•
조회 컬럼: OrdNo, OrdDtm, ShopId, OrdDetNo, ItemId, OrdQty, SalePrc
OrdNo OrdDtm ShopId OrdDetNo ItemId OrdQty SalePrc
----- ------------------- ------ -------- ------ ------ --------
89925 2023-03-31 09:00:00 S062 1 BMFR 1 3500.000
90137 2023-03-31 11:00:00 S062 1 CLR 1 5000.000
SQL
복사
BOOSTER QUIZ 7-3-2
2023년 1월 주문 중에, 아메리카노빅사이즈 관련 주문 정보를 출력해주세요.
•
대상 테이블: 주문(Ord)과 주문상세(OrdDet)
•
조회 조건: OrdDtm이 2023년 1월이면서, ItemId가 AMB(아메리카노빅사이즈)인 데이터
•
조회 컬럼: OrdNo, OrdDtm, ShopId, OrdDetNo, ItemId, OrdQty, SalePrc
OrdNo OrdDtm ShopId OrdDetNo ItemId OrdQty SalePrc
----- ------------------- ------ -------- ------ ------ --------
67284 2023-01-01 20:30:00 S200 1 AMB 1 4500.000
67298 2023-01-02 09:30:00 S217 1 AMB 1 4500.000
67312 2023-01-02 10:00:00 S003 1 AMB 1 4500.000
67326 2023-01-02 10:00:00 S001 1 AMB 1 4500.000
67340 2023-01-02 10:00:00 S226 1 AMB 1 4500.000
67353 2023-01-02 10:00:00 S025 2 AMB 1 4500.000
… 생략 …
SQL
복사
BOOSTER QUIZ 7-3-3
주문번호 91810에 포함된 주문 상세 내역을 보여주세요.
•
대상 테이블: 주문상세(OrdDet)와 상품(Item)
•
조회 조건: OrdNo가 91810인 데이터
•
조회 컬럼: OrdNo, OrdDetNo, OrdQty, SalePrc, ItemId, ItemNm
OrdNo OrdDetNo OrdQty SalePrc ItemId ItemNm
----- -------- ------ -------- ------ -----------------
91810 1 1 5000.000 ICLR 아이스카페라떼(R)
91810 2 1 4500.000 AMR 아메리카노(R)
91810 3 1 4500.000 AMR 아메리카노(R)
SQL
복사
Upper: 7. JOIN
