서브쿼리(SUB QUERY)란 하나의 쿼리(Query = SQL) 안에 포함된 또 다른 쿼리를 이야기한다.
8-1-1. 기준코드 테이블
서브쿼리를 설명하기 위해 기준코드 테이블을 먼저 살펴보자. 기준코드 테이블은 시스템 전반적으로 사용하는 코드 데이터를 관리한다. 공통코드, 마스터코드, 기초코드등 시스템별로 다양한 명칭을 사용한다. 여기서는 기준코드란 명칭을 사용한다. 테이블 구조와 데이터를 살펴보자.
-- [SQL-8-1-1-a]
SELECT *
FROM startdb.BaseCd;
BaseCdDv BaseCd BaseCdNm SortOrder
------------- ------ --------------- ---------
EntryResultCd FAIL Fail 2
EntryResultCd SUCC Success 1
HotColdCd COLD Cold 2
HotColdCd HOT Hot 1
ItemSizeCd BIG Big Size 2
ItemSizeCd REG Regular Size 1
MemberGd GOLD Gold 2
MemberGd PLAT Platinum 1
MemberGd SILV Silver 3
MemberSt ACTV Active Member 1
… 생략 …
SQL
복사
지금까지 사용한 테이블들의 코드성 컬럼에 대한 정보가 저장되어 있다. 이 기준코드 테이블을 사용해 코드에 대한 값을 가져올 수 있다. 코드 데이터를 구성할 때 코드 값을 001, 002와 같이 무의미하게 구성할 수도 있으며, 위 결과와 같이 COMP(완료), WAIT(대기)와 같이 유의미하게 구성할 수도 있다. 유의미는 코드 자체만으로 의미를 알 수 있는 장점이 있지만, 비즈니스 로직의 추가나 변경으로 코드의 의미가 바뀔 때 큰 곤란을 겪게 된다. 규모가 크고 확장성을 고려한 시스템일수록 유의미보다 무의미 코드 체계가 조금 더 좋은 선택일 수 있다. 우리가 사용중인 startdb는 연습을 목적으로 하기에 유의미 코드 체계를 사용하고 있다. 코드 체계의 유의미, 무의미보다 중요한것은 코드 데이터를 DB화해서 프로세스를 만들어 관리하고 있느냐이다. 코드를 제대로 관리하지 않으면 나중에 데이터를 제대로 해석할 수 없게 되며, 이는 데이터 활용에 큰 어려움을 가져오게 된다.
8-1-2. SELECT 절 서브쿼리
서브쿼리는 SELECT 절과 WHERE 절, 그리고 FROM 절에도 추가해서 사용할 수 있다. 우선은 SELECT 절에 서브쿼리를 사용하는 법을 살펴보자. Shop 테이블의 ShopOperTp와 ShopSt에 대한 코드명을 서브쿼리로 해결하는 예제다.
-- [SQL-8-1-2-a]
SELECT T1.ShopID ,T1.ShopNm
,T1.ShopOperTp
,( SELECT X.BaseCdNm
FROM startdb.BaseCd X
WHERE X.BaseCdDv = 'ShopOperTp'
AND X.BaseCd = T1.ShopOperTp
) ShopOperTp_NM
,T1.ShopSt
,( SELECT X.BaseCdNm
FROM startdb.BaseCd X
WHERE X.BaseCdDv = 'ShopSt'
AND X.BaseCd = T1.ShopSt
) ShopSt_NM
FROM startdb.Shop T1
WHERE T1.ShopId IN ('S001','S002');
ShopID ShopNm ShopOperTp ShopOperTp_NM ShopSt ShopSt_NM
------ --------------- ---------- ------------- ------ ---------
S001 New York-1st FLAG Flagship OPEN Open
S002 Los Angeles-1st DIST Distributor OPEN Open
SQL
복사
이처럼 코드에 대한 명칭 처리를 위해 SELECT 절 서브쿼리를 자주 사용한다. SELECT 절에 서브쿼리를 사용할 때는 서브쿼리의 결과 값이 반드시 스칼라(Scalar) 형태가 되어야 한다. 스칼라(Scalar)란 하나의 수치만으로 표시되는 양을 뜻한다. 다시 말해 One-Row, One-Column으로 구성된 단일 값이다. 만약에 SELECT 절 서브쿼리에서 여러 건의 결과나 여러 컬럼의 값을 내보내면 SQL은 에러가 발생하게 된다.
아래와 같이 서브쿼리에서 BaseCd에 대한 조건을 제거하면 SELECT 절 서브쿼리에서 여러 건의 데이터가 추출되면서 에러가 발생한다.
-- [SQL-8-1-2-b]
-- SELECT 절 서브쿼리에서 스칼라가 아닌 값을 내보내서 에러
SELECT T1.ShopID ,T1.ShopNm
,T1.ShopOperTp
,( SELECT X.BaseCdNm
FROM startdb.BaseCd X
WHERE X.BaseCdDv = 'ShopOperTp'
-- > 실수로 조건 생략 AND X.BaseCd = T1.ShopOperTp
) ShopOperTp_NM
,T1.ShopSt
,( SELECT X.BaseCdNm
FROM startdb.BaseCd X
WHERE X.BaseCdDv = 'ShopSt'
AND X.BaseCd = T1.ShopSt
) ShopSt_NM
FROM startdb.Shop T1
WHERE T1.ShopId IN ('S001','S002');
-- 에러
Subquery returns more than 1 row
SQL
복사
8-1-3. 상관 서브쿼리와 단독 서브쿼리
서브쿼리는 상관 서브쿼리와 단독 서브쿼리로 구분할 수 있다.
•
서브쿼리가 메인쿼리의 컬럼(값)을 받아서 처리된다면 상관 서브쿼리
•
메인쿼리와 상관 없이 단독 실행이 가능한 형태면 단독 서브쿼리
아래 SQL을 살펴보자. 방금 앞에서 살펴봤던 SQL이다.
-- [SQL-8-1-3-a]
SELECT T1.ShopID ,T1.ShopNm
,T1.ShopOperTp
,( SELECT X.BaseCdNm
FROM startdb.BaseCd X
WHERE X.BaseCdDv = 'ShopOperTp'
AND X.BaseCd = T1.ShopOperTp -- > 메인쿼리에서 조건 받는중
) ShopOperTp_NM
,T1.ShopSt
,( SELECT X.BaseCdNm
FROM startdb.BaseCd X
WHERE X.BaseCdDv = 'ShopSt'
AND X.BaseCd = T1.ShopSt -- > 메인쿼리에서 조건 받는중
) ShopSt_NM
FROM startdb.Shop T1
WHERE T1.ShopId IN ('S001','S002');
SQL
복사
위의 서브쿼리들은 메인쿼리에서 조건을 공급 받고 있다. 메인쿼리에서 조건을 공급 받고 있으므로 상관 서브쿼리다. 위 SQL에서 서브쿼리만 별도로 떼어내서 아래와 같이 실행하면 에러가 난다. 서브쿼리에서 사용한 T1.ShopOperTp라는 컬럼은 메인쿼리에 있던 컬럼이므로 아래처럼 서브쿼리만 단독으로 실행할 수 없다.
-- [SQL-8-1-3-b]
SELECT X.BaseCdNm
FROM startdb.BaseCd X
WHERE X.BaseCdDv = 'ShopOperTp'
AND X.BaseCd = T1.ShopOperTp -- > 메인쿼리에서 조건 받는중
SQL
복사
아래는 Shop에서 ShopOperTp가 FLAG인 매장의 건수를 세면서, 서브쿼리로 전체 매장 카운트도 추가한 SQL이다.
-- [SQL-8-1-3-c]
SELECT COUNT(*) FLAG_SHOP_CNT
,(SELECT COUNT(*) FROM startdb.Shop X) ALL_SHOP_CNT
FROM startdb.Shop T1
WHERE T1.ShopOperTp = 'FLAG';
FLAG_SHOP_CNT ALL_SHOP_CNT
------------- ------------
30 300
SQL
복사
위 SQL에서 ALL_SHOP_CNT를 처리하는 서브쿼리는 메인 쿼리에서 조건을 받고 있지 않다. 그러므로 아래와 같이 서브쿼리만 단독으로 실행할 수 있다. 이처럼 단독으로 실행 가능한 서브쿼리를 단독 서브쿼리라고 한다.
-- [SQL-8-1-3-d]
SELECT COUNT(*) FROM startdb.Shop X;
SQL
복사
8-1-4. SELECT 절 서브쿼리를 조인으로
SELECT 절 서브쿼리는 복잡한 SQL을 조금 더 편하게 작성할 수 있게 해주는 장점이 있다. 실제로 개발자와 이야기를 해보면 조인보다는 스칼라 서브쿼리가 훨씬 이해가 쉽고 편하다고 말하기도 한다. 하지만 편한만큼 성능과 확장성에 있어서 안좋은 경우가 제법 있다. 앞에서 살펴본 기준코드와 같이 데이터 양이 적고, 값의 종류가 적은 데이터가 아니라면, SELECT 절 서브쿼리 방식을 최소화하는 것이 좋다.
아래는 Shop 데이터를 조회하면서 해당 매장의 주문금액을 조회하는 SQL이다. 스칼라 서브쿼리를 사용해 주문금액을 구하고 있다. 서브쿼리를 살펴보면, WHERE 절 조건으로 T1(Shop)의 T1.ShopId를 공급 받고 있다. 그리고 해당 매장(ShopId)의 OrdAmt를 SUM처리해서 결과로 내보내고 있다.
-- [SQL-8-1-4-a]
SELECT T1.ShopID ,T1.ShopNm
,(
SELECT SUM(X.OrdAmt)
FROM startdb.Ord X
WHERE X.ShopId = T1.ShopId
AND X.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
) SumOrdAmt
FROM startdb.Shop T1
WHERE T1.ShopOperTp = 'FLAG'
AND T1.ShopStartYmd = '20180323'
ORDER BY T1.ShopID;
ShopID ShopNm SumOrdAmt
------ ---------------- ---------
S012 Jacksonville-1st 71500.000
S013 Fort Worth-1st 55500.000
S026 Philadelphia-2nd 57500.000
S027 San Antonio-2nd 43000.000
SQL
복사
만약에 위 SQL을 서브쿼리 없이 해결하려면 조인과 GROUP BY를 혼합해야 한다. 조인과 GROUP BY를 혼합하는 것보다는 서브쿼리만 사용하는 것이 일부 개발자들에게는 더 쉬울 수 있다. 하지만, 이처럼 서브쿼리를 사용한 방식은 요구 조건이 추가될 때 문제가 생긴다. 예를 들어 SumOrdAmt 외에 주문 건수도 추가해야 한다고 생각해보자. 가장 쉬운 방법은 아래와 같이 똑같은 서브쿼리를 하나 추가하는 것이다.
-- [SQL-8-1-4-b]
SELECT T1.ShopID ,T1.ShopNm
,(
SELECT SUM(X.OrdAmt)
FROM startdb.Ord X
WHERE X.ShopId = T1.ShopId
AND X.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
) SumOrdAmt
,(
SELECT COUNT(*)
FROM startdb.Ord X
WHERE X.ShopId = T1.ShopId
AND X.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
) OrdCnt -- > 서브쿼리를 추가
FROM startdb.Shop T1
WHERE T1.ShopOperTp = 'FLAG'
AND T1.ShopStartYmd = '20180323'
ORDER BY T1.ShopID;
ShopID ShopNm SumOrdAmt OrdCnt
------ ---------------- --------- ------
S012 Jacksonville-1st 71500.000 14
S013 Fort Worth-1st 55500.000 12
S026 Philadelphia-2nd 57500.000 12
S027 San Antonio-2nd 43000.000 8
SQL
복사
이처럼 기존 서브쿼리를 카피하는 방법은 SQL 변경을 비교적 편하게 해준다. 변경은 편하겠지만 성능적으로 좋지 않다. 같은 데이터에 접근하는 서브쿼리를 두 번 사용하면, DB 내부적으로는 같은 데이터에 두 번 접근이 발생한다. 데이터 접근이 많아질수록 성능은 저하될 가능성이 높다. (물론 SQL 성능에 조예가 깊어지면, 일부러 서브쿼리를 사용해 성능을 개선하기도 한다. 이건 먼 훗날 성능을 공부했을 때 이야기다.) 또한, 위와 같은 패턴의 SQL에는 Ord쪽의 컬럼이 추가 될 때마다 서브쿼리를 추가해 해결할 가능성이 높다. SQL은 점점 길어지고 복잡해지며, 성능은 점점 느려지게 될 것이다. 그러므로 위 SQL은 아래와 같이 아우터 조인으로 변경하는 것이 좋다.
-- [SQL-8-1-4-c]
SELECT T1.ShopID ,MAX(T1.ShopNm) ShopNm
,IFNULL(SUM(T2.OrdAmt),0) SumOrdAmt
,COUNT(T2.OrdNo) OrdCnt
FROM startdb.Shop T1
LEFT OUTER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20220201','%Y%m%d'))
WHERE T1.ShopOperTp = 'FLAG'
AND T1.ShopStartYmd = '20180323'
GROUP BY T1.ShopID
ORDER BY T1.ShopID;
ShopID ShopNm SumOrdAmt OrdCnt
------ ---------------- --------- ------
S012 Jacksonville-1st 71500.000 14
S013 Fort Worth-1st 55500.000 12
S026 Philadelphia-2nd 57500.000 12
S027 San Antonio-2nd 43000.000 8
SQL
복사
아래 SQL도 서브쿼리를 과도하게 사용한 예다. Shop에 대한 정보를 조인이 아닌 서브쿼리를 여러개 사용해 처리하고 있다. 서브쿼리가 네 개이므로 레코드 한 건당 서브쿼리가 네 번 실행된다.
-- [SQL-8-1-4-d]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.ShopId
,(SELECT X.ShopNm FROM startdb.Shop X WHERE X.ShopID = T1.ShopID) ShopNm
,(SELECT X.ShopSize FROM startdb.Shop X WHERE X.ShopID = T1.ShopID) ShopSize
,(SELECT X.TableQty FROM startdb.Shop X WHERE X.ShopID = T1.ShopID) TableQty
,(SELECT X.ChairQty FROM startdb.Shop X WHERE X.ShopID = T1.ShopID) ChairQty
FROM startdb.Ord T1
WHERE T1.OrdNo BETWEEN 1 AND 10
ORDER BY T1.OrdNo;
SQL
복사
서브쿼리에서 접근하는 테이블과 조건이 모두 같으므로, 네 개의 서브쿼리는 아래와 같이 조인으로 변경해야 한다. 이때, SELECT 절 서브쿼리는 아우터 조인으로 변경해야 변경전 결과와 동일함을 보장할 수 있다. (테이블의 데이터 구성에 따라 이너 조인으로 변경해도 문제 없을 수도 있다.)
-- [SQL-8-1-4-e]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.ShopId
,T2.ShopNm
,T2.ShopSize
,T2.TableQty
,T2.ChairQty
FROM startdb.Ord T1
LEFT OUTER JOIN startdb.Shop T2
ON (T2.ShopId = T1.ShopId)
WHERE T1.OrdNo BETWEEN 1 AND 10
ORDER BY T1.OrdNo;
SQL
복사
8-1-5. 중첩된 스칼라 서브쿼리
DBMS에 따라 서브쿼리를 중첩해서 사용할 수 있다. 복잡합 데이터 추출을 위해 서브쿼리를 중첩해서 사용해야 할 때가 있다. 하지만, 과도한 서브쿼리의 중첩은 성능에 악영향을 줄 가능성이 있으며, SQL문을 이해하는데 어려움을 가져올 수도 있다. 가능하다면 서브쿼리가 중첩되는 것은 피해서 SQL을 작성하는 것이 좋다. 더 나아가서는, 테이블 설계 시점부터 중첩된 서브쿼리 방식으로 데이터가 조회될 가능성이 있다면 이를 피할 수 있도록 테이블과 프로세스를 설계하는 것이 가장 좋다.
아래는 Ord를 메인쿼리에서 조회하면서 ShopOperTp_NM을 중첩된 서브쿼리로 해결하고 있다.
-- [SQL-8-1-5-a]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.ShopId
,(SELECT X.ShopNm FROM startdb.Shop X WHERE X.ShopID = T1.ShopID) ShopNm
,(SELECT X.ShopSize FROM startdb.Shop X WHERE X.ShopID = T1.ShopID) ShopSize
,(
SELECT (
SELECT MAX(Y.BaseCdNm)
FROM startdb.BaseCd Y
WHERE Y.BaseCdDv = 'ShopOperTp'
AND Y.BaseCd = X.ShopOperTp
) BaseCdNm
FROM startdb.Shop X
WHERE X.ShopId = T1.ShopId) ShopOperTp_NM
FROM startdb.Ord T1
WHERE T1.OrdNo = 1
ORDER BY T1.OrdNo;
SQL
복사
위에서 사용한 스칼라 서브쿼리는 아래와 같이 변경할 수 있다. 서브쿼리가 중첩되지 않도록 서브쿼리의 Shop 부분을 조인으로 처리했다. 덕분에 Shop에 대한 정보를 처리하던 서브쿼리도 모두 조인으로 해결할 수 있다.
-- [SQL-8-1-5-b]
SELECT T1.OrdNo ,T1.OrdDtm
,T1.ShopId ,T2.ShopNm ,T2.ShopSize
,(
SELECT MAX(X.BaseCdNm)
FROM startdb.BaseCd X
WHERE X.BaseCdDv = 'ShopOperTp'
AND X.BaseCd = T2.ShopOperTp
) ShopOperTp_NM
FROM startdb.Ord T1
LEFT OUTER JOIN startdb.Shop T2 ON (T2.ShopId = T1.ShopId)
WHERE T1.OrdNo = 1
ORDER BY T1.OrdNo;
SQL
복사
8-1. BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 8-1-1
주문번호 10번의 주문과 주문상세 정보를 보여주세요. 주문한 상품의 상품크기에 대한 코드명도 한글로 보여주세요.
•
대상 테이블: 주문(Ord), 주문상세(OrdDet), 상품(Item)
•
조회 조건: OrdNo가 10인 데이터
•
조회 컬럼: OrdNo, OrdDtm, OrdDetNo, ItemId, ItemNm, ItemSizeCd, ItemSizeCd_Nm
•
추가 조건
◦
ItemSizeCd_Nm은 ItemSizeCd에 대한 코드명칭입니다.
▪
BaseCd 테이블을 사용해 스칼라 서브쿼리로 처리합니다.
OrdNo OrdDtm OrdDetNo ItemId ItemNm ItemSizeCd ItemSizeCd_Nm
----- ------------------- -------- ------ ------------------- ---------- -------------
10 2019-05-21 14:00:00 1 IAMR Iced Americano(R) REG Regular Size
10 2019-05-21 14:00:00 2 CMFR Chocolate Muffin(R) REG Regular Size
10 2019-05-21 14:00:00 3 IAMR Iced Americano(R) REG Regular Size
SQL
복사
BOOSTER QUIZ 8-1-2
아래 SQL에서 서브쿼리를 제거하고 조인으로 처리하시오.
SELECT T1.MemberId ,T1.NickNm
,(
SELECT SUM(X.OrdAmt)
FROM startdb.Ord X
WHERE X.MemberId = T1.MemberId
AND X.OrdDtm >= STR_TO_DATE('20211201','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20220101','%Y%m%d')
) OrdAmt
,(
SELECT COUNT(*)
FROM startdb.Ord X
WHERE X.MemberId = T1.MemberId
AND X.OrdDtm >= STR_TO_DATE('20211201','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20220101','%Y%m%d')
) OrdCnt
FROM startdb.Member T1
WHERE T1.JoinDtm >= STR_TO_DATE('20210701','%Y%m%d')
AND T1.JoinDtm < STR_TO_DATE('20210801','%Y%m%d')
AND T1.MemberGd = 'PLAT'
AND T1.MemberSt = 'ACTV'
ORDER BY T1.MemberId ASC;
SQL
복사
BOOSTER QUIZ 8-1-3
아래 SQL에서 Shop과 Member에 대한 서브쿼리를 조인으로 변경하시오.
•
추가 조건: MemberGd_NM에 대한 이중 중첩 스칼라 서브쿼리는 중첩을 제거, 단일 스칼라 서브쿼리로 변경
SELECT T1.OrdNo ,T1.OrdDtm ,T1.ShopId
,(SELECT X.ShopNm FROM startdb.Shop X WHERE X.ShopID = T1.ShopID) ShopNm
,(SELECT X.ShopSize FROM startdb.Shop X WHERE X.ShopID = T1.ShopID) ShopSize
,T1.MemberId
,(SELECT X.NickNm FROM startdb.Member X WHERE X.MemberId = T1.MemberId) NickNm
,(SELECT X.MemberGd FROM startdb.Member X WHERE X.MemberId = T1.MemberId) MemberGd
,(SELECT (SELECT MAX(Y.BaseCdNm)
FROM startdb.BaseCd Y
WHERE Y.BaseCdDv = 'MemberGd'
AND Y.BaseCd = X.MemberGd)
FROM startdb.Member X
WHERE X.MemberId = T1.MemberId) MemberGd_NM
FROM startdb.Ord T1
WHERE T1.OrdNo = 100
ORDER BY T1.OrdNo;
SQL
복사
