매장이름으로 주문 데이터 검색하기
매장이름(ShopNm)을 사용해 주문(Ord) 데이터를 검색하려면,
•
주문(Ord) 테이블에는 매장이름(ShopNm) 컬럼이 없다.
◦
주문 테이블에는 매장 정보 관련해서는 매장ID(ShopId) 컬럼만 있다.
•
그러므로, 매장(Shop) 테이블에서 매장이름(ShopNm)으로 매장ID(ShopId)를 검색해서,
◦
검색한 매장ID를 사용해 주문 데이터를 조회할 수 있다.
-- [SQL-7-2-1] Shop에서 ShopNm으로 Shop의 정보를 조회
SELECT T1.ShopId ,T1.ShopNm ,T1.ShopSize
FROM startdb.Shop T1
WHERE T1.ShopNm = 'Seattle-13th';
ShopId ShopNm ShopSize
------ ------------ --------
S258 Seattle-13th 101
-- [SQL-7-2-2] 검색한 ShopId를 사용해 Ord를 조회
SELECT T2.OrdNo ,T2.OrdDtm ,T2.ShopId ,T2.OrdAmt
FROM startdb.Ord T2
WHERE T2.ShopId = 'S258'
AND T2.OrdDtm >= STR_TO_DATE('20230302','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230303','%Y%m%d');
OrdNo OrdDtm ShopId OrdAmt
----- ------------------- ------ --------
73448 2023-03-02 10:30:00 S258 5000.000
SQL
복사
이와 같은 방법은 두 테이블을 각각 조회하는 방법이므로 두 테이블의 정보를 결합해서 보여줄 수 없다. 관계형 데이터는 각각의 정보를 결합해서 보여줄 때 더욱 강력한 힘을 발휘한다.
JOIN이란?
조인(JOIN)을 사용하면 두 데이터 집합(테이블)을 쉽게 연결해서 보여줄 수 있다.
•
JOIN
◦
데이터와 데이터를 연결하는 방법
◦
두 테이블 간에 조인 조건을 주고, 조건에 따라 데이터를 결합한다.
◦
데이터를 연결해 통합된 정보가 나올 때 데이터 가치가 상승한다.
◦
조인은 PK/FK 관계에서 주로 발생한다.
▪
서로 참조되고, 참조하는 PK와 FK간에 조인이 주로 발생한다.
▪
하지만, PK/FK 관계가 아니어도 얼마든지 조인할 수 있다.
•
조인의 종류
◦
INNER JOIN(이너 조인)
◦
OUTER JOIN(아우터 조인)
◦
CROSS JOIN(크로스 조인 또는 카테시안 조인)
◦
그냥 조인이라고 말하면 흔히 INNER JOIN을 뜻한다.
•
테이블을 연결하기 위한 기술로 UNION / UNION ALL도 있다.
◦
조인은 두 테이블을 좌우로 연결한다.
◦
반면에 UNION / UNION ALL은 두 테이블을 상하로 연결한다.
조인의 기초 문법은 다음과 같다.(ANSI 기준)
SELECT [컬럼들,]
FROM [테이블1] [별칭1]
INNER JOIN [테이블2] [별칭]
ON ([별칭1].[컬럼] = [별칭2].[컬럼]) -- 조인 조건
WHERE [각 테이블의 필터 조건]
SQL
복사
실제 매장과 주문을 조인해보면 다음과 같다. ShopNm을 조건으로 사용해 매장과 주문 데이터를 한 번에 조회하고 있다. 조인 조건으로 두 테이블 간의 PK/FK 관계의 컬럼을 사용하고 있다.
-- [SQL-7-2-3] 조인을 활용해 매장이름으로 주문데이터 검색
SELECT T1.ShopId ,T1.ShopNm ,T1.ShopSize
,T2.OrdNo ,T2.OrdDtm ,T2.ShopId ,T2.OrdAmt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T1.ShopNM = 'Seattle-13th'
AND T2.OrdDtm >= STR_TO_DATE('20230302','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230303','%Y%m%d');
SQL
복사
INNER JOIN 이해하기
아래 그림을 통해 INNER JOIN을 이해해보자. 그림과 같이 Shop에는 세 건의 데이터만, Ord에는 네 건의 데이터만 있다고 가정한다.
다음 두 가지를 꼭 기억하자.
•
INNER JOIN에 실패한 데이터는 결과에 나오지 못한다.
•
한 건의 데이터가 여러 건과 조인하면, 한 건의 데이터가 여러 건으로 늘어난다.
위 그림에서는 Shop을 먼저 접근해 Ord와 조인을 처리했다. 이너 조인은 조인 처리를 위한 테이블의 접근 순서와 상관 없이 동일한 결과를 보장한다. 아래와 같이 Ord를 먼저 접근, Shop을 나중에 접근해 조인을 수행해보자. 조금 전과 같은 결과가 나온다.
아래 SQL들을 보고, 이너 조인이 처리된 결과를 예측해 적어보자.
무언가 추가하고 싶다는 생각으로
처음부터 조인 SQL을 만들려고 하면 잘 되지 않을 수 있다. 조회하려는 데이터의 메인 데이터를 먼저 조회한 후에 조인을 하나씩 추가한다는 개념으로 조인을 작성해보기 바란다. 완성된 SQL을 한번에 만들어서 실행하지 말고, 중간 중간 실행해보면서 SQL을 완성해 나가기 바란다.
-- [SQL-7-2-4] 매장 정보를 우선 조회
SELECT T1.ShopId ,T1.ShopNm, T1.ShopSize
FROM startdb.Shop T1
WHERE T1.ShopId = 'S200';
ShopId ShopNm ShopSize
------ --------------- --------
S200 Washington-10th 105
-- [SQL-7-2-5] 매장 정보에 주문 정보를 조인으로 추가
SELECT T1.ShopId ,T1.ShopNm ,T1.ShopSize
,T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T1.ShopId = 'S200';
ShopId ShopNm ShopSize OrdNo OrdDtm OrdAmt
------ --------------- -------- ----- ------------------- ---------
S200 Washington-10th 105 13224 2021-05-11 11:30:00 4000.000
S200 Washington-10th 105 13227 2021-05-11 11:30:00 4500.000
S200 Washington-10th 105 13233 2021-05-11 11:30:00 7500.000
... 생략 ...
SQL
복사
-- [SQL-7-2-6] 주문 정보를 우선 조회
SELECT T1.OrdNo ,T1.MemberId ,T1.OrdAmt
FROM startdb.Ord T1
WHERE T1.ShopId = 'S003'
AND T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230102','%Y%m%d');
OrdNo MemberId OrdAmt
----- -------- --------
67277 M0001 4500.000
-- [SQL-7-2-7] 주문 정보에 회원 정보를 추가
SELECT T1.OrdNo ,T1.MemberId ,T1.OrdAmt
,T2.NickNm ,T2.JoinDtm
FROM startdb.Ord T1
INNER JOIN startdb.Member T2
ON (T2.MemberId = T1.MemberId)
WHERE T1.ShopId = 'S003'
AND T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230102','%Y%m%d');
OrdNo MemberId OrdAmt NickNm JoinDtm
----- -------- -------- ------ -------------------
67277 M0001 4500.000 Air 2019-03-28 00:00:00
SQL
복사
ANSI VS. ORACLE
SQL은 ANSI(American National Standards Institute) 라는 표준화된 문법이 있다. 하지만 각 DBMS별로 그에 맞는 문법을 만들어 사용하기도 한다. 대표적으로 오라클(ORACLE)의 조인 문법이 ANSI와 차이가 있다. 오라클을 사용할 때는 오라클만의 조인 문법을 많이 사용하는 편이다. 우리는 MySQL뿐만 아니라 다양한 DBMS를 다룰 가능성이 있으므로 조인 관련해 ANSI 표준 문법과 오라클 문법을 모두 알 필요가 있다.
ANSI 조인 SQL을 오라클 문법 조인으로 변경하려면,
•
INNER JOIN은 제거하고 FROM 절의 테이블과 테이블을 콤마(,)로 구분한다.
•
ON 절도 제거하고, ON 절에 있던 조인 조건을 WHERE 절로 옮긴다.
-- [SQL-7-2-8] ANSI INNER JOIN
SELECT T1.ShopId ,T1.ShopNm ,T1.ShopSize
,T2.OrdNo ,T2.OrdDtm ,T2.ShopId ,T2.OrdAmt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T2.ShopId = 'S270'
AND T2.OrdDtm >= STR_TO_DATE('20230302','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230303','%Y%m%d');
-- [SQL-7-2-9] ORACLE INNER JOIN
-- MySQL도 아래와 같이 JOIN을 처리할 수 있다.
-- 반대로 ORACLE도 ANSI JOIN을 사용할 수 있다.
-- 하지만 나중에 배울 OUTER JOIN은 DBMS마다 다를 수 있다.
SELECT T1.ShopId ,T1.ShopNm ,T1.ShopSize
,T2.OrdNo ,T2.OrdDtm ,T2.ShopId ,T2.OrdAmt
FROM startdb.Shop T1
,startdb.Ord T2
WHERE T2.ShopId = 'S270'
AND T2.OrdDtm >= TO_DATE('20230302','%Y%m%d')
AND T2.OrdDtm < TO_DATE('20230303','%Y%m%d')
AND T2.ShopId = T1.ShopId;
SQL
복사
JOIN 조건과 FILTER 조건
•
조인 조건: 테이블을 연결하는 조건
•
보통은 ON 절에 사용. (WHERE 절에 사용해도 SQL이 정상 실행된다.)
•
조건식을 기준으로 왼쪽과 오른쪽에 서로 다른 테이블의 컬럼이 사용되었다면 조인 조건
◦
Ex) T1.ShopId = T2.ShopId
•
필터 조건: 테이블에서 데이터를 골라는 조건
◦
WHERE 절과 ON 절에 모두 사용할 수 있다.
◦
나중에 배울 아우터 조인(OUTER JOIN)에서는 필터 조건을 WHERE 절에 사용했는지, ON 절에 사용했는지에 따라 결과가 달라진다. 이너 조인은 상관없다.
◦
조건식을 기준으로 한 쪽에는 테이블의 컬럼이, 반대쪽에는 조건 값이 오면 필터 조건이다.
▪
Ex) T1.ShopNm = 'New York-1st’
아래 SQL은 모두 같은 SQL이다.
-- [SQL-7-2-10] ON 절에 조인 조건만 사용
SELECT T1.ShopId ,T1.ShopSize ,T2.OrdNo ,T2.OrdDtm
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T1.ShopSize >= 100
AND T2.OrdDtm >= STR_TO_DATE('20230331','%Y%m%d')
ORDER BY T1.ShopId, T2.OrdNo;
-- [SQL-7-2-11] ON 절에 모든 조건을 처리
SELECT T1.ShopId ,T1.ShopSize ,T2.OrdNo ,T2.OrdDtm
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId
AND T1.ShopSize >= 100
AND T2.OrdDtm >= STR_TO_DATE('20230331','%Y%m%d'))
ORDER BY T1.ShopId, T2.OrdNo;
-- [SQL-7-2-12] ORACLE 방식으로 조인 처리
SELECT T1.ShopId ,T1.ShopSize ,T2.OrdNo ,T2.OrdDtm
FROM startdb.Shop T1
,startdb.Ord T2
WHERE T1.ShopSize >= 100
AND T2.OrdDtm >= STR_TO_DATE('20230331','%Y%m%d')
AND T2.ShopId = T1.ShopId
ORDER BY T1.ShopId, T2.OrdNo;
-- [SQL-7-2-13] ON 절을 생략(MySQL은 가능하지만, ORACLE은 에러)
-- 조인 조건을 WHERE 절에 사용
SELECT T1.ShopId ,T1.ShopSize ,T2.OrdNo ,T2.OrdDtm
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
WHERE T1.ShopSize >= 100
AND T2.OrdDtm >= STR_TO_DATE('20230331','%Y%m%d')
AND T2.ShopId = T1.ShopId
ORDER BY T1.ShopId, T2.OrdNo;
SQL
복사
JOIN과 데이터 집합
기본적으로 조인을 통해 두 데이터 집합이 결합한다. 이 과정 속에서 어떤 데이터 집합의 데이터가 늘어나기도 하며 줄어들기도 한다. 정적인 사각형 모양으로 테이블에 저장되었던 데이터가 조인을 통해 좀 더 다양한 사각형 모양의 데이터 집합을 만들어내고 있다. 항상 조인 SQL을 작성 할 때면 이와 같이 데이터가 변형되는 모습을 머리 속에 떠올려 보기 바란다.
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 7-2-1
(SQL-1)과 (SQL-2)의 각각의 결과를 확인하고, (SQL-3)을 실행했다고 가정하고 질문에 답하시오.
-- (SQL-1)
SELECT T1.MemberId ,T1.NickNm
FROM startdb.Member T1
WHERE T1.MemberID IN ('M0001','M0002');
MemberId NickNm
-------- ------
M0001 Air
M0002 Apple
-- (SQL-2)
SELECT T2.OrdNo ,T2.OrdDtm ,T2.MemberId
FROM startdb.Ord T2
WHERE T2.MemberID IN ('M0001','M0002')
AND T2.OrdDtm >= STR_TO_DATE('20191201','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20191203','%Y%m%d');
OrdNo OrdDtm MemberId
----- ------------------- --------
391 2019-12-01 08:00:00 M0001
392 2019-12-02 08:00:00 M0001
397 2019-12-02 09:00:00 M0002
-- (SQL-3)
SELECT T1.MemberId ,T1.NickNm ,T2.OrdNo ,T2.OrdDtm ,T2.MemberId
FROM startdb.Member T1
INNER JOIN startdb.Ord T2
ON (T1.MemberId = T2.MemberId)
WHERE T1.MemberID IN ('M0001','M0002')
AND T2.MemberID IN ('M0001','M0002')
AND T2.OrdDtm >= STR_TO_DATE('20191201','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20191203','%Y%m%d')
ORDER BY T1.MemberId ,T2.OrdNo;
SQL
복사
•
(SQL-3)은 (SQL-1)과 (SQL-2)를 MemberId로 이너조인한 SQL이다.
•
(SQL-3)을 실행하면 NickNm이 Air인 데이터는 몇 건인가?
•
(SQL-3)을 실행하면 NickNm이 Apple인 데이터는 몇 건인가?
BOOSTER QUIZ 7-2-2
(SQL-1)과 (SQL-2)의 각각의 결과를 확인하고, (SQL-3)을 실행했다고 가정하고 질문에 답하시오.
-- (SQL-1)
SELECT T1.MemberId ,T1.NickNm ,T1.MemberGd ,T1.JoinDtm
FROM startdb.Member T1
WHERE T1.JoinDtm = STR_TO_DATE('20220421','%Y%m%d')
AND T1.MemberGd = 'SILV'
ORDER BY T1.MemberId;
MemberId NickNm MemberGd JoinDtm
-------- ------- -------- -------------------
M3084 River61 SILV 2022-04-21 00:00:00
M3170 Lake63 SILV 2022-04-21 00:00:00
M3362 Fire67 SILV 2022-04-21 00:00:00
M3367 Gold67 SILV 2022-04-21 00:00:00
-- (SQL-2)
SELECT T2.OrdNo ,T2.OrdDtm ,T2.MemberId, T2.ShopId ,T2.OrdAmt
FROM startdb.Ord T2
WHERE T2.OrdDtm >= STR_TO_DATE('20221201','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230101','%Y%m%d')
AND T2.ShopId = 'S167'
AND T2.OrdAmt >= 10000
ORDER BY T2.OrdNo;
OrdNo OrdDtm MemberId ShopId OrdAmt
----- ------------------- -------- ------ ---------
57250 2022-12-23 09:00:00 M3170 S167 12500.000
66490 2022-12-30 15:00:00 M9408 S167 12500.000
-- (SQL-3): (SQL-1)과 (SQL-2)를 이너 조인한 SQL이다.
SELECT T1.MemberId ,T1.NickNm ,T1.MemberGd ,T1.JoinDtm
,T2.OrdNo ,T2.OrdDtm ,T2.MemberId, T2.ShopId ,T2.OrdAmt
FROM startdb.Member T1
INNER JOIN startdb.Ord T2
ON (T2.MemberId = T1.MemberId)
WHERE T1.JoinDtm = STR_TO_DATE('20220421','%Y%m%d')
AND T1.MemberGd = 'SILV'
AND T2.OrdDtm >= STR_TO_DATE('20221201','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230101','%Y%m%d')
AND T2.ShopId = 'S167'
AND T2.OrdAmt >= 10000
ORDER BY T1.MemberId;
SQL
복사
•
(SQL-3)은 (SQL-1)과 (SQL-2)를 MemberId로 이너조인한 SQL이다.
•
(SQL-3)을 실행하면 버려지는 Member 테이블의 MemberId는?
•
(SQL-3)을 실행하면 버려지는 Ord 테이블의 OrdNo는?
Upper: 7. JOIN
