ORA-01799: 열은 하위 질의에 포괄 조인될 수 없습니다. (ORA-01799: a column may not be outer-joined to a subquery) 해당 오류는 오라클 아우터 조인(외부 조인)에서 서브쿼리를 시도하려고 할 때 발생합니다. 해당 오류를 수정하는 방법에 대해 알아보도록 합시다.
아참, 오류에서 말하는 포괄조인이 외부조인을 뜻한답니다! 최근에는 모두 외부조인이라고 해석하는데, 당시에는 포괄이라고 해석을 하였습니다.
2가지 예시를 살펴볼 것인데요, 첫 번째는 레프트 조인, 두 번째는 풀 아우터 조인입니다.
하위 질의 포괄 조인 ORA-01799: 열은 하위 질의에 포괄 조인될 수 없습니다.
해당 오류는 문자 그대로 아우터 조인에서 서브쿼리를 사용하려고 시도할 때에 발생합니다. 이 때에는 하위 질의에 해당하는 컬럼을 뷰로 바꾸어 해결할 수 있답니다. 예시를 바로 살펴보겠습니다!
직원정보 테이블에서 'KING'과 같은 부서에서 일하고 있는 경우 표시를 하려고 하는데, 다음과 같이 ORA-01799 오류가 발생합니다.
SELECT EMPNO,ENAME,EMP.DEPTNO, DECODE (DEPT.DEPTNO, NULL, NULL, 'Working With KING')
FROM EMP
LEFT JOIN
DEPT
ON DEPT.DEPTNO = EMP.DEPTNO
AND DEPT.DEPTNO = (SELECT MAX (DEPTNO)
FROM EMP
WHERE ENAME = 'KING');
ERROR at line 1:
ORA-01799: a column may not be outer-joined to a subquery다음처럼 해당 컬럼을 바꾸어 조회하여 해결할 수 있답니다.
SELECT EMPNO,ENAME,EMP.DEPTNO, DECODE (DEPT.DEPTNO, NULL, NULL, 'Working With KING')
FROM EMP
LEFT JOIN
(SELECT *
FROM DEPT
WHERE DEPTNO = (SELECT MAX (DEPTNO)
FROM EMP
WHERE ENAME = 'KING')) DEPT
ON DEPT.DEPTNO = EMP.DEPTNO;
EMPNO ENAME DEPTNO DECODE(DEPT.DEPTNO,NULL,NULL,'WORK
---------- -------------------- ---------- ----------------------------------
7839 KING 10 Working With KING
7934 MILLER 10 Working With KING
7782 CLARK 10 Working With KING
7900 JAMES 30
7844 TURNER 30
7654 MARTIN 30
7521 WARD 30
7499 ALLEN 30
7698 BLAKE 30
7876 ADAMS 20
7369 SMITH 20
7902 FORD 20
7788 SCOTT 20
7566 JONES 20
14 rows selected.하위 질의 풀 아우터 조인 시 ORA-01799 오류
ORA-01799 오류는 레프트 아우터 조인과 라이트 아웃 조인을 오라클 조인인 (+) 기호와 함께 표현한 쿼리에서 발생하며, 이 글을 보고 계신 여러분의 쿼리에도 (+) 사인이 있을 것입니다. 해결 방법은 오라클 조인에서 ANSI 조인으로 변경하는 방법 또는 뷰를 먼저 조회하는 방법이 있습니다.
- 오라클 조인 => ANSI 조인 변경하기
- 뷰 조회하기
문제가 발생하는 예시 쿼리는 다음과 같습니다. 해당 쿼리는 실행 시 오류가 발생합니다.
SELECT *
FROM EMP, DEPT
WHERE DEPT.DEPTNO(+) = EMP.DEPTNO
AND DEPT.DEPTNO(+) IN (SELECT '10' FROM DUAL
UNION ALL
SELECT '20' FROM DUAL)
ORDER BY EMP.DEPTNO, DEPT.DEPTNO, EMPNO;
ERROR at line 7:
ORA-01799: a column may not be outer-joined to a subquery오라클 조인에서 ANSI 조인으로 변경하여 해결하기
ANSI 조인으로 변경하여 해결하는 방법입니다. 아우터 조인 형식으로 수정한 간단한 예시를 추가합니다.
--ANSI 조인으로 해결 방법
SELECT *
FROM EMP
LEFT JOIN
DEPT
ON DEPT.DEPTNO = EMP.DEPTNO
AND DEPT.DEPTNO IN (SELECT '10' FROM DUAL
UNION ALL
SELECT '20' FROM DUAL)
ORDER BY EMP.DEPTNO, DEPT.DEPTNO, EMPNO;
--14 rows selected.인라인뷰를 생성하여 해결하기
인라인뷰 형태로 변경하여 해결하는 방법입니다. 두 테이블을 바로 조인하지 않고 아우터 조인의 대상이 되는 테이블을 뷰 형태로 가공하였습니다.
--인라인 뷰로 해결 방법
SELECT *
FROM EMP,
(SELECT *
FROM DEPT
WHERE DEPTNO IN (SELECT '10' FROM DUAL
UNION ALL
SELECT '20' FROM DUAL)) DEPTVIEW
WHERE DEPTVIEW.DEPTNO(+) = EMP.DEPTNO
ORDER BY EMP.DEPTNO, DEPTVIEW.DEPTNO, EMPNO;
--14 rows selected.(+) 기호를 그냥 생략하면 어떻게 될까?
오류가 발생한다고 (+) 조인 기호를 그냥 지워버리면 전혀 다른 결과를 조회합니다. 오라클 아우터 조인은 WHERE부터 시작하기에 조인 컬럼의 (+) 여부에 따라 조건이 전혀 다르게 해석이 되는 점을 기억해 주세요. 다음 쿼리는 (+)를 생략하였을 때 어떠한 ANSI 조인 결과와 동일한지 보여드리고 있습니다.
SELECT *
FROM EMP, DEPT
WHERE DEPT.DEPTNO(+) = EMP.DEPTNO
AND DEPT.DEPTNO IN (SELECT '10' FROM DUAL
UNION ALL
SELECT '20' FROM DUAL)
ORDER BY EMP.DEPTNO, DEPT.DEPTNO, EMPNO;
--8 rows selected.위의 쿼리는 다음 ANSI조인과 조회 결과가 같습니다.
SELECT *
FROM EMP LEFT JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO
WHERE DEPT.DEPTNO IN (SELECT '10' FROM DUAL
UNION ALL
SELECT '20' FROM DUAL)
ORDER BY EMP.DEPTNO, DEPT.DEPTNO, EMPNO;
--8 rows selected.