Scalar Subquery 와 일반 Join 간의 변환 시 1대M 관계 정의 (일반 조인을 Scalar Subquery 로)
일반 Join 을 Scalar Subquery 로 재작성할 때 1:1 관계는 단순 SELECT 로, 1:M 관계는 집계 함수 또는
ROWNUM <= 1으로 처리해야 합니다. Scalar Subquery 는 메인 row 당 정확히 1 row 만 반환해야 하기 때문입니다.
핵심 정리
- 일반 Join → Scalar Subquery 변환 시 필수 / 옵션 관계는 무관합니다. 옵션 관계 (outer join) 든 필수 관계든 같은 형태로 변환할 수 있습니다.
- 1:M 관계의 M 쪽을 Scalar Subquery 로 옮길 때는 집계 함수 또는
ROWNUM <= 1이 필수입니다. 메인 row 당 N 개의 row 가 나오면 ORA-01427 (single-row subquery returns more than one row) 에러를 발생시킵니다. - 1:1 관계는 단순 SELECT 로 변환 가능합니다. PK 또는 unique 매칭이라 메인 row 당 정확히 1 row 가 보장됩니다.
시나리오
고객 (1) → 주문 (M) → 주문상품 (M) 의 1:M:M chain 관계입니다. 사용자 자료의 ER 다이어그램은 다음과 같습니다.
주문 을 메인 집합으로 보면 두 가지 관계가 등장합니다.
| 관계 | 카디널리티 | 변환 시 처리 |
|---|---|---|
| 주문 → 고객 | 1:1 (주문 row 당 고객 row 1 개) | 단순 SELECT |
| 주문 → 주문상품 | 1:M (주문 row 당 주문상품 row 여러 개) | 집계 함수 (MAX, SUM 등) 또는 ROWNUM <= 1 |
1) 원본 — 일반 Join + GROUP BY
1
2
3
4
5
6
7
8
SELECT O.주문번호, MIN(O.고객번호) AS 고객번호, MIN(C.고객명) AS 고객명
, MIN(O.주문금액) AS 총주문금액
, MAX(P.주문금액) AS 최대주문금액
FROM 고객 C, 주문 O, 주문상품 P
WHERE O.주문일자 BETWEEN :ORD_DT1 AND :ORD_DT2
AND C.고객번호 = O.고객번호
AND P.주문번호 = O.주문번호
GROUP BY O.주문번호;
주문번호 단위로 GROUP BY 한 뒤 각 그룹의 MIN(고객번호), MIN(고객명), MIN(주문금액), MAX(주문상품.주문금액) 을 추출합니다. 고객 과 주문 은 1:1 관계라 GROUP BY 후 단일 값이 보장되지만, 주문상품 은 1:M 관계라 MAX 집계가 필수입니다.
주문 이 메인 집합인데 주문상품 과의 1:M 조인 때문에 결과 row 가 폭증한 뒤 GROUP BY 로 다시 압축하는 비효율이 있습니다. Scalar Subquery 로 변환하면 이 폭증 자체가 사라집니다.
2) 수정 — Scalar Subquery 로 재작성
1
2
3
4
5
6
SELECT O.주문번호, O.고객번호
, (SELECT 고객명 FROM 고객 WHERE 고객번호 = O.고객번호) AS 고객명
, O.주문금액 AS 총주문금액
, (SELECT MAX(주문금액) FROM 주문상품 WHERE 주문번호 = O.주문번호) AS 최대주문금액
FROM 주문 O
WHERE 주문일자 BETWEEN :ORD_DT1 AND :ORD_DT2;
핵심 변환:
고객테이블 → 단순 Scalar Subquery. 주문 row 당 고객 row 1 개라(SELECT 고객명 FROM 고객 WHERE 고객번호 = O.고객번호)로 그대로 옮겨도 안전합니다.주문상품테이블 → 집계 Scalar Subquery. 주문 row 당 주문상품 row N 개이므로(SELECT MAX(주문금액) FROM 주문상품 WHERE 주문번호 = O.주문번호)로 집계가 필요합니다.주문자체 컬럼 (주문번호,고객번호,주문금액) 은 메인 SELECT 에서 그대로 노출.GROUP BY가 사라짐. 메인 집합이주문자체이고 그룹화 단위 (주문번호) 와 일치하므로 GROUP BY 가 불필요합니다.
분석
1:M 관계에서 집계가 필요한 이유
Scalar Subquery 는 SQL 표준상 단일 row, 단일 column 만 반환할 수 있습니다. 메인 row 당 N 개의 row 가 나오면 ORA-01427 (single-row subquery returns more than one row) 에러를 발생시킵니다. 따라서 1:M 의 M 쪽을 Scalar Subquery 로 옮길 때는 N 개의 row 를 1 개로 압축하는 장치가 필수입니다.
- 집계 함수 (MAX / MIN / SUM / COUNT / AVG): N 개의 row 를 의미 있는 단일 값으로 집계합니다. 본 시나리오의
MAX(주문금액)처럼 비즈니스 의미가 명확합니다. ROWNUM <= 1: N 개 중 1 개만 (옵티마이저가 먼저 만난 row) 반환합니다. ORDER BY 가 없으면 어떤 row 가 반환될지 불확정이라 사용 시 주의해야 합니다.
변환 룰: 메인 집합으로 삼을 테이블 (결과 row 단위가 되는 테이블) 을 정한 뒤, 다른 테이블은 모두 Scalar Subquery 로. 1:1 은 단순 SELECT, 1:M 은 집계 함수 또는
ROWNUM <= 1. 필수 / 옵션 관계는 변환 형태에 영향이 없습니다.
정리
- 일반 Join → Scalar Subquery 변환의 룰: 메인 집합 결정 후 다른 테이블은 Scalar Subquery 로 옮기되, 카디널리티 (1:1 vs 1:M) 에 따라 형태를 결정합니다.
- 1:M 관계의 M 쪽을 Scalar Subquery 로 옮길 때는 집계 함수 또는
ROWNUM <= 1이 필수입니다. 미적용 시 ORA-01427 이 발생합니다. - 필수 / 옵션 관계는 변환 형태에 무관합니다. 옵션 관계는 매칭이 없을 때 Scalar Subquery 가 NULL 을 반환하여 LEFT OUTER JOIN 과 같은 결과를 자연스럽게 보장합니다.