8-2-1. WHERE 절 서브쿼리
WHERE 절에도 서브쿼리를 사용할 수 있다. SELECT 절에서는 단일 값이 출력되는 스칼라 형태로 서브쿼리를 사용해야 했다. WHERE 절에서는 단일 값이 아닌 형태로도 서브쿼리를 사용할 수 있다.
아래는 IN 조건과 WHERE 절 서브쿼리를 사용해, 2022년 1월 1일에 주문이 있는 매장 정보를 출력하는 SQL이다.
-- [SQL-8-2-1-a]
SELECT T1.ShopId ,T1.ShopNm
FROM startdb.Shop T1
WHERE T1.ShopId IN ( SELECT X.ShopId
FROM startdb.Ord X
WHERE X.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20220102','%Y%m%d'));
ShopId ShopNm
------ ---------------
S003 Chicago-1st
S007 San Antonio-1st
S011 Austin-1st
SQL
복사
위 SQL에서 서브쿼리만 떼어내서 실행해보자. Ord에서 2022년 1월 1일에 주문이 존재하는 세 건의 ShopId가 출력된다. 여러 건의 데이터가 출력되므로 스칼라 서브쿼리가 아닌 것을 알 수 있다.
-- [SQL-8-2-1-b]
SELECT X.ShopId
FROM startdb.Ord X
WHERE X.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20220102','%Y%m%d');
ShopId
------
S003
S007
S011
SQL
복사
원래 SQL의 메인쿼리에서 ShopId 조건을 IN으로 받았기 때문에, 위와 같이 여러 건의 값을 받아 낼 수가 있는 것이다. 아래와 같이 메인쿼리의 ShopId에 대한 조건을 같다(=)로 변경하면 SQL은 에러가 발생한다.
-- [SQL-8-2-1-c]
SELECT T1.ShopId ,T1.ShopNm
FROM startdb.Shop T1
WHERE T1.ShopId = ( SELECT X.ShopId
FROM startdb.Ord X
WHERE X.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20220102','%Y%m%d'));
Error
--------------------------------
Subquery returns more than 1 row
SQL
복사
WHERE 절에서 같다(=) 조건으로 서브쿼리를 사용하려면, SELECT 절 서브쿼리처럼 스칼라 형태로 단일 값을 돌려주어야 한다. 아래는 같다(=) 조건으로 WHERE 절 서브쿼리를 사용하는 SQL이다. ORDER BY 와 LIMIT 1을 사용해 ShopSize가 가장큰 ShopId를 Ord의 조건으로 공급하고 있다.
-- [SQL-8-2-1-d]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.ShopId
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220201','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220301','%Y%m%d')
AND T1.ShopId =
(SELECT X.ShopId FROM startdb.Shop X ORDER BY X.ShopSize DESC LIMIT 1);
OrdNo OrdDtm ShopId
----- ------------------- ------
26268 2022-02-04 14:00:00 S099
26481 2022-02-06 14:00:00 S099
26545 2022-02-08 14:00:00 S099
26767 2022-02-13 14:00:00 S099
26849 2022-02-21 14:00:00 S099
SQL
복사
8.2.2 EXISTS
EXISTS는 WHERE 절의 서브쿼리와 자주 짝을 이루는 특수 조건자다. EXISTS는 서브쿼리내의 데이터 존재여부를 확인하는 특수 조건자다. 메인 쿼리의 레코드별로 EXISTS의 서브쿼리를 수행해 데이터가 한 건이라도 존재하면 레코드를 조회 결과에 포함시킬 수 있다.
아래 SQL을 살펴보자. 매장운영유형(ShopOperTp)이 대리점(DIST)이면서 의자수량(ChairQty)이 70 이상인 매장 리스트를 조회하고 있다.
-- [SQL-8-2-2-a] Shop 데이터
SELECT T1.ShopId ,T1.ShopNm
FROM startdb.Shop T1
WHERE T1.ShopOperTp ='DIST'
AND T1.ChairQty >= 70
ORDER BY T1.ShopId;
ShopId ShopNm
------ -------------
S088 San Diego-5th
S099 Denver-5th
S198 Seattle-10th
S199 Denver-10th
SQL
복사
위 SQL의 결과를 보면 네 개의 매장 데이터가 출력되었다. 이제 위 매장들 중에 2022년 1월 1일에 주문이 한 건이라도 존재하는 매장만 출력하도록 해보자. 아래와 같이 EXISTS 를 추가해 처리할 수 있다.
-- [SQL-8-2-2-b] 대리점(DIST)이면서, 의자가 70개 이상인 매장 중에
-- 22년 1월달 주문이 한 건이라도 존재하는 매장만 조회
SELECT T1.ShopId ,T1.ShopNm
FROM startdb.Shop T1
WHERE T1.ShopOperTp ='DIST'
AND T1.ChairQty >= 70
AND EXISTS(
SELECT *
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')
)
ORDER BY T1.ShopId;
ShopId ShopNm
------ -----------
S199 Denver-10th
SQL
복사
S199 매장만 2022년 1월 1일에 주문이 존재하기 때문에 결과로 출력되고 나머지는 출력되지 못한 것을 알 수 있다.
EXISTS의 부정으로 NOT EXISTS를 사용할 수 있다. NOT EXISTS는 서브쿼리에 해당하는 데이터가 한 건도 없어야만 조건을 만족한다. 서브쿼리에 해당하는 데이터가 한 건이라도 있으면 조건을 만족하지 않는다. 위에서 작성한 SQL의 EXISTS를 NOT EXISTS로 변경해보자. 2022년 1월 1일에 주문이 한 건도 없는 매장이 출력된다.
-- [SQL-8-2-2-c]
SELECT T1.ShopId ,T1.ShopNm
FROM startdb.Shop T1
WHERE T1.ShopOperTp ='DIST'
AND T1.ChairQty >= 70
AND NOT EXISTS(
SELECT *
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')
)
ORDER BY T1.ShopId;
ShopId ShopNm
------ -------------
S088 San Diego-5th
S099 Denver-5th
S198 Seattle-10th
SQL
복사
앞에서 살펴봤던 IN 조건을 사용한 WHERE 절 서브쿼리는 EXISTS 서브쿼리로 변경할 수 있다. 메인쿼리의 T1.ShopId를 서브쿼리 안쪽의 조건으로 집어 넣으면서 IN 조건을 EXISTS로 변경해버리면 된다.
-- [SQL-8-2-2-d] IN 서브쿼리
SELECT T1.ShopId ,T1.ShopNm
FROM startdb.Shop T1
WHERE T1.ShopId IN ( SELECT X.ShopId
FROM startdb.Ord X
WHERE X.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20220102','%Y%m%d'));
-- [SQL-8-2-2-e] 같은 결과를 만드는 EXISTS 서브쿼리
-- IN 조건 컬럼을 서브쿼리 안으로 밀어 넣어서 연결 조건으로 사용
SELECT T1.ShopId ,T1.ShopNm
FROM startdb.Shop T1
WHERE EXISTS -- > IN을 EXISTS로 변경(조건 컬럼은 서브쿼리 안에서 처리)
( SELECT X.ShopId
FROM startdb.Ord X
WHERE X.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20220102','%Y%m%d')
AND X.ShopId = T1.ShopId -- > IN 조건 컬럼 이동
);
SQL
복사
반드시 그런건 아니지만, WHERE 절 서브쿼리는 EXISTS 로 처리하는 것이 성능에 유리할 가능성이 좀 더 높다. 정확한건 DBMS마다, SQL마다, 데이터 분포마다 다를 수 있으며, 실제 실행계획을 확인해야만 정확이 알 수 있다. 이 부분은 SQL 성능과 관련되므로 여기서 다룰 수는 없다. 다만 WHERE 절 서브쿼리는 EXISTS가 성능에 유리할 가능성이 좀 더 높다 정도만 알고 넘어가기 바란다.
8-2-3. EXISTS와 JOIN
WHERE 절의 EXISTS 서브쿼리는 조인으로 풀어볼 수도 있다. 아래 SQL들을 살펴보기 바란다.
-- [SQL-8-2-3-a]
-- 23년 1월 1일 주문을 조회
-- EXISTS를 사용해 20220705에 가입한 회원의 주문만 필터
SELECT T1.OrdNo ,T1.OrdDtm, T1.MemberId ,T1.OrdAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230102','%Y%m%d')
AND EXISTS(
SELECT *
FROM startdb.Member A
WHERE A.MemberID = T1.MemberId
AND A.JoinDtm = STR_TO_DATE('20220705','%Y%m%d'))
ORDER BY T1.OrdNo;
OrdNo OrdDtm MemberId OrdAmt
----- ------------------- -------- --------
67282 2023-01-01 19:30:00 M9998 5000.000
67284 2023-01-01 20:30:00 M9998 4500.000
-- [SQL-8-2-3-b]
-- 비슷한 결과를 만들기 위해 JOIN으로 처리
-- 조인을 사용하면 필터 조건으로 사용하던 회원 정보를 같이 출력해 줄 수 있다.
SELECT T1.OrdNo ,T1.OrdDtm, T1.MemberId ,T1.OrdAmt ,T2.NickNm ,T2.MemberGd ,T2.JoinDtm
FROM startdb.Ord T1
INNER JOIN startdb.Member T2
ON (T2.MemberId = T1.MemberId)
WHERE T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230102','%Y%m%d')
AND T2.JoinDtm = STR_TO_DATE('20220705','%Y%m%d')
ORDER BY T1.OrdNo;
OrdNo OrdDtm MemberId OrdAmt NickNm MemberGd JoinDtm
----- ------------------- -------- -------- ---------- -------- -------------------
67282 2023-01-01 19:30:00 M9998 5000.000 Thunder199 PLAT 2022-07-05 00:00:00
67284 2023-01-01 20:30:00 M9998 4500.000 Thunder199 PLAT 2022-07-05 00:00:00
SQL
복사
EXISTS를 조인으로 변경해 같은 데이터를 출력했다. 조인을 사용하면 필터 조건인 Member의 정보도 같이 출력해 줄 수 있는 장점이 있다. 다만, EXISTS를 조인으로 바꿀때는 주의가 필요하다. 메인 쿼리에 있던 테이블과 EXISTS 에서 처리한 테이블 간의 관계 차수에 따라, 조인으로 변경하는 것이 적절하지 않을 수 있다. 아래 예를 살펴보자.
-- [SQL-8-2-3-c]
-- 직영(DIST) 매장이면서 의자수가 70개 이상인 데이터 조회
-- EXISTS를 사용해 2022년 1월에 주문이 있는 매장만 필터 처리
SELECT T1.ShopId ,T1.ShopNm, T1.ChairQty
FROM startdb.Shop T1
WHERE T1.ShopOperTp ='DIST'
AND T1.ChairQty >= 70
AND EXISTS(
SELECT *
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')
)
ORDER BY T1.ShopId;
ShopId ShopNm ChairQty
------ ----------- --------
S199 Denver-10th 74
-- [SQL-8-2-3-d]
-- 같은 결과를 만들기 위해 JOIN으로 처리, 하지만 메인 쿼리의 결과 건수가 늘어난다.
-- 조인은 결과 건수를 변화시킬 수 있다는 점에 주의가 필요
SELECT T1.ShopId ,T1.ShopNm, T1.ChairQty
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T1.ShopOperTp ='DIST'
AND T1.ChairQty >= 70
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
ORDER BY T1.ShopId;
ShopId ShopNm ChairQty
------ ----------- --------
S199 Denver-10th 74
S199 Denver-10th 74
S199 Denver-10th 74
S199 Denver-10th 74
… 생략 …
SQL
복사
8-2-4. 서브쿼리 다양하게 활용하기
서브쿼리에는 집계함수와 HAVING 조건도 처리할 수 있다. 서브쿼리는 말 그대로 부가적인 또 하나의 쿼리이기 때문에, 일반적으로 SQL을 작성하면서 사용할 수 있는 모든 문법을 사용할 수 있다. 아래는 HAVING 조건과 집계함수를 사용해 주문 수량이 30 개 이상인 회원 리스트를 출력하고 있다.
-- [SQL-8-2-4-a] 주문이 한 건이라도 있는 매장
SELECT T1.ShopId ,T1.ShopNm, T1.ShopSize
FROM startdb.Shop T1
WHERE T1.ShopOperTp ='FLAG'
AND T1.ShopSize >= 60
AND EXISTS(
SELECT *
FROM startdb.Ord X
WHERE X.ShopId = T1.ShopId
AND X.OrdDtm >= STR_TO_DATE('20201201','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20210101','%Y%m%d')
)
ORDER BY T1.ShopId;
ShopId ShopNm ShopSize
------ ---------------- --------
S018 Seattle-1st 61
S020 Washington-1st 65
S021 New York-2nd 67
S022 Los Angeles-2nd 69
S026 Philadelphia-2nd 77
S027 San Antonio-2nd 79
S030 San Jose-2nd 85
S031 Austin-2nd 87
S035 Charlotte-2nd 95
-- [SQL-8-2-4-b] 서브쿼리 안에 집계함수 사용
-- 주문 건수가 30건 이상인 매장
-- EXISTS 서브쿼리에서 집계함수와 HAVING 사용
SELECT T1.ShopId ,T1.ShopNm, T1.ShopSize
FROM startdb.Shop T1
WHERE T1.ShopOperTp ='FLAG'
AND T1.ShopSize >= 60
AND EXISTS(
SELECT 1 -- > HAVING 처리를 위해 SELECT 절에 1을 사
FROM startdb.Ord X
WHERE X.ShopId = T1.ShopId
AND X.OrdDtm >= STR_TO_DATE('20201201','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20210101','%Y%m%d')
HAVING COUNT(*)>=30 -- > HAVING COUNT 조건 사용
)
ORDER BY T1.ShopId;
ShopId ShopNm ShopSize
------ ------------ --------
S018 Seattle-1st 61
S021 New York-2nd 67
SQL
복사
아래와 같이 서브쿼리 안에서 조인도 얼마든지 가능하다. 이를 통해 복잡합 로직의 데이터를 좀 더 편하게 추출할 수 있다.
-- [SQL-8-2-4-c]
-- 2020년 12월에 Bakery 상품 주문이 한 건이라도 존재하는 매장 조회
SELECT T1.ShopOperTp
,( SELECT MAX(X.BaseCdNm)
FROM startdb.BaseCd X
WHERE X.BaseCdDv = 'ShopOperTp'
AND X.BaseCd = T1.ShopOperTp) ShopOperTp_NM
,COUNT(*) ShopCnt
FROM startdb.Shop T1
WHERE EXISTS(
SELECT *
FROM startdb.Ord X
INNER JOIN startdb.OrdDet Y ON (X.OrdNo = Y.OrdNo)
INNER JOIN startdb.Item Z ON (Z.ItemId = Y.ItemId)
WHERE X.ShopId = T1.ShopId
AND X.OrdDtm >= STR_TO_DATE('20201201','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20210101','%Y%m%d')
AND Z.ItemCat = 'BKR'
)
GROUP BY T1.ShopOperTp
ORDER BY T1.ShopOperTp;
SQL
복사
서브쿼리는 SQL 작성을 편하게 해주지만, 과도하게 사용하면 성능에 좋지 않을 수 있다. 가능하면 사용을 자제하는 것이 좋다.
8-2-5. WHERE 절 서브쿼리의 순환 조건
WHERE 절 서브쿼리의 조건이 다시 메인쿼리로 공급되도록 순환되는 경우가 있는데 매우 안 좋은 패턴이다. 성능에 문제 있을 가능성이 매우 높은 패턴 중에 하나다. 아래 SQL을 살펴보자.
-- [SQL-8-2-5-a]
SELECT T1.ShopId ,T1.OrdNo
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20210201','%Y%m%d')
AND T1.OrdNo = (
SELECT MAX(X.OrdNo)
FROM startdb.Ord X
WHERE X.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20210201','%Y%m%d')
AND X.ShopId = T1.ShopId)
ORDER BY T1.ShopId ASC;
SQL
복사
WHERE 절 서브쿼리는 메인쿼리의 T1.ShopId를 받아서 처리하고 있다. 그리고 서브쿼리에서 처리된 결과(MAX(X.OrdNo))를 다시 메인쿼리의 T1.OrdNo에 조건으로 공급하고 있다. 두 테이블이 서로 값을 주고 받으면서 출력될 데이터를 걸러내게 되므로 많은 부하가 발생하게 된다. (WHERE 절의 EXISTS 서브쿼리는 이와 같은 순환 패턴이 아니다.)
위 SQL이 어떤 결과를 추출하려는 것인지 곰곰히 생각해 보자. 조회 결과와 함께 SQL을 잘 살펴보기 바란다. 위 SQL은 주문 테이블에서 ShopId별로 2021년 1월의 마지막 OrdNo만 출력하는 SQL이다. 그러므로 아래와 같이 GROUP BY SQL로 간단하게 작성이 가능하다.
-- [SQL-8-2-5-b]
SELECT T1.ShopId
,MAX(T1.OrdNo) LastOrdNo
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20210201','%Y%m%d')
GROUP BY T1.ShopId;
SQL
복사
항상 WHERE 절의 서브쿼리와 조건을 주고 받는 형태가 되지 않도록 SQL을 작성하기 바란다. 하지만 테이블 구조상 불가피하게 이러한 패턴이 나오기도 한다. 이 경우에는 주변의 선배들과 이야기에 성능에 문제가 있지 않을지 고민해보기 바란다. 가능하면 이와 같은 패턴이 나오지 않도록 테이블 구조가 만들어져 있는 것이 가장 좋다.
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 8-2-1
2022년 12월 24일에 한 번이라도 주문이 있었던 회원 수를 알려주세요.
•
대상 테이블: 회원(Member), 주문(Ord)
•
조회 조건: 2022년 12월 24일(OrdDtm)에 주문이 한 건이라도 있는 회원
•
조회 컬럼: MemberCnt
•
추가 조건
◦
MemberCnt는 2022년 12월 24일에 주문이 한 번이라도 있었던 회원 수입니다.
MemberCnt
---------
2208
SQL
복사
BOOSTER QUIZ 8-2-2
2022년 12월 24일에 초코머핀 주문이 한 번이라도 있었던 회원을 회원등급별로 카운트(회원수)해주세요.
•
대상 테이블: 회원(Member), 주문(Ord), 주문상세(OrdDet)
•
조회 조건
◦
2022년 12월 24일(OrdDtm)에 ItemId가 CMFR(초코머핀)인 주문이 한건이라도 있는 회원
•
조회 컬럼: MemberGd, MemberCnt
•
추가 조건
◦
MemberGd별 GROUP BY 처리하시오.
◦
MemberCnt는 MemberGd별 카운트입니다.(회원수)
MemberGd MemberCnt
-------- ---------
GOLD 87
PLAT 38
SILV 94
SQL
복사
