SQLD · SQL 개발자
서브쿼리와 집합 연산자
서브쿼리는 위치(SELECT·FROM·WHERE)와 상관성(상관·비상관)이라는 두 축으로 정리하면 보기가 빠르게 정리됩니다. 집합 연산자는 중복 제거 여부와 정렬 여부가 핵심 쟁점입니다.
이 주제에서 다루는 내용
- 스칼라 서브쿼리, 인라인 뷰, 중첩 서브쿼리
- 상관 서브쿼리와 비상관 서브쿼리의 실행 방식 차이
- EXISTS 와 IN 의 차이, NULL 이 섞였을 때의 NOT IN
- UNION 과 UNION ALL — 중복 제거와 정렬 비용
- INTERSECT, MINUS(EXCEPT) 와 집합 연산의 컬럼 제약
자주 틀리는 지점
- NOT IN 서브쿼리에 NULL 이 섞이면 결과가 공집합이 되는 것을 놓치는 경우.
- UNION 이 정렬과 중복 제거를 한다는 점을 빼고 UNION ALL 과 같게 보는 경우.
- 집합 연산에서 양쪽 컬럼 개수와 자료형이 호환되어야 한다는 제약을 놓치는 경우.
연습문제 3문항
아래 문항은 오리지널 문제입니다. 기출문제가 아닙니다.
다음 상황에서 요구사항을 만족하는 SQL로 가장 적절한 것은? [테이블 구조] SHIPMENT (배송 정보) - SHIP_ID: 배송번호(PK) - WAREHOUSE_CODE: 창고코드 - SHIP_DATE: 배송일자 - TOTAL_AMOUNT: 총배송금액 [요구사항] 각 창고별로 최근 3개월(2023-10-01 ~ 2023-12-31) 배송 데이터 중에서: 1) 월별 총 배송금액이 가장 높은 달의 금액 2) 해당 창고의 전체 기간 평균 배송금액 3) 1)과 2)의 비율(최고월금액/평균금액) 을 함께 조회하되, 비율이 1.5 이상인 창고만 출력하시오.
- A. WITH MONTHLY AS ( SELECT WAREHOUSE_CODE, EXTRACT(MONTH FROM SHIP_DATE) AS SHIP_MONTH, SUM(TOTAL_AMOUNT) AS MONTHLY_SUM FROM SHIPMENT WHERE SHIP_DATE BETWEEN '2023-10-01' AND '2023-12-31' GROUP BY WAREHOUSE_CODE, EXTRACT(MONTH FROM SHIP_DATE) ) SELECT w.WAREHOUSE_CODE, MAX(w.MONTHLY_SUM) AS MAX_MONTHLY, AVG(w.MONTHLY_SUM) AS AVG_MONTHLY, MAX(w.MONTHLY_SUM) / AVG(w.MONTHLY_SUM) AS RATIO FROM MONTHLY w GROUP BY w.WAREHOUSE_CODE HAVING MAX(w.MONTHLY_SUM) / AVG(w.MONTHLY_SUM) >= 1.5
- B. SELECT WAREHOUSE_CODE, MAX(SUM(TOTAL_AMOUNT)) OVER (PARTITION BY WAREHOUSE_CODE) AS MAX_MONTHLY, AVG(TOTAL_AMOUNT) AS AVG_AMOUNT, MAX(SUM(TOTAL_AMOUNT)) OVER (PARTITION BY WAREHOUSE_CODE) / AVG(TOTAL_AMOUNT) AS RATIO FROM SHIPMENT WHERE SHIP_DATE BETWEEN '2023-10-01' AND '2023-12-31' GROUP BY WAREHOUSE_CODE, EXTRACT(MONTH FROM SHIP_DATE) HAVING MAX(SUM(TOTAL_AMOUNT)) OVER (PARTITION BY WAREHOUSE_CODE) / AVG(TOTAL_AMOUNT) >= 1.5
- C. SELECT WAREHOUSE_CODE, MAX(MONTHLY_SUM) AS MAX_MONTHLY, AVG(TOTAL_AMOUNT) AS AVG_AMOUNT, MAX(MONTHLY_SUM) / AVG(TOTAL_AMOUNT) AS RATIO FROM ( SELECT WAREHOUSE_CODE, EXTRACT(MONTH FROM SHIP_DATE) AS SHIP_MONTH, SUM(TOTAL_AMOUNT) AS MONTHLY_SUM, TOTAL_AMOUNT FROM SHIPMENT WHERE SHIP_DATE BETWEEN '2023-10-01' AND '2023-12-31' GROUP BY WAREHOUSE_CODE, EXTRACT(MONTH FROM SHIP_DATE), TOTAL_AMOUNT ) GROUP BY WAREHOUSE_CODE HAVING MAX(MONTHLY_SUM) / AVG(TOTAL_AMOUNT) >= 1.5
- D. WITH MONTHLY AS ( SELECT WAREHOUSE_CODE, EXTRACT(MONTH FROM SHIP_DATE) AS SHIP_MONTH, SUM(TOTAL_AMOUNT) AS MONTHLY_SUM FROM SHIPMENT WHERE SHIP_DATE BETWEEN '2023-10-01' AND '2023-12-31' GROUP BY WAREHOUSE_CODE, EXTRACT(MONTH FROM SHIP_DATE) ) SELECT w.WAREHOUSE_CODE, MAX(w.MONTHLY_SUM) AS MAX_MONTHLY, AVG(w.MONTHLY_SUM) OVER () AS AVG_MONTHLY, MAX(w.MONTHLY_SUM) / AVG(w.MONTHLY_SUM) OVER () AS RATIO FROM MONTHLY w GROUP BY w.WAREHOUSE_CODE HAVING MAX(w.MONTHLY_SUM) / AVG(w.MONTHLY_SUM) OVER () >= 1.5
정답: A
해설. Option A correctly performs the required calculations and filters the results to only include warehouses where the ratio of maximum monthly total amount to average total amount is 1.5 or greater.
다음 질의의 실행 순서를 고려할 때 옳은 것은? [테이블: PRODUCT] PRODUCT_ID | PRODUCT_NAME | CATEGORY | PRICE 1 | 모터 | 기계 | 50000 2 | 센서 | 전자 | 30000 3 | 베어링 | 기계 | 20000 4 | 회로보드 | 전자 | 40000 5 | 기어 | 기계 | 15000 [질의] SELECT CATEGORY, COUNT(*) as CNT FROM PRODUCT WHERE EXISTS (SELECT 1 FROM PRODUCT P2 WHERE P2.CATEGORY = PRODUCT.CATEGORY AND P2.PRICE > 25000) GROUP BY CATEGORY ORDER BY CNT DESC;
- A. EXISTS 조건을 만족하는 행들만 GROUP BY 처리되어 결과는 2행이 출력된다.
- B. EXISTS는 각 행마다 독립적으로 평가되므로 기계 카테고리의 모든 행이 결과에 포함된다.
- C. ORDER BY CNT DESC에 의해 전자(2), 기계(1) 순서로 출력된다.
- D. WHERE 절의 EXISTS 조건이 모든 행에 대해 FALSE이므로 결과는 0행이다.
정답: A
해설. EXISTS 서브쿼리는 각 행에 대해 해당 카테고리에 25000원 초과 제품이 있는지 확인합니다. 기계 카테고리(모터 50000원), 전자 카테고리(센서 30000원, 회로보드 40000원) 모두 조건을 만족하므로 모든 행이 WHERE를 통과합니다. 하지만 GROUP BY CATEGORY에 의해 기계(3개), 전자(2개)로 그룹화되어 최종 2행이 출력됩니다. B는 EXISTS 동작을 잘못 이해한 것이고, C는 카운트 값을 반대로 계산한 오답이며, D는 EXISTS 조건 평가를 잘못한 것입니다.
제조사의 생산 실적 테이블 PROD(각 행: 라인번호 LINE, 제품 PRODUCT, 생산량 QTY)가 다음과 같다. PROD: (L1, A, 100) (L1, B, 200) (L2, A, 300) (L2, C, NULL) (L3, A, 500) 다음 SQL을 실행한 결과로 옳은 것은? SELECT LINE FROM PROD WHERE QTY > (SELECT AVG(QTY) FROM PROD WHERE PRODUCT = 'A') GROUP BY LINE HAVING SUM(QTY) > 250;
- A. 결과 행은 L1, L2, L3 세 개이다.
- B. 결과 행은 L2, L3 두 개이다.
- C. 결과 행은 L3 한 개이다.
- D. 서브쿼리가 다중 행을 반환하므로 오류가 발생한다.
정답: C
해설. 서브쿼리는 제품 A 평균 (100+300+500)/3=300(단일값)이므로 오류 없음(D 오답). WHERE QTY>300 통과 행은 L3-A(500)뿐이며, L2-A(300)은 300>300 거짓, L2-C는 NULL이라 제외. GROUP BY 후 HAVING SUM>250 조건도 만족하여 L3만 결과에 남는다.