[Database]/SQL

[SQL - Join (4)] SQL의 EXISTS문 (Semi Join, Anti Join)

quokkalover 2022. 4. 4. 20:49

현재 SQL의 조인 시리즈를 작성하고 있다. Join시리즈의 목차를 참고하려면 이 글을 참고하길 바란다.

 

본 글은 SQL의 EXISTS문에 대해 다룬다.

 

 

EXISTS문이란?

SQL에서 EXIST문은 WHERE절에서 사용되며 조건에 따라 데이터를 걸러내어 결과를 조회할 때 사용한다. JOIN관점에서는 Semi Join과 Anti Join에 사용된다.

 

EXIST문은 예시만 보면 쉽다가도, outer table과 subquery의 관계를 이해하는 관점에서는 매우 헷갈리는 개념이라, 예시와 함께 정리해보려고 한다.

 

EXISTS / IN 개념 비교

EXIST와 IN은 WHERE절에 사용되며 조건에 따라 데이터를 걸러내어 결과를 조회할 때 사용되는 공통점을 가지고 있다. 본 글에서는 EXISTS와 IN을 간단하게 비교하고 EXIST문에 집중한다.

 

EXISTS

  • 한 테이블이 다른 테이블과 외래키와 같은 관계가 있을 때 유용하다.
  • 조건에 해당하는 ROW의 존재 유무를 확인하고나면 연산을 더 수행하지 않는다.
  • 일반적으로 SELECT절 까지 가지 않기에 동일한 결과를 보여주는 IN에 비해 속도나 성능면에서 더 좋다.
  • 연산 순서 : 메인 쿼리 → EXISTS 쿼리

IN

  • 조건에 해당하는 ROW의 컬럼 비교하여 체크한다
  • SELECT절에서 조회한 컬럼 값으로 비교하기 때문에 EXISTS에 비해 성능 떨어진다.
  • 연산 순서 : IN 쿼리 → 메인 쿼리

 

EXISTS 동작 방식

  • EXIST문의 동작 방식은 서브 쿼리의 결과가 “한 건이라도 존재하면" TRUE, 없으면 FALSE를 리턴한다는 점이다.
  • EXISTS는 서브 쿼리에 일치하는 결과가 한 건이라도 있으면 쿼리를 더이상 수행하지 않는다.

EXISTS문에서 필자가 제일 헷갈렸던 부분은 Outer 쿼리와 inner subquery와의 관계가 어떻게 되냐는 점이었다.

예를 들어 아래와 같은 쿼리에서

SELECT *
  FROM suppliers
 WHERE EXISTS (select *
                 from products
                where suppliers.supplier_id = products.supplier_id);

suppliers테이블에서의 suppliers_id와 products 테이블에서의 supplier_id가 매칭하면 true를 리턴하는게 하나라도 있는 경우 해당 열을 출력한다는 점은 이해한다.

 

하지만 subquery가 어떻게 suppliers와 연관이 지어지냐는 점이었다. pk가 있는 경우만 확인하나? 뭐 이런 생각을 했었는데, 이 헷갈리는 포인트를 이해하기 위해서는 아래 두 가지 포인트를 이해해야 한다.

 

1) exists문은 WHERE절에서 동작하면서 각 row 당 한번씩 수행된다

2) outer table의 특정 column과 inner subquery의 특정 column을 비교하는 형태로 가져가야 기대하는 대로 동작한다. outer table과 무관한 column을 사용해 연산을 하게 되면 자칫하면 모두 true가 리턴될 수 있다.

 

위 내용을 글만으로 이해가 가지 않는다면, 아래 설명할 예시들로 디테일하게 다룰 것이니 걱정하지 않아도 된다.

 

EXISTS문 Syntax

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

 

EXISTS 예시

EXISTS문의 예시로 어떤 데이터가 있는지도 직접 확인해볼 수 있고, 쿼리문도 변형해가면서 웹에서 실행해볼 수 있다는 점에서 매우 유용한 w3schools.com/sql/sql_exists.asp에 있는 예시에 집중하고자 한다.

직접 SELECT쿼리를 통해 데이터를 확인해볼 수 있지만 일단 스크린샷으로 간단하게 데이터를 소개하겠다.

  • Products테이블의 데이터 총 갯수 : 77개
  • Suppliers테이블의 총 갯수 : 29

페이지에서

위에 표시한 빨간 버튼을 누르면 실제로 쿼리를 실행해볼 수 있고, 직접 쿼리를 변형해서도 실행해볼 수 있으니, 본 글에서 소개하는 예제 말고도 여러 예제를 한번 살펴보자.

 

Products테이블에 등록된 Supplier의 이름만 보고싶을 경우

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Suppliers.SupplierID == Products.SupplierID);

Products테이블에 모든 Supplier가 등록돼있기 때문에,

모든 레코드가 리턴된다.

 

 

상품 가격 20가 넘어가는 Supplier의 이름을 받고 싶은 경우

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products 
              WHERE Suppliers.SupplierID == Products.SupplierID
              AND Products.price > 20);

 

 

NOT EXISTS 응용 : 상품 가격이 20가 넘어가지 않는 Supplier의 이름을 받고 싶은 경우

 

위 쿼리에서 NOT만 붙이면 됨

SELECT SupplierName
FROM Suppliers
WHERE NOT EXISTS (SELECT ProductName FROM Products 
              WHERE Suppliers.SupplierID == Products.SupplierID
              AND Products.price > 20);

 

전체 상품 갯수는 29개였고 그 반대이기 때문에 8개가 리턴된다.

 

Outer table과 inner subquery의 테이블이 서로 아무런 연관이 없는 경우

 

이런 경우에는 우리가 EXISTS문으로부터 기대하는 결과를 얻을 수 없다.

아래 쿼리 예제를 잘 살펴보자.

SELECT SupplierName
FROM Suppliers;
------
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.Price > 0);

위 쿼리를 보면 Suppliers에는 Price라는 열이 없다. 따라서, Price가 0보다 큰 Product가 하나라도 있으면 Suplliers 테이블의 모든 열에 대해 True가 리턴되면서 모든 행들이 출력되게 된다.

 

물론 1개도 없는 Price의 조건을 넣게 할 경우에는, 모두 False가 리턴되기 때문에 아무 값도 리턴되지 않는다

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.Price < 0);

 

 

EXISTS문을 활용한 Join Query

Semi Join

세미조인은 서브 쿼리를 사용하여 서브 쿼리의 결과에 존재하는 데이터만 메인 쿼리에서 추출하는 조인 방법이다.

IN과 EXISTS연산자가 사용되며, 위에서 살펴봤던 예시에도 이미 설명한 바가 있다.

Products테이블에서 상품 가격 22가 넘어가는 Supplier의 이름을 받고 싶은 경우에는 아래와 같은 Semi-Join쿼리를 실행할 수 있다.

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);

 

Anti Join (안티 조인)

 

어떤 테이블 A에는 존재하지만, 다른 테이블 B에는 존재하지 않는 값을 선택하는 join의 종류를 anti-join이라고 한다. 테이블과 벤다이어그램을 비교하는 것이 정확하지는 않지만, 어렴풋이 차집합과 비슷한 개념이라고 생각해도 괜찮다.

 

Anti join은 생각보다 사용할 일이 자주 발생한다.

 

예를 들어 post를 가져오는데, 내가 차단한 유저의 post를 가져오지 않는 경우, 차단 테이블에 없는 유저의 post를 가져와야 하는데, 이 때 사용할 수 있는 것이 anti-join이다.

 

Anti join의 두 가지 방법

left outer join & is null

  • userblock과 left join을 하게 되면 userblock에 없는 user_id에 대해서는 null값이 처리되기 때문에 null인 것만 출력할 수 있다.
select * 
from user left join userblock
on (user.id = userblock.user_id)
where userblock.user_id is null;

not exists

select user.id
from user 
where not exists 
( select user_id
  from user_block
  where user_block.user_id = user.id
)

위와 같이 수행할 경우에도 Anti join을 할 수 있다.

 

 

본 글에서는 EXISTS문과 Semi Join, Anti Join에 대해서 다루었다. 

 

이제는 Join할 때 필자가 꼭 사용하는 Aliasing에 대해 알아보도록 하겠다.

 

 

 

 

참고자료

https://juneyr.dev/anti-join

https://www.w3schools.com/sql/sql_exists.asp

http://wiki.gurubee.net/pages/viewpage.action?pageId=1508124

저작자표시 비영리 변경금지