SQL 문제: 2005년 5월 29일 ~ 30일 사이에 반납된 영화의 아이디와 이름을 구하시오.

테이블 구조는 아래와 같았다. 각기 모든 테이블은 기본키를 가지고 있고,

필름 - 인벤토리(중간테이블) - 렌탈

인벤토리는 필름과 렌탈을 이어주는 중간테이블이다. (N:N관계를 만들어줌)

1. 테이블을 모두 JOIN 후 GROUP BY로 중복제거

처음 내가 낸 답은 정직하게 모든 테이블을 조인하고 WHERE 조건을 적용하는 것이었다. 그런데 한 영화가 2005년 5월 29~30일 사이에 여러번 대여되고 반납된 경우가 있어서 여러번 나올 때도 있었다.

SELECT film.film_id, title FROM rental
INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
INNER JOIN film ON inventory.film_id = film.film_id
WHERE return_date >= '2005-05-29' AND return_date < '2005-05-31'

횟수 상관없이 고유한 영화 이름만 나오면 되는 상황이라, 나는 추가적으로 GROUP BY를 통해 '같은 이름에 같은 아이디'인 데이터는 하나로 묶이도록 만들어서 중복제거를 하였다.

SELECT film.film_id, title FROM rental
INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
INNER JOIN film ON inventory.film_id = film.film_id
WHERE return_date >= '2005-05-29' AND return_date < '2005-05-31'
GROUP BY film.film_id, title
ORDER BY film_id

2. DISTINCT

그런데 중복제거를 위해 할 수 있는 방법은 그룹바이 말고도 아래처럼 DISTINCT를 이용하는 방법도 있다.

SELECT DISTINCT film.film_id, title
FROM rental
INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
INNER JOIN film ON inventory.film_id = film.film_id
WHERE return_date >= '2005-05-29' AND return_date < '2005-05-31'
ORDER BY film.film_id

나는 DISTINCT는 각 컬럼에 한 번씩 적용되는걸로 착각하고 있었는데, SELECT에 쓰인 모든 컬럼을 포함하여 고유한 행만 선택한다. 고유한 복합키(?)를 만드는 느낌이라 이해했다. 아주 적확한 표현은 아닌거 같지만.

3. IN 서브쿼리

그리고 마지막으로 서브쿼리를 이용하는 방법도 있었다.

아래 쿼리는 서브쿼리로 인벤토리와 렌탈 테이블을 조인하여 2005년 5월 29~30일에 반납된 영화들의 film_id를 일차적으로 뽑아내고, 그 영화 아이디에 해당하는 영화 타이틀을 뽑아내는 SQL문이다.

서브쿼리에서 반환된 값에서 중복제거하는 명령어는 없는데 괜찮을까? 라고 생각한다면, 괜찮다고 말할 수 있다.

SELECT film_id, title FROM film
WHERE film_id IN
(SELECT inventory.film_id FROM rental
INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
WHERE return_date BETWEEN '2005-05-29' AND '2005-05-31')
ORDER BY film_id

서브쿼리 내 중복이 존재한다 해도 마지막 SELECT문에선 조건에 해당하는거 한 번만 가져오기 때문이다.

WHERE film_id IN (1, 1, 1, 2, 2, 3)이어도 상관없어진다.

4. WHERE EXISTS

아예 서브쿼리 예제 중에 WHERE EXISTS문을 사용해서 찾을수도 있다.

WHERE EXISTS는 해당하는 것을 모두 조건으로 사용하는 기능으로, JS의 Array.prototype.filter와 작동방식은 같다고 보면 된다.

(아래 예시에선 조건에 해당하기만 하면 무엇을 셀렉하든 상관없어서 상수 1을 그냥 반환하게 했다.)

(반면 EXISTS는 JS의 Array.prototype.some과 비슷하게, 해당되는 조건이 하나라도 있으면 true를 반환하는 기능이다.)

SELECT film_id, title
FROM film f
WHERE EXISTS (
SELECT 1
FROM rental r
INNER JOIN inventory i ON r.inventory_id = i.inventory_id
WHERE i.film_id = f.film_id
AND r.return_date >= '2005-05-29'
AND r.return_date < '2005-05-31'
)

성능 상으로는 데이터의 규모나 인덱스 여부에 따라 다르지만

WHERE EXISTS나 DISTINCT를 활용한 1, 4번이 가장 효율적이고

IN을 활용한 2번이 그 다음, GROUP BY를 사용한 것이 가장 비효율적이라 한다.

DISTINCT와 WHERE EXISTS 둘 다 집계 자체가 가장 빠르기도 한데, DISTINCT 쪽이 인덱스만 제대로 설정되어있다면 보통 가장 빠르다고 한다. IN은 무난하게 보통의 성능을 보여주는 편이고, GROUP BY는 집계 후 다시 카테고라이징 하는 비용이 들기 때문에 가장 안 좋다고 한다.