exists와 in

sqld를 위한 학습 중 예제에서 발견한 exists와 in에 관련된 내용을 하나 정리하려고 한다.

 

예제

문제 예제 중, 특정 기간 이후 발송된 총 메일의 갯수가 이벤트의 갯수 미만인 유저(이벤트 메일이 하나 이상 누락된 유저)의 정보를 표시하는 쿼리가 있었다.

 

테이블 구조

user 테이블

 

 

이벤트 테이블

 

메일 발송내역 테이블

 

예제를 재현하기 위해 유저, 이벤트 정보, 메일 발송내역 테이블을 생성하고 테스트를 위한 값들을 insert해준 상태이다.

 

 

상황

user_id가 1인 유저의 경우 모든 event에 대한 메시지를 받은 상태이고, 2와 3인 유저는 일부 event에 대한 message가 누락된 상태이다.

 

 

 

문제의 쿼리문과 결과 사진이다.

 

코드 블럭으로 옮겨보면

SELECT a.user_id, a.user_name, a.email FROM t10_user a
WHERE exists(
	SELECT 'x' FROM t10_event b, t10_mail c
	WHERE b.start_date >= '2024-01-01'
    AND b.event_id = c.event_id
    AND a.user_id = c.user_id
    HAVING count(*) < (
    	SELECT count(*) FROM t10_event
        WHERE start_date >='2024-01-01')
    )

 

위와 같다.

 

 

생각해 볼 점

해당 구문에서는 메인쿼리의 user_id를 이용해 서브쿼리 안에서 특정 조건에 부합하는 메일 내역 개수가 이벤트 수보다 더 적은 사람만 x를 반환하고, x가 반환된 유저일 경우 메인쿼리의 select문을 표시해주고 있다.

 

이 경우 메인쿼리의 from 테이블인 t10_user의 row만큼 subquery가 반복되는, 프로그래밍에서 흔히 말하는 2중 for문의 형태를 띄게 된다.

각 user_id를 기준으로 having조건을 달아서 맞는 경우 x를 반환해야 하기 때문이다.

 

만약 subquery를 한 번에 돌려놓은 결과를 바탕으로 해당하는 유저인지를 판별하려면 IN 절로 바꿀 수 있는데, 약간의 변형이 필요하다.

 

SELECT a.user_id, a.user_name, a.email FROM t10_user a
WHERE a.user_id in(
	SELECT c.user_id FROM t10_event b, t10_mail c
	WHERE b.start_date >= '2024-01-01'
    AND b.event_id = c.event_id
    GROUP BY c.user_id
    HAVING count(*) < (
    		SELECT count(*) FROM t10_event
            WHERE start_date >='2024-01-01')
    )

같은 결과를 내는 코드지만, 이렇게 다르게 짤 수도 있다.

 

둘의 실행계획 차이를 보면

 

첫 번째 exists의 경우

NESTED LOOPS 형식으로 동작하는 것을 확인할 수 있다.

 

 

두 번째 in절의 경우는

이렇게 hash join이 뜨는데, 이건 optimizer의 판단에 따라 달라질 수 있을 것 같다.

 

아직 SQLP는 시작도 안했지만, 이렇게 실행 계획을 조금씩 보게 되면서 느낀 것은

결국 SQLP 자격증 취득을 비롯해 SQL 튜닝 공부를 한다는 건 나날이 발전하는 DB의 Optimizer와 경쟁하는 거구나 하는 생각이 들었다.

 

요 근래 개발자에게 ChatGPT보다 더 실력있는 사람이 될 수 있어야 한다고 말하는 것과 일맥상통하는 것 같다.

 

ORM이 대세가 되어가고 있는 현 시장에서 Optimizer와 경쟁하는 것이 개발자로서 올바른 길인지는 잘 모르겠지만, 이미 목표한 부분이니까 열심히 해야겠다. 알아서 손해볼 건 없는 내용인 것 같기도 하다.

 

 

---

다음날 해당 쿼리의 실행 계획을 다시 조회해보았는데, exists의 경우 hash join을 사용하고, in절의 경우 nested loop 안에서 hash로 그룹화되는 것을 확인하였다.

 

해당 조건에서 exists라고 무조건 nested loop방식으로 동작하는 것은 아니고, 현재 db의 내부적인 상태에 따라 optimizer가 실행계획을 바꿔가며 수립하는 것을 알 수 있었다.