[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반
[수강목록]
44. 집합 연산자와 서브쿼리 - 09. 실습문제 - 1
45. 집합 연산자와 서브쿼리 - 10. 실습문제 - 2
[강의내용]
44. 집합 연산자와 서브쿼리 - 09. 실습문제 - 1
아래의 SQL문은 film 테이블을 2번이나 스캔하고 있다. film 테이블을 한번만 스캔하여
동일한 결과 집합을 구하는 SQL을 작성하라
SELECT FILM_ID, TITLE, RENTAL_RATE
FROM FILM
WHERE RENTAL_RATE > (
SELECT AVG (RENTAL_RATE)
FROM FILM
);
[풀이]
AVG값을 SELECT절에서 바로 출력되도록 해야한다. OVER()를 사용하면 된다.
OVER()를 사용하면 GROUP BY나 서브쿼리를 사용하지 않고도
분석함수나 집계함수 (SUM, MAX, MIN, COUNT, AVG, RANK 등..)를 사용할 수 있다.
디폴트는 OVER()이나, OVER(PARTITION BY 컬럼, ORDER BY, 세부 분할 기준)의 형태로 조건을 설정하는 것도 가능하다.
SELECT FILM_ID, TITLE, RENTAL_RATE
FROM (
SELECT FILM_ID, title, rental_rate,
AVG(A.RENTAL_RATE) OVER() AS AVG_RENTAL_RATE
FROM FILM A
) A
WHERE rental_rate > AVG_RENTAL_RATE;
결과는 동일하다.
45. 집합 연산자와 서브쿼리 - 10. 실습문제 - 2
아래 SQL문은 EXCEPT 연산을 사용하여 재고가 없는 영화를 구하고 있다.
EXCEPT 연산을 사용하지 말고 같은 결과를 도출하라
SELECT FILM_ID, TITLE
FROM FILM
EXCEPT
SELECT DISTINCT INVENTORY.FILM_ID, TITLE
FROM INVENTORY
INNER JOIN FILM ON FILM.FILM_ID = INVENTORY.FILM_ID
ORDER BY TITLE;
[내 답안]
핵심은 전체 film_id에서 inventory 테이블에 존재하는 film_id를 제외하는 것이다.
LEFT JOIN을 통해 inventory.film_id를 추출하고, NULL값(재고가 없는 영화들)을 뽑는 방법을 사용했다.
SELECT f.film_id, f.title
FROM film f
LEFT JOIN (
SELECT DISTINCT film_id
FROM inventory
) AS t1 ON f.film_id = t1.film_id
WHERE t1.film_id IS NULL;
결과는 동일하다.
[모범답안]
NOT EXISTS를 사용하여 재고가 존재하는 집합을 전체 film 집합에서 제외한다.
SELECT a.film_id, a.title
FROM film a
WHERE NOT EXISTS (
SELECT 1
FROM inventory b
WHERE b.film_id = a.film_id
);
결과는 동일하다.
WHERE (NOT) EXISTS (SELECT 1 ...)에서 SELECT 1은 TRUE 값을 의미하며
WHERE (NOT) EXISTS (...) 에서 (...) 안의 내용이 TRUE인 조건이 WHERE 조건이 되는 것을 알아 두자
즉, SELECT 1 이하의 서브쿼리는 특정한 컬럼을 출력하는 것이 목표가 아니라,
WHERE (NOT) EXISTS 형식에 맞추기 위한 목표이다.