[Oracle] 오라클 조인(IN, EXISTS, (+))

조인을 그렇게 많이 봤는데 제대로 이해하지 못했다는 것을 깨달았다.
갑자기 툭 던지면 바로 뽑아낼 수 있도록 오라클의 조인에 대해서 적어보고자 한다.
전 POST에서는 LEFT OUTER JOIN과 RIGHT OUTER JOIN, FULL OUTER JOIN만 다뤘다.
이번 POST에선 좀 더 다양한 형태의 조인을 설명하고자 한다.

이미지 출처 : https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

• 위와 같은 형태를 IN/NOT IN, EXISTS/NOT EXISTS, JOIN의 형태로 보여주고자한다. (ORACLE 문법으로 ANSI 사용X)
• 일단 테이블은 전 ORACLE POST와 같이 아래와 같다.
  

SQL

1. A(EMPLOYEE) 테이블만 출력하기

JOIN

SELECT A.*
 FROM  EMPLOYEE A
     , DEPT B
 WHERE A.DEPT_CD = B.DEPT_CD(+);

IN/NOT IN

 SELECT A1.EMP_CD AS EMP_CD
  FROM EMPLOYEE A1
  WHERE A1.DEPT_CD IN (
    SELECT B.DEPT_CD
     FROM DEPT B
    WHERE A1.DEPT_CD IS NOT NULL
 )
 UNION ALL -- union이나 union all을 쓸 경우 위의 select에서만 'AS 별칭' 설정한다면 아래도 자동 설정된다.
 SELECT A2.EMP_CD 
  FROM EMPLOYEE A2
  WHERE A2.DEPT_CD NOT IN (
    SELECT B.DEPT_CD
     FROM DEPT B
     WHERE A2.DEPT_CD IS NOT NULL
  );

• NOT IN/IN은 NULL값을 취급하지 않기 때문에 NULL일 경우 조회하지 않는다 따라서 A2.DEPT_CD IS NOT NULL이라는 문구로 미리 처리를 해줘야 누락없이 조회할 수 있다.
• UNION/ UNION ALL을 쓸 경우, 각각의 SELECT의 컬럼을 똑같이 설정해줘야한다. (설정안하면 UNION/UNION ALL이 안된다.)

EXISTS/NOT EXISTS

SELECT A.EMP_CD AS EMP_CD
 FROM EMPLOYEE A
 WHERE EXISTS(
    SELECT 'x'
     FROM DEPT B
     WHERE A.DEPT_CD = B.DEPT_CD
 )
 UNION ALL
SELECT A.EMP_CD
 FROM EMPLOYEE A
 WHERE NOT EXISTS(
    SELECT 'x'
     FROM DEPT B
     WHERE A.DEPT_CD = B.DEPT_CD
 )

• EXISTS은 NULL도 처리하기 때문에 따로 NULL값을 설정해주지 않아도 된다.
• EXISTS/NOT EXISTS 존재여부를 판단하여 해당 결과를 반환하는 함수이다.

번외

-- 사실 이게 가장 효율적인 코드이다.
SELECT *
 FROM EMPLOYEE;

B(DEPT) 테이블만 출력하기

JOIN

-- 1.
SELECT A.DEPT_CD
       , A.DEPT_NM
       , A.DEPT_UP_CD
 FROM  DEPT A
     , EMPLOYEE B
 WHERE A.DEPT_CD = B.DEPT_CD(+)
 GROUP BY A.DEPT_CD, A.DEPT_NM, A.DEPT_UP_CD;
 
-- 2.
SELECT DISTINCT A.*
 FROM  DEPT A
     , EMPLOYEE B
 WHERE A.DEPT_CD = B.DEPT_CD(+);

• EMPLOYEE 테이블에 해당 DEPT의 PK(DEPT_CD)가 중복이 되기도 하기 때문에 중복 제거를 위해 GROUP BY나 DISTINCT 키워드 사용을 해야한다. (GROUP BY를 더 권장한다.)

IN/NOT IN

SELECT A.DEPT_CD DEPT_CD
 FROM DEPT A
 WHERE A.DEPT_CD IN (
    SELECT B.DEPT_CD 
     FROM EMPLOYEE B
 )
 UNION ALL
SELECT A.DEPT_CD
 FROM DEPT A
 WHERE A.DEPT_CD NOT IN (
    SELECT B.DEPT_CD
     FROM EMPLOYEE B
     WHERE B.DEPT_CD IS NOT NULL 
 )

• B를 대상으로 IS NOT NULL을 설정한 이유는 DEPT_CD(A 테이블)에겐 DEPT_CD가 PK이므로 NULL 값이 나올 수 없는데 B(EMPLOYEE) 테이블을 기준으로는 DEPT_CD가 나올 수 있으며 NOT IN/IN에 NULL값이 생기게 되면 조회를 할 수 없기 때문에 WHERE절에 조건식을 추가해주어야 한다.

EXISTS/NOT EXISTS

SELECT A.DEPT_CD AS DEPT_CD
 FROM DEPT A
 WHERE EXISTS(
    SELECT 'x'
     FROM EMPLOYEE B
     WHERE A.DEPT_CD = B.DEPT_CD
 )
 UNION ALL
SELECT A.DEPT_CD
 FROM DEPT A
 WHERE NOT EXISTS(
    SELECT 'x'
     FROM EMPLOYEE B
     WHERE A.DEPT_CD = B.DEPT_CD
 );

번외

 -- B(DEPT)테이블만 뽑는거라면 이게 가장 효율적이다.
 SELECT * 
  FROM DEPT;

A(EMPLOYEE)와 B(DEPT)의 교집합 출력하기

JOIN

SELECT *
 FROM  EMPLOYEE A
     , DEPT B
 WHERE A.DEPT_CD = B.DEPT_CD;

NOT IN/IN

-- 1.
SELECT A.DEPT_CD
 FROM EMPLOYEE A
 WHERE A.DEPT_CD IN (
    SELECT B.DEPT_CD
     FROM DEPT B
 );
-- 2. 
SELECT  A.DEPT_CD
 FROM DEPT A
 WHERE A.DEPT_CD IN (
    SELECT B.DEPT_CD
     FROM EMPLOYEE B
     WHERE B.DEPT_CD IS NOT NULL
     GROUP BY B.EMP_CD
 );
 

• 2와 같이 기준이 변하게 되면 해당 결과 조회가 달라진다(DEPT테이블은 DEPT_CD 중복허용이 불가능하지만, EMPLOYEE테이블의 DEPT_CD는 중복이 허용되기 때문에)

NOT EXISTS/EXISTS

SELECT A.DEPT_CD
 FROM EMPLOYEE A
 WHERE EXISTS (
    SELECT 'x'
     FROM DEPT B
 )
 AND A.DEPT_CD IS NOT NULL; 
 
 SELECT A.DEPT_CD
 FROM DEPT A
 WHERE EXISTS (
    SELECT 'x'
     FROM EMPLOYEE B
     where A.DEPT_CD = B.DEPT_CD
 );

• EMPLOYEE 테이블은 DEPT_CD(DEPT 테이블의 PK)의 NULL값을 가지고 있기 때문에 NULL값은 DEPT 테이블에 존재하지 않다는 뜻이므로 A.DEPT_CD가 NULL이 아닌 값만 뽑아야 한다.

A(EMPLOYEE) 테이블에서 B(DEPT) 테이블의 데이터 제거하여 출력하기

JOIN

SELECT A.* 
 FROM  EMPLOYEE A
     , DEPT B
 WHERE A.DEPT_CD = B.DEPT_CD(+)
   AND A.DEPT_CD IS NULL; 

• A(EMPLOYEE)테이블의 DEPT_CD가 IS NULL이라는 것은 B(DEPT) 테이블의 PK를 안갖는 것이므로 교집합을 제거한 거로 볼 수 있다.

NOT IN/IN

SELECT A2.EMP_CD 
  FROM EMPLOYEE A2
  WHERE A2.DEPT_CD NOT IN (
    SELECT B.DEPT_CD
     FROM DEPT B
     WHERE A2.DEPT_CD IS NOT NULL
  );

NOT EXISTS/EXISTS

 SELECT A.EMP_CD
 FROM EMPLOYEE A
 WHERE NOT EXISTS(
    SELECT 'x'
     FROM DEPT B
     WHERE A.DEPT_CD = B.DEPT_CD
 );

A(DEPT) 테이블에서 B(EMPLOYEE) 테이블의 데이터 제거하여 출력하기

JOIN

SELECT A.* 
 FROM  DEPT A
     , EMPLOYEE B
 WHERE A.DEPT_CD = B.DEPT_CD(+)
  AND B.DEPT_CD IS NULL;

NOT IN/IN

SELECT A.DEPT_CD
 FROM DEPT A
 WHERE A.DEPT_CD NOT IN (
    SELECT B.DEPT_CD
     FROM EMPLOYEE B
     WHERE B.DEPT_CD IS NOT NULL 
 );

NOT EXISTS/EXISTS

SELECT A.DEPT_CD
 FROM DEPT A
 WHERE NOT EXISTS(
    SELECT 'x'
     FROM EMPLOYEE B
     WHERE A.DEPT_CD = B.DEPT_CD
 );

A(EMPLOYEE)테이블, B(DEPT)테이블 FULL OUTER JOIN

• 해당 DEPT_CD만 출력하므로 드래그한 부분의 값이 나오면 된다.

JOIN

SELECT A.DEPT_CD
 FROM EMPLOYEE A
UNION ALL
SELECT A.DEPT_CD
 FROM  DEPT A
     , EMPLOYEE B
 WHERE A.DEPT_CD = B.DEPT_CD(+)
  AND B.DEPT_CD IS NULL;

NOT IN/IN

SELECT A.DEPT_CD
 FROM EMPLOYEE A
UNION ALL
SELECT A.DEPT_CD
 FROM DEPT A
 WHERE A.DEPT_CD NOT IN (
    SELECT B.DEPT_CD
     FROM EMPLOYEE B
     WHERE B.DEPT_CD IS NOT NULL
 );

NOT EXISTS/EXISTS

SELECT A.DEPT_CD
 FROM EMPLOYEE A
UNION ALL
SELECT A.DEPT_CD
 FROM DEPT A
 WHERE NOT EXISTS (
    SELECT 'x'
     FROM EMPLOYEE B
     WHERE A.DEPT_CD = B.DEPT_CD
 );

A(EMPLOYEE)테이블, B(DEPT)테이블에서 교집합만 제거하여 출력하기

JOIN

SELECT A.DEPT_CD
 FROM  DEPT A
     , EMPLOYEE B
 WHERE A.DEPT_CD = B.DEPT_CD(+)
  AND B.DEPT_CD IS NULL
UNION ALL 
SELECT A.DEPT_CD
 FROM  EMPLOYEE A
     , DEPT B
 WHERE A.DEPT_CD = B.DEPT_CD(+)
   AND A.DEPT_CD IS NULL;

NOT IN/IN

SELECT A.DEPT_CD
 FROM DEPT A
 WHERE A.DEPT_CD NOT IN (
    SELECT B.DEPT_CD
     FROM EMPLOYEE B
     WHERE B.DEPT_CD IS NOT NULL 
 )
UNION ALL
SELECT  A2.DEPT_CD 
  FROM EMPLOYEE A2
  WHERE A2.DEPT_CD NOT IN (
    SELECT B.DEPT_CD
     FROM DEPT B
     WHERE A2.DEPT_CD IS NOT NULL
  );

NOT EXISTS/EXISTS

SELECT A.DEPT_CD
 FROM DEPT A
 WHERE NOT EXISTS(
    SELECT 'x'
     FROM EMPLOYEE B
     WHERE A.DEPT_CD = B.DEPT_CD
 )
UNION ALL
 SELECT A.DEPT_CD
 FROM EMPLOYEE A
 WHERE NOT EXISTS(
    SELECT 'x'
     FROM DEPT B
     WHERE A.DEPT_CD = B.DEPT_CD
 )

느낀 점

• 아래로 갈 수록 위의 부분을 가져다 UNION ALL한 것이라 설명은 따로 하지않았다.
• 다음 POST에선 이번 POST에서 사용한 NOT IN/IN, EXISTS/NOT EXISTS, JOIN에 대해 개념 정리를 하려고 한다.