[SQL 100제#75-80] ALL, ANY, EXISTS, NOT EXISTS

[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반

[수강목록]

6주차 과제 풀이 (SQL 75~80번 문제)

 

[강의내용]

 

문제75번) 매출을 가장 많이 올린 dvd 고객 이름은? (subquery 활용)

 

서브쿼리를 활용하여 매출이 가장 많은 customer_id를 구하고,

메인쿼리에서 고객 이름을 구한다.

 

SELECT c.first_name, c.last_name, t1.sum 
FROM customer c 
	JOIN (
		SELECT p.customer_id, sum(p.amount) 
		FROM payment p 
		GROUP BY p.customer_id
		ORDER BY sum DESC 
		LIMIT 1
	) AS t1 ON c.customer_id = t1.customer_id;

 

쿼리 결과

 

문제76번) 대여가 한번이라도 된 영화 카테고리 이름을 알려주세요. (Exists 활용)

 

대여 이력이 있는 영화는 rental 테이블에 존재하는 film_id라고 볼 수 있다.

그리고 그 film_id는 해당하는 category_id에 매핑될 것이고 카테고리 테이블에서 그 이름을 찾으면 된다.

 

EXISTS 조건을 쓸때는 WHERE 바로 뒤에 쓰는 점(컬럼 이름을 쓰지 않음),

뒤에 IN을 쓰지 않는 점에 주의한다.

 

SELECT c2."name" 
FROM category c2 
WHERE EXISTS (
	SELECT 1
	FROM rental r 
		JOIN inventory i ON r.inventory_id = i.inventory_id 
		JOIN film_category fc ON i.film_id = fc.film_id
    );

 

rental 테이블과 film_category 테이블을 JOIN하면 렌탈이력이 있고 카테고리도 있는 정보가 남는다.

이러한 정보에는 물론 film_id뿐만 아니라category_id도 포함되어 있기 때문에

여기에 존재하는(EXIST) category 테이블의 정보가 출력된다.

 

쿼리 결과

 

문제77번) 대여가 한번도이라도 된 영화 카테고리 이름을 알려주세요. (Any 활용)

 

76번 문제와 거의 같고 메인 쿼리의 WHERE절 부분만 다르다.

 

SELECT c.name
FROM category c 
WHERE c.category_id = ANY (
	SELECT fc.category_id
	FROM rental r 
		JOIN inventory i ON r.inventory_id = i.inventory_id 
		JOIN film_category fc ON i.film_id = fc.film_id
    );

 

결과도 동일하다.

 

문제78번) 대여가 가장 많이 진행된 카테고리는 무엇인가요? (Any, All 조건 중 하나를 사용)

 

SELECT *
FROM category c 
WHERE c.category_id = ANY (
	SELECT fc.category_id
	FROM rental r 
		JOIN inventory i ON r.inventory_id = i.inventory_id 
		JOIN film_category fc ON i.film_id = fc.film_id
	GROUP BY fc.category_id
	ORDER BY count(r.rental_id)  DESC 
	LIMIT 1);

 

ANY 대신 ALL을 써도 결과는 똑같다.

 

쿼리 결과

 

문제79번) dvd 대여를 제일 많이한 고객 이름은? (subquery 활용)

 

75번 문제와 거의 비슷하다. 쿼리 결과도 같다.

 

SELECT c.customer_id, c.first_name, c.last_name 
FROM customer c 
	JOIN (
		SELECT r.customer_id, count(r.rental_id) 
		FROM rental r
		GROUP BY r.customer_id
		ORDER BY count DESC 
		LIMIT 1
	) AS t1 ON c.customer_id = t1.customer_id;

 

쿼리 결과

 

(매출액이 가장 많은 고객이 가장 많은 대여건수를 올린 것으로 나타난다)

 

문제80번) 영화 카테고리값이 존재하지 않는 영화가 있나요?

 

내 풀이: film 테이블에서 category_id가 NULL값이거나 공백('')인 것이 있는지 조회했다.

 

SELECT *
FROM film f 
	JOIN film_category fc ON f.film_id = fc.film_id 
WHERE fc.category_id IS NULL
OR CAST(fc.category_id AS varchar) = ''

 

쿼리 결과

 

출력되는 값이 없다.

 

모범 답안: film 테이블과 film_category 테이블을 JOIN한 결과에서 존재하지 않는 film 테이블의 정보가 있는지 조회

 

SELECT *
FROM film f 
WHERE NOT EXISTS (
		SELECT *
		FROM film_category fc 
		WHERE fc.film_id = f.film_id
	);

 

쿼리 결과

 

film 테이블과 film_category 테이블의 로우 수가 같은 것으로 나타난다.

 

참고)

 

IN, EXISTS는 함수는 다르나 동일한 결과물을 내는 것이라고 생각해도 된다.
하지만 NOT IN, NOT EXISTS는 NULL 에 의한 차이가 존재한다.

 

in = exists   
not in != not exists (전제조건은 null,  null이 데이터셋에 들어있을때는 같지않음. 단 null이 없으면 같다)
not in + null = not exists