오라클 SQL조인, 안티 조인(Anti Join)

 

오라클 SQL조인, 안티 조인(Anti Join)

조인의 대상이 되는 테이블과 일치하지 않는 데이터를 추출하는 조인방식 입니다.
 

서브 쿼리의 테이블에는 없는 메인 쿼리의 테이블의 데이터만 추출하는 조인 방법으로 NOT IN, NOT EXISTS 사용시 발생하는 조인연산이며 오라클에서 중첩루프조인 안티, 머지조인 안티, 해시 조인 안티로 실행 됩니다.
 

NOT IN을 이용한 안티 조인 예문 입니다. EMP 테이블에서 사번, 성명, 부서코드를 추출하는데 부서코드를 가지고 있지않은 사원들만 추출하는 쿼리 문 입니다. “KING” 사원은 최고관리자로 부서코드(deptno) 값을 가지고 있지않지만  NULL값이므로  결과 대상에서 제외 되었습니다.(NULL과 연산하는 결과는 NULL이 됨)

-- NOT IN SELECT empno, ename, deptno FROM emp WHERE deptno NOT IN (   SELECT deptno   FROM dept )

 

<실행결과>

선택된 행 없음

<실행계획(F10)>

오라클19C에서 별다른 힌트구문없이 그냥 실행 했을 때 해시조인 안티로 실행계획을 수립함을 확인할 수 있습니다.

NOT EXISTS을 이용한 안티 조인 예문 입니다. DEPT 테이블에서 부서코드, 부서명을 출력 하는데  EMP 테이블에 부서원들을 가지고 있지 않은 부서코드, 부서명을 출력합니다. 40번 부서는 EMP 테이블에 해당하는 부서원들이 없습니다. HASH_AJ 힌트구문은 안티 해시조인을 하라는 힌트구문으로 사용하지 않으면 오라클19C에서는 머지조인 안티로 쿼리문을 수행 합니다.

-- EXISTS SELECT deptno, dname FROM    dept WHERE NOT EXISTS    (SELECT /*+ HASH_AJ */ 1     FROM   emp     AND      emp.deptno = dept.deptno)

 

<실행결과>

	DEPTNO	ENAME
1	40	OPERATION

<실행계획(F10)>

#SQL조인, #조인이란, #안티조인, #AntiJoin, #SQL교육, #SQL강의, #오라클교육, #오라클, #오라클조인

오라클 조인방법, SQL 세미 조인(Semi Join)

 

오라클 조인방법, SQL 세미 조인(Semi Join)

세미조인은 SQL에서 서브쿼리에 IN, EXISTS를 사용했을 때 주로 나타나는 조인방식으로 서브쿼리에서 메인쿼리와의 연결을 마치 조인처럼 수행하는 것을 의미 합니다. 서브쿼리문을 마치 조인처럼 실행을 하는 것입니다.

중첩루프 세미조인, 해시 세미조인, 소트 머지 세미조인, 필터 세미조인, 부정형 세미조인 등이 있으며 자세한 내용은 오라클 힌트, SQL 튜닝 편에서 확인하세요.

서브쿼리는 메인쿼리에 종속적이므로 메인쿼리의 결과를 변하게 할 수는 없는 것이 특징인데 세미조인의 결과는 항상 메인쿼리의 결과 셋 중의 일부 또는 전체가 됩니다.

사원(EMP) 테이블 입니다.

SELECT empno, ename, deptno FROM emp

 

<실행결과>

	EMPNO	ENAME	DEPTNO
1	7369	SMITH	20
2	7499	ALLEN	30
3	7521	WARD	30
4	7566	JONES	20
5	7654	MARTIN	30
6	7698	BLAKE	30
7	7782	CLARK	10
8	7788	SCOTT	20
9	7839	KING
10	7844	TURNER	30
11	7876	ADAMS	20
12	7900	JAMES	30
13	7902	FORD	20
14	7934	MILLER	10

부서(DEPT) 테이블 입니다.

SELECT * FROM dept

<실행결과>

	DEPTNO	DNAME	LOC
1	10	ACCOUNTING	NEW YORK
2	20	RESEARCH	DALLAS
3	30	SALES	CHICAGO
4	40	OPERATIONS	BOSTON

IN을 이용한 세미조인 예문 입니다. DEPT 테이블에서 부서코드, 부서명을 출력하는데 EMP 테이블에 부서원들이 있는 부서만 출력을 하고 싶습니다. 40번 부서코드를 가진 사원은 EMP 테이블에 존재하지 않으므로 40번 부서는 출력되지 않습니다. HASH_SJ 힌트구문은 해시 세미조인을 하라는 구문으로 기술하지 않으면 오라클 19C에서는 머지 해시조인으로 쿼리문을 실행 합니다.

-- IN SELECT deptno, dname FROM   dept WHERE deptno in     (SELECT /*+ HASH_SJ */ deptno     FROM    emp)

 

<실행결과>

	DEPTNO	DNAME
1	10	ACCOUNTING
2	20	RESEARCH
3	30	SALES

<실행계획(F10)>

-- EXISTS SELECT deptno, dname FROM   dept WHERE EXISTS    (SELECT /*+ HASH_SJ */ 1     FROM   emp     WHERE emp.deptno = dept.deptno)

 

<실행결과>

	DEPTNO	DNAME
1	10	ACCOUNTING
2	20	RESEARCH
3	30	SALES

<실행계획(F10)>

위 EXISTS 쿼리 예문에서 HASH_SJ 대신 중첩루프 세미조인을 하라는 힌트구문인 NL_SJ을 사용하여 다시 실행을 해보겠습니다. 오라클은 서브쿼리로 작성된 구문을 중첩루프조인 형태로 실행 계획을 세우고 실행을 합니다. 머지 세미조인으로 수행하기 위한 MERGE_SJ 힌트 구문도사용하여 테스트 해보시기 바랍니다.   실행계획인 중요한 이유는 대용량의 데이터인 경우 어떤 절차와 어떤 조인 방법으로 실행되느냐에 따라 성능이 좌우 됩니다. 드라이빙 되는(최초에 읽는) 테이블의 데이터가 어마어마하게 많다면 중첩루프조인 으로는 어렵습니다. 한건식 읽으면서 안쪽 Inner 테이블과 처음 읽은 건수 만큼 계속 조인을 해야 하니까요, 이럴 때는 해시조인 형태로 수행을 하는 것이 성능상 대체로 유리 합니다.

-- EXISTS SELECT deptno, dname FROM    dept WHERE EXISTS    (SELECT /*+ NL_SJ */ 1     FROM   emp     WHERE emp.deptno = dept.deptno)

 

<실행결과>

	DEPTNO	DNAME
1	10	ACCOUNTING
2	20	RESEARCH
3	30	SALES

<실행계획(F10)>

#ORACLE, #SQL세미조인, #세미조인, #오라클세미조인, #semijoin, #SQL, #ORACLE교육, #오라클, #sQL교육