[Oracle] IN, EXISTS, JOIN(+)

"설명을 앞서 예시로 사용할 테이블은 아래와 같다."

IN/NOT IN

IN

• in 구문의 경우 입력된 값들 중에서 하나라도 일치하는 것이 있다면 조회가 된다.
• 예시

SELECT *
 FROM EMPLOYEE A
 WHERE A.DEPT_CD IN (SELECT DEPT_CD
                     FROM DEPT);
                     
SELECT *
 FROM EMPLOYEE A
 WHERE A.DEPT_CD IN ('001', '002', '003', '004');
    
SELECT *
 FROM DEPT A
 WHERE A.DEPT_CD IN (SELECT DEPT_CD
                      FROM EMPLOYEE);
                      
                      
SELECT *
 FROM DEPT A
 WHERE A.DEPT_CD IN ('001', '002', '003', '004');
 

• 드라이빙 테이블이 EMPLOYEE일 때의 결과
  

• 드라이빙 테이블이 DEPT일 때의 결과
  

• IN안에 있는 sql문이 OR연산을 한다는 것을 결과만 봐도 알 수 있을 것이다.

NOT IN

• IN 구문과 같은 방식으로 작성하며, NOT IN 구문은 사용된 NOT IN절에 들어있는 서브쿼리의 결과 요소들과 일치하지 않는 값을 체크하여 반환해준다.
• IN 구문의 경우 서브쿼리 결과로 나온 값들 중 일치하는 값이 하나라도 있어도 조회가 되지만, NOT IN의 경우 서브쿼이의 결과로 나온 모든 값들과 일치하는지 체크한다.
• NOT IN(여기에 들어가는 요소들 중 하나라도 가지고 있으면 안된다.)
• 예시

-- 1
SELECT *
 FROM EMPLOYEE A
 WHERE A.DEPT_CD NOT IN (SELECT DEPT_CD
                     FROM DEPT);
-- 2                     
SELECT *
 FROM EMPLOYEE A
 WHERE A.DEPT_CD NOT IN ('001', '002', '003', '004');
 
-- 3
SELECT *
 FROM DEPT A
 WHERE A.DEPT_CD NOT IN (SELECT DEPT_CD
                      FROM EMPLOYEE);
-- 4                                        
SELECT *
 FROM DEPT A
 WHERE A.DEPT_CD NOT IN ('001', '002', '003', '004');

• 1~3번의 결과는 아래와 같다.
  

• 4번의 SQL문 결과는 아래와 같다.
  

• NOT IN구문일 경우, AND 연산이 된다는 것을 결과를 보면 확인할 수 있다.

• 3번 sql문을 예시로 들면 NOT IN구문인 서브쿼리를 본다면 EMPLOYEE안에 있는 DEPT_CD는 아래와 같다.
  

• 즉, 001, 002, 003, 004, null 값을 가지고 있는데, 여기서 문제가 생긴다!!!

• DB에서 null값을 비교연산하게 되면, 예를 들어 DEPT_CD가 005일 때 001, 002, 003, 004까지 검사를 했다고 치자. 그렇다면 다음 연산할 것은 DEPT_CD(005) != NULL인데, 여기서 문제가 생긴다는 것이다. 005와 NULL을 비교했을 떄 UNKNOWN(=FALSE)값이 발생하게 되고 AND연산으로 처리하는 NOT IN구문에 대한 결과는 FALSE가 되어 출력되지 않는 다는 것!!!

• 그렇다면 NOT IN을 사용하기 위해선 NULL 값이 없는 테이블이어야 하나?

• "그것은 아니다." 아래와 같이 설정해준다면 원하는 결과 값을 가질 수 있다.

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

• WHERE절 NULL이 아닌 것으로 NULL값을 피한다면 비교할 일도 없어지니 결과는 아래와 같이 나오게 된다

1

2

정리

• IN/NOT IN은 IN/NOT IN절 즉, 서브쿼리 먼저 실행하고 그 다음 메인쿼리를 실행해 서브쿼리에 있는 값이 메인쿼리에 있는지 없는지 컬럼값을 비교한다.
• 그렇기 때문에 IN/NOT IN절에 있는 구문의 결과 값의 크기(데이터의 양)이 클수록 비교해야할 대상이 많아진다.
• 그렇기 때문에 IN/NOT IN절에 상수를 넣는 게 아닌 이상 JOIN을 쓰는 것을 추천한다.

EXISTS/NOT EXISTS

EXISTS

SELECT *
 FROM EMPLOYEE A
 WHERE EXISTS (SELECT 'X'
                FROM DEPT B
                WHERE A.DEPT_CD = B.DEPT_CD);          
                
SELECT *
 FROM DEPT A
 WHERE EXISTS (SELECT 'X'
                FROM EMPLOYEE B
                WHERE A.DEPT_CD = B.DEPT_CD);    

• 드라이빙 테이블이 EMPLOYEE인 결과
  

• 드라이빙 테이블이 DEPT인 결과
  

• 서브쿼리에 조회되는 값만 나온 것을 알 수 있다.

NOT EXISTS

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

• 드라이빙 테이블이 EMPLOYEE인 결과

• 드라이빙 테이블이 DEPT인 결과
  

• 서브쿼리에 조회되지않는 값만 나온 것을 알 수 있다.

정리

• EXISTS/NOT EXISTS의 경우 IN구문과 달리 메인 쿼리에 접근해서 행을 하나 가져오고 EXISTS의 서브쿼리를 실행시킨다.
• 쉽게 말하면 메인쿼리 -> 서브쿼리인 셈이다.
• IN/NOT IN처럼 실행방향이 반대인데, 가장 다른 것은 EXISTS/NOT EXISTS은 결과가 존재하는지 아닌지만 체크하고 만약 EXISTS의 경우 존재한다면 그 다음은 행은 체크하징않고 true를 반환한다. 또, NOT EXISTS의 경우는 그 행이 존재할 경우 true를 반환하고 그 다음 행은 체크하지 않는다.
• 또, IN의 경우 상수 값을 넣어 직접 대입할 수 있었지만 EXISTS의 경우는 서브쿼리만 사용할 수있다.

IN/NOT IN vs. EXISTS/NOT EXISTS

• IN 연산자와 EXISTS 연산자의 차이점은 IN 연산자의 경우 무조건 서브쿼리의 모든 행을 검색하여 해당 값(DEPT_CD)을 IN 연산자에 대입하지만 EXISTS은 서브쿼리에 해당하는 값을 1개라도 찾는다면 조회를 멈추고 true를 반환한다.
• 쉽게 정리하면 IN은 끝까지 서브쿼리를 조회하고, EXISTS은 찾을 때까지만 조회한다는 것이다.
• 이러한 특징으로 존재 여부만 따질 땐 EXISTS를 사용하는 것이 효율적이며, 서브쿼리에 조회되는 데이터가 많아질 수록 IN의 성능은 좋지 않을 것이다.

JOIN/(+)

IN, EXISTS의 결과와 같은 SQL

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

-- 2
SELECT A.*
 FROM  DEPT A
     , EMPLOYEE B
 WHERE A.DEPT_CD = B.DEPT_CD;
 
 -- 2-1
SELECT DISTINCT A.*
 FROM  DEPT A
     , EMPLOYEE B
 WHERE A.DEPT_CD = B.DEPT_CD;
 

• 드라이빙 테이블이 EMPLOYEE인 결과
  

• 드라이빙 테이블이 DEPT인 결과
  1)
  

• EMPLOYEE 테이블에 DEPT_CD를 중복으로 가지는 직원이 있기 때문에 중복되는 값이 나올 수 있다.

• 중복된 값을 제거하면 원하는 값을 가질 수 있다. (아래와 같다)

2)

NOT IN, NOT EXISTS의 결과와 같은 SQL

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

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

• 드라이빙 테이블이 EMPLOYEE인 결과
  

• 드라이빙 테이블이 DEPT인 결과
  

생각 정리

• 다양한 방식으로 테이블을 조인할 수는 있지만, 적재적소에 사용할 수 있어야할 것 같다.
• 정해진 상수 값을 가지는 지, 가지지 않는지 체크하고 그 컬럼 값을 가지고 오고 싶다면 IN을 쓰는 게 좋겠다는 생각이 들었다.
• 존재 여부만 따질때는 EXISTS를 쓰는게 좋을 것같다느 생각이 들었다(JOIN과 IN은 컬럼 값까지 다 가져오고 끝까지 조회하므로 성능면에서 좋지 않은 것 같다.)
• JOIN을 가장 많이 애용할 것 같은데, 지금은 컴퓨터의 성능이 많이 좋아지기도 했고 ORALCE 성능 자체도 많이 좋아져서 성능면에서 큰 차이를 보이지 않지만 표준상 테이블을 조인 걸때 JOIN(+)을 사용하는 것을 권장한다고 한다.
• IN과 EXISTS가 직관적으로 보여 잘 읽히지만 그래도... JOIN을 쓰는게 표준된 방법인 것 같다...!(잘 못 조사한거면... 댓글로... 남겨주세요)
• 암튼, 필요한 상황에 맞춰 적절하게 사용하는 것이 가장 중요한 것 같다!