업무중 이전 분석 자료들을 찾아보다가 exists 구문을 알게되었다.
전체 앱로그 유저 중에서 table a에 특정 기간동안에 기록된 유저를 제외하는 목적으로 NOT EXISTS 가 쓰이고 있었다. 언뜻 이해했을 때는 NOT IN 과 유사해보였으나 다른 부분이 있었다. exists, not exists 는 언제 쓰이는지 예시를 확인하고 in, not in 과 무엇이 다른지 알아보았다.
Exists?
exists의 기본적인 형태는 다음과 같다.
-- table_1 중에서 table_2 에도 있는 row만 추출하기
SELECT
column1
FROM
table_1
WHERE -- 메인쿼리 조건문
EXISTS ( SELECT
1
FROM
table_2
WHERE
column_2 = table_1.column_1);exists 뒤에 서브쿼리가 따라오는 구조다. 서브쿼리의 조건문에는 메인쿼리의 table_1.column 도 포함된다. 보통 이런 구조를 correlated subquery라 부른다고 한다.
exists 뒤 서브쿼리에서 적어도 하나의 행이 리턴되면, 메인쿼리의 조건문은 true을 반환하고, 서브쿼리에서 어떠한 행도 리턴하지 못하면 메인쿼리 조건문은 false를 리턴한다.
이 때 주의할 것이, 서브쿼리가 NULL을 리턴하더라도 메인쿼리의 조건문 결과는 true이라는 것이다. (NULL이라도 행을 리턴하긴 했으므로)
참고로 서브쿼리의 select 문은 어떠한 형태라도 상관이 없다. (행이 하나라도 존재하는지 여부만 중요)
아래 세 가지 쿼리의 결과는 동일하다.
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);SELECT column1 FROM t1 WHERE EXISTS (SELECT 1 FROM t2);SELECT column1 FROM t1 WHERE EXISTS (SELECT column1 FROM t2);
사용 예시
총 두가지 테이블이 있다고 하자.
- customer 테이블: 고객 id, 고객 이름
- payment 테이블 : 구매 id, 고객 id, 수량
전체 고객 중 10개 이상의 수량을 구매해본 이력이 있는 고객을 찾는다고 가정하자.
추출하는 방법은 여러가지가 가능하다. 고객 정보 테이블을 기준으로 구매 이력 테이블을 left join 하는 방법도 있고, 아래처럼 exists를 쓸 수도 있다.
SELECT name
FROM customer c
WHERE EXISTS
(SELECT 1
FROM payment p
WHERE p.customer_id = c.customer_id -- payment 테이블에 기록된 고객인가
AND amount >= 10 ) -- 그리고 이 때 구매 수량이 10개 이상인가
ORDER BY name;메인쿼리에서 customer 테이블의 각 name 마다, 서브쿼리 결과가 true인지, false 인지 여부를 조회한다.
반대로 수량을 10개 이상으로 구매해본적 없는 고객을 추출하려면? 아래와 같이 not exists 를 이용하여 찾을 수 있다.
SELECT name
FROM customer c
WHERE NOT EXISTS -- 메인쿼리 조건문
(SELECT 1
FROM payment p
WHERE p.customer_id = c.customer_id
AND amount >= 10 )
ORDER BY name;- 만약 서브쿼리가 어떠한 row도 리턴하지 않는다면? 메인쿼리의 조건문(where not exists ~)은 true를 리턴한다.
not exists와 not in의 차이점
아래 쿼리처럼 서브쿼리가 NULL을 리턴하면 exists는 true를 리턴한다.
-- 아래 쿼리의 결과는 select name from customer 와 동일하다.
SELECT
name
FROM
customer
WHERE
EXISTS( SELECT NULL )
;
서브쿼리에 NULL이 리턴되는 상황에서 NOT IN vs NOT EXISTS 사이 차이가 발생한다.
예를 들어 아래와 같은 테이블이 있다고 하자.
with customer as (
select 100 as customer_id, 'a' as name
union all
select 200 as customer_id, 'b' as name
union all
select 300 as customer_id, 'c' as name
), payment as (
select 100 as customer_id, 15 as amount
union all
select 300 as customer_id, 7 as amount
union all
select NULL as customer_id, 10 as amount -- customer_id NULL 존재
)customer 테이블에 기록된 고객 중 구매 기록이 없는 고객의 customer_id를 찾고자 한다. 구매기록은 payment 테이블에 기록되어 있다.
customer
- customer_id
- name
payment
- customer_id
- amount
간단하게 NOT IN을 쓰면 되지 않을까?
-- 1) not in 을 사용한 경우
select name
from customer
where customer_id not in (
select customer_id
from payment
);그런데 위 쿼리를 실행하면 아무런 행도 추출되지 않는다.
payment 테이블의 customer_id 에는 NULL값이 포함되어 있기 때문이다. NOT IN 이하 서브쿼리가 먼저 실행되는데 서브퀴리 결과물 중 NULL이 포함되어 있다. 그래서 NOT IN (SELECT NULL)의 조건문의 결과 0개의 행이 추출된다.
-- 2) not exists 를 사용한 경우
select name
from customer
where not exists (
select 1
from payment
where payment.customer_id = customer.customer_id
);반면 not exists 를 사용한 경우, name='b' 인 행이 추출된다.
not exists는 customer 테이블의 customer_id마다 payment 테이블에 존재하는지 여부를 조회한다. 따라서 payment.customer_id에 NULL이 있든 없든 동일한 결과가 추출된다.
참고 자료 :
