Oracle ORA-01427 오류 원인과 해결 방법 완벽 가이드

ORA-01427 single-row subquery returns more than one row 는?

ORA-01427 에러는 단일 행을 반환해야 하는 서브쿼리(Subquery)가 실제로 두 개 이상의 행을 반환할 때 발생하는 Oracle 오류입니다. 주로 SELECT, UPDATE, DELETE 구문에서 = 연산자와 함께 서브쿼리를 사용할 때 나타나며, 오라클 옵티마이저가 단일 값(Scalar Value)을 기대하는 위치에 복수의 결과가 반환될 경우 즉시 예외를 발생시킵니다. 이 에러는 데이터 설계의 허점이나 WHERE 조건의 불완전함에서 비롯되는 경우가 많으며, 운영 환경에서 예상치 못한 데이터 증가로 인해 갑작스럽게 터지는 경우도 흔히 발생합니다.

---

주요 발생 원인

1. 스칼라 서브쿼리에서 중복 데이터 반환

가장 빈번한 원인으로, SELECT 절이나 WHERE 절에서 = 연산자로 서브쿼리를 연결할 때 해당 서브쿼리가 유일한 값을 보장하지 못하는 경우입니다. 예를 들어, 참조 테이블에 동일한 키 값을 가진 레코드가 두 개 이상 존재할 때 이 문제가 발생합니다. 개발 초기에는 데이터가 정상이었지만 운영 중 데이터가 쌓이면서 뒤늦게 오류가 발생하는 패턴이 매우 많습니다.

2. UPDATE 문의 SET 절에서 서브쿼리 사용 시 다중 행 반환

UPDATE 문에서 특정 컬럼 값을 서브쿼리 결과로 설정할 때, 서브쿼리가 조건에 맞는 여러 행을 반환하면 에러가 발생합니다. 업무 로직상 “마지막 값” 또는 “최댓값”만 가져와야 하는데 집계 함수나 ROWNUM 처리를 누락한 경우가 대표적입니다. 특히 배치 프로그램이나 마이그레이션 스크립트에서 자주 목격됩니다.

3. 조인 조건 누락 또는 잘못된 WHERE 절로 인한 다중 행 반환

서브쿼리 내부의 WHERE 조건이 불완전하거나, 외부 쿼리와의 연결 조건(Correlation)이 빠져 있는 경우 의도치 않게 전체 테이블을 스캔하여 다중 행을 반환합니다. 코드 리뷰 없이 급하게 작성된 쿼리나, 요구사항 변경 후 수정이 미흡한 SQL에서 이 원인이 많이 발견됩니다. 특히 서브쿼리 안에 외부 테이블과의 연결 컬럼이 누락된 비상관 서브쿼리(Non-Correlated Subquery)가 주범인 경우가 많습니다.

---

해결 방법

원인 1 해결: 집계 함수로 단일 값 보장

서브쿼리에 MAX(), MIN(), SUM() 등 집계 함수를 적용하여 반드시 단일 행이 반환되도록 강제합니다.

-- 문제 있는 쿼리 (ORA-01427 발생)
SELECT employee_id, employee_name,
       (SELECT department_name
        FROM departments
        WHERE location_id = 1700) AS dept_name
FROM employees;

-- 해결: MAX() 또는 MIN() 집계 함수 사용
SELECT employee_id, employee_name,
       (SELECT MAX(department_name)
        FROM departments
        WHERE location_id = 1700) AS dept_name
FROM employees;

원인 1 추가 해결: ROWNUM 또는 FETCH FIRST 사용

-- ROWNUM으로 단일 행 제한 (Oracle 11g 이하)
SELECT employee_id,
       (SELECT department_name
        FROM departments
        WHERE location_id = 1700
        AND ROWNUM = 1) AS dept_name
FROM employees;

-- FETCH FIRST 사용 (Oracle 12c 이상 권장)
SELECT employee_id,
       (SELECT department_name
        FROM departments
        WHERE location_id = 1700
        ORDER BY department_id
        FETCH FIRST 1 ROW ONLY) AS dept_name
FROM employees;

원인 2 해결: UPDATE 문에서 서브쿼리 단일 행 보장

-- 문제 있는 UPDATE (ORA-01427 발생)
UPDATE employees e
SET e.salary = (SELECT salary
                FROM salary_history sh
                WHERE sh.employee_id = e.employee_id);

-- 해결 방법 1: MAX() 집계 함수로 최신 급여 가져오기
UPDATE employees e
SET e.salary = (SELECT MAX(sh.salary)
                FROM salary_history sh
                WHERE sh.employee_id = e.employee_id);

-- 해결 방법 2: 서브쿼리 내 추가 조건으로 단일 행 보장
UPDATE employees e
SET e.salary = (SELECT sh.salary
                FROM salary_history sh
                WHERE sh.employee_id = e.employee_id
                AND sh.effective_date = (
                    SELECT MAX(effective_date)
                    FROM salary_history
                    WHERE employee_id = e.employee_id
                ));

원인 3 해결: WHERE 절 및 상관 조건 보완

-- 문제 있는 쿼리 (조인 조건 누락 - 비상관 서브쿼리)
SELECT e.employee_id, e.employee_name
FROM employees e
WHERE e.department_id = (SELECT d.department_id
                          FROM departments d
                          WHERE d.manager_id > 100);
                          -- 여러 부서가 반환됨!

-- 해결 방법 1: IN 연산자로 변경 (다중 행 허용)
SELECT e.employee_id, e.employee_name
FROM employees e
WHERE e.department_id IN (SELECT d.department_id
                           FROM departments d
                           WHERE d.manager_id > 100);

-- 해결 방법 2: EXISTS 사용 (성능상 유리한 경우 많음)
SELECT e.employee_id, e.employee_name
FROM employees e
WHERE EXISTS (SELECT 1
              FROM departments d
              WHERE d.department_id = e.department_id
              AND d.manager_id > 100);

-- 해결 방법 3: 상관 서브쿼리(Correlated Subquery)로 수정
SELECT e.employee_id, e.employee_name
FROM employees e
WHERE e.department_id = (SELECT d.department_id
                          FROM departments d
                          WHERE d.department_id = e.department_id
                          AND d.manager_id > 100);

실무 진단 쿼리: 문제 서브쿼리 사전 검증

-- 서브쿼리 단독 실행으로 반환 행 수 확인
SELECT COUNT(*) AS row_count, department_name
FROM departments
WHERE location_id = 1700
GROUP BY department_name
HAVING COUNT(*) > 1;

-- 실제 데이터 중복 여부 확인
SELECT location_id, COUNT(*) AS cnt
FROM departments
GROUP BY location_id
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

---

예방 방법

1. 서브쿼리 작성 시 사전 단독 실행 및 행 수 검증 습관화

서브쿼리를 메인 쿼리에 포함하기 전, 반드시 해당 서브쿼리만 단독으로 실행하여 반환되는 행의 수를 확인하는 습관을 들이는 것이 중요합니다. COUNT(*) 로 반환 행 수를 체크하거나, 데이터가 많을 경우 테스트 환경에서 충분한 데이터 볼륨으로 검증한 뒤 운영 환경에 적용해야 합니다. 특히 스칼라 서브쿼리(SELECT 절 서브쿼리)나 = 연산자를 사용하는 경우에는 유니크 제약(UNIQUE Constraint) 또는 집계 함수를 통해 항상 단일 행을 보장하는 패턴을 표준으로 삼으세요.

2. 단일 행 보장이 필요한 컬럼에 UNIQUE 제약 조건 적용

서브쿼리가 참조하는 기준 테이블의 컬럼에 UNIQUE 또는 PRIMARY KEY 제약 조건을 설정하면, 데이터베이스 레벨에서 중복 데이터 자체를 원천 차단할 수 있습니다. 데이터 모델링 단계에서 키 설계를 철저히 하고, 복합 조건으로 단일 행을 식별해야 하는 경우에는 복합 유니크 인덱스(Composite Unique Index)를 활용하세요. 이 방법은 ORA-01427뿐 아니라 여러 데이터 정합성 문제를 근본적으로 예방하는 Best Practice입니다.

---

관련 에러

• ORA-01422: exact fetch returns more than requested number of rows – PL/SQL의 SELECT INTO 구문에서 단일 행이 아닌 복수 행이 반환될 때 발생하며, ORA-01427과 매우 유사한 맥락의 에러입니다.
• ORA-00913: too many values – INSERT나 SELECT에서 컬럼 수와 값의 수가 일치하지 않을 때 발생하는 에러로, 구조적 불일치 측면에서 연관됩니다.
• ORA-01403: no data found – 반대 방향의 에러로, 단일 행을 기대하는 쿼리에서 결과가 전혀 없을 때 발생하며 PL/SQL 예외 처리 시 ORA-01422와 함께 쌍으로 처리하는 경우가 많습니다.

---

본 포스트는 AI가 생성한 기술 가이드입니다. 운영 환경 적용 전 충분한 검토를 권장합니다.