SQL 11일차

수강날짜 2021-08-20 (2021-08-02)

강의목록
강의목록
강의목록

서브쿼리

서브쿼리란?
서브쿼리는 SQL문 내에서 메인 쿼리가 아닌 하위에 존재하는 쿼리를 말한다.
서브쿼리를 활용함으로써 다양한 결과를 도출 할 수 있다.

SELECT
	AVG(RENTAL_RATE)
  FROM
   	FILM;

RENTAL 요금의 평균을 구했다.

궁금한 점이 생겼다. RENTAL_RATE의 평균보다 큰 RENTAL_RATE집합을 구하고 싶다! 어떻게 하지?

이미 평균은 위에 구했다. 그러면 저걸 이용해서 할 수는 없을까?

SELECT
	AVG(RENTAL_RATE)
      , TITLE
      , RENTAL_RATE
 FROM FILM
WHERE RENTAL_RATE > (2.98)

이런 방식으로 답을 넣어서 할 수는 없는 노릇이다. 그렇다면 어떻게 할까?

SELECT를 통해 구한 결과를 이용하여 결과를 내는 것 그것이 서브 쿼리이다.

실습
중첩 서브 쿼리의 활용

SELECT f.film_id, f.title, f.rental_rate 
  FROM film f 
 WHERE rental_rate > (
 		SELECT AVG(rental_rate)
 		  FROM film
)

다음과 같이 쿼리를 통해 값을 추출한 값을 다시 쿼리에 적는다. 이렇게 쿼리 안에 쿼리르 적는 것을 인라인 뷰라고 한다.

소괄호를 통해 인라인 뷰의 가시성을 높인다.

실습
인라인 뷰의 활용

SELECT
		  A.film_id
		, A.title
		, A.rental_rate 
  FROM 
  		  film A
     	, (
     		SELECT
     			   AVG(rental_rate) AS AVG_RENTAL_RATE
     		  FROM film
     	) B
 WHERE A.rental_rate > B.AVG_rental_rate 
 		

실습
스칼라 서브 쿼리의 활용

SELECT
		  A.FILM_ID
		, A.TITLE
		, A.RENTAL_RATE
  FROM
(
	SELECT 
		  A.FILM_ID
		, A.TITLE
		, A.RENTAL_RATE			
        , (					--스칼라 서브쿼리의 시작
		SELECT AVG(L.RENTAL_RATE)
		  FROM film L
          ) AS AVG_RENTAL_RATE			--스칼라 서브쿼리의 종료
	  FROM film A
) A						--인라인뷰의 종료
WHERE A.RENTAL_RATE > A.AVG_RENTAL_RATE

스칼라 서브쿼리린
SELECT문에서 사용하는 서브쿼리로 1행만 반환

이렇게 여러개의 테이블을 이용하여 한가지 결과를 도출하는 방법으로는

1. 중첩 서브 쿼리
2. 인라인 뷰
3. 스칼라 쿼리

이 중에 어떤 것이 좋은지에 대해서는 이야기를 단정 지을 수 없다. 그때 그때 상황에 따라 달라진다. 그렇다고 해도 어느연산이 빠른지를 계산하는 능력을 키우는 것도 좋다.

연산을 줄이는 것이 좋다! 값들을 작게 쪼개서 사용하는 것이 좋다!

ANY 연산자

ANY 연산자는 값을 서브 ㅁ쿼리에 의해 반환된 값 집합과 비교한다. ANY 연산자느 서브쿼리의 값이 어떠한 값이라도 만족을 하면 조건이 성립된다.

ANY 연산자 실습
영화분류별 상영시간이 가장 긴 영화의 제목 및 상영시간을 구하라.

SELECT title, length 
  FROM film 
 WHERE length >= ANY 
 (
	SELECT MAX(f.length)
	  FROM film f
	 INNER JOIN film_category fc ON f.film_id = fc.film_id
	 GROUP BY fc.category_id
 )

근데 이렇게 코딩을 짜게 되면.... 영화 분류별 상영시간이 가장 긴 영화의 시간 중에 가장 작은 값보다 크거나 같은 경우를 모으는 것이기 때문에 질문의 요지와 다르지 않는가?
의문이 생긴다.

진짜 원하는 질문을 만들면 다음과 같지 않을까?

SELECT f.title, f.length, fc.category_id 
  FROM film f, film_category fc,  (
		 SELECT MAX(f.length) AS length, fc.category_id 
		  FROM film f
		 INNER JOIN film_category fc ON f.film_id = fc.film_id
		 GROUP BY fc.category_id
) A
 WHERE f.film_id = fc.film_id 
   AND f.length = A.length
   AND fc.category_id = A.category_id
 ORDER BY f.title ```

뭔가 테이블의 값을 비교하는 부분에서 의문점이 생겼다. 이것보다 더 좋은 것이 있지 않을까?
인라인 뷰를 통해 문제를 해결하였지만 인라인 뷰가 아닌 중첩 서브쿼리를 이용하여 문제를 해결하는 것이 더 좋아 보이는데. 아직 공부가 부족해서 이렇게 처리를 하겠다.

ANY 연산자는 값을 어떠한 것이라도 만족할 경우를 찾는다.

그렇다면 같은 경우를 찾는 경우는 어떻게 될까?

SELECT title, length 
  FROM film 
 WHERE length = ANY 
 (
	SELECT MAX(f.length)
	  FROM film f
	 INNER JOIN film_category fc ON f.film_id = fc.film_id
	 GROUP BY fc.category_id
 )

다음과 같이 괄호 안에 있는 질의의 결과가 같은게 있는 것들을 찾아서 하나라도 있으면 그것을 추출한다. 이것은 IN 연산자와 같은 기능을 수행한다.

SELECT title, length 
  FROM film 
 WHERE length IN 
 (
	SELECT MAX(f.length)
	  FROM film f
	 INNER JOIN film_category fc ON f.film_id = fc.film_id
	 GROUP BY fc.category_id
 )

결과가 같은 것을 확인할 수 있다.

'=ANY'는 'IN'과 동일

ALL연산자

ALL 연산자는 값을 서브 쿼리에 의해 반환된 값 집합과 비교한다. ALL 연산자는 서브 쿼리의 모든 값이 만족을 해야만 조건이 성립된다.

ANY의 경우 모든 경우와 OR연산을 하여 TRUE인 경우를 선택한다.
AND의 경우 모든 경우와 AND연산을 하여 TRUE인 경우를 선택한다.

<실습>
영화분류별 상영시간이 가장 긴 양화의 모든 사영시간 보다 크거나 같아야만 조건 성립되는 경우를 보여라

SELECT title, length 
  FROM film 
 WHERE length >= ALL
 (
	SELECT MAX(f.length)
	  FROM film f
	 INNER JOIN film_category fc ON f.film_id = fc.film_id
	 GROUP BY fc.category_id
 )

위에 ANY연산 대신에 ALL연산을 적었다.
결과를 보면 다음과 같다.

• 이것을 보면 가장 긴 영화의 상영시간은 185임을 알 수 있다.
  그리고 185와 영화시간이 크거나 같은 영화들이 출력된 것을 볼 수 있다.

  조건을 모두 만족하는 것을 찾는 경우이다.

  <실습>
  평가 기준을로 영화 상영 길이의 평균 값을 구하고 그것을 평균보다 상영시간이 긴 영화를 출력한다.

SELECT f.film_id 
	 , f.title 
	 , f.length 
  FROM film f
 WHERE length > ALL (
	 SELECT ROUND(AVG (length),2)
	   FROM film f 
	  GROUP BY rating
 )

• 인쿼리를 출력하면 다음과 같다.

  SELECT f.rating, ROUND(AVG (length),2)
   FROM film f 
  GROUP BY rating

  

이 모든 값중 가장 큰 값은 120.44이기 때문에 이 값보다 큰 값을 모두 출력한다.

EXISTS 연산자

EXIST 연산자는 서브쿼리 내에 집합이 존재하는지 존재 여부만을 판단한다. 존재 여부만을 판단하므로 연산 시 부하가 줄어든다.

EXISTS 연산자 실습

고객 중에서 지불내역이 11달러 초과한 고객이 있는지 확인해라

 SELECT c.first_name, c.last_name 
   FROM customer c 
  WHERE 
 EXISTS ( SELECT 1
    	    FROM payment p
 	   WHERE p.customer_id = c.customer_id
   	     AND p.amount > 11
 )
 ORDER BY first_name, last_name;

해당 집합이 존재하기만 하면 더 이상 연산을 멈추기 때문에 성능상 유리하다.

근데 서브쿼리가 신기하였다.

SELECT 1
  FROM payment p
 WHERE p.customer_id = c.customer_id
   AND p.amount > 11

이거는 무엇을 출력하게 되는지 궁금해졌다.

SELECT 1
  FROM payment p
 WHERE p.amount > 11

다음을 출력하였다.

11이 넘는 애들은 전부 1로 아닌 경우는 존재하지 않는다.
이걸 통해 exists는 값이 무엇인지를 보는게 아니라 존재 여부를 본다는 것을 알 수 있었다.

그렇다면 존재하지 않는 경우에 대해서도 물어볼 수 있지 않을까?

NOT EXISTS 연산자

<실습>
고객 중에서 지불 내역이 11달러 초과한 적이 없는 고객이 있는지 확인

 SELECT c.first_name, c.last_name 
   FROM customer c 
  WHERE 
 NOT EXISTS ( SELECT 1
 			FROM payment p
 		   WHERE p.customer_id = c.customer_id
 		     AND p.amount > 11
 )
 ORDER BY first_name, last_name;

해당 집합이 존재하지않기만 하면 더 이상 연산을 멈추므로 성능상 유리함

실습문제 1

아래 SQL문은 FILM 테이블을 2번이나 스캔하고 있다. FILM 테이블을 한 번만 SCAN하여 동일한 결과 집합을 구하는 SQL을 작성하라.

SELECT f.film_id, f.title, f.rental_rate 
  FROM film f
 WHERE rental_rate > 
 (
		SELECT AVG(f2.rental_rate)
		  FROM film f2 
 )

2번의 스캔을 1번으로 줄이는 것이 문제이다.
WHERE절만 바꿔주면 문제가 해결이 될 것이다.

SELECT  A.film_id
	  , A.title
	  , A.rental_rate
  FROM (
		SELECT  f.film_id
			  , f.title
			  , f.rental_rate
			  , AVG(f.rental_rate) OVER() AS AVG_RENTAL_RATE
  	      FROM film f) A
 WHERE rental_rate > AVG_RENTAL_RATE

실습 문제 2

아래 SQL문은 EXCEPT 연산을 사용하여 재고가 없는 영화를 구하고 있다. 해당 SQL문은 EXCEPT연산을 사용하지 말고 같은 결과를 도출하라.

SELECT f.film_id, f.title 
  FROM film f 
EXCEPT
SELECT DISTINCT f2.film_id, f2.title 
  FROM inventory i
 INNER JOIN film f2
 ON f2.film_id = i.film_id
 ORDER BY f.title;

이에 대해 NOT EXIST로 문제를 풀수 있다고 생각하여 한 번 해보았다.

SELECT f.film_id, f.title 
 FROM film f 
WHERE NOT EXISTS (
	SELECT 1
	  FROM inventory i
	 WHERE i.film_id = f.film_id 
)
ORDER BY f.title;

EXCEPT 연산을 NOT EXISTS로 해결하였는데 이렇게 하면 더욱 효율적이고 조회가 적다!

생각대로 SQL-6

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

SELECT c.first_name, c.last_name 
  FROM customer c 
 WHERE c.customer_id in (
 		SELECT p.customer_id 
 		  FROM payment p
 		 GROUP BY p.customer_id
 		 ORDER BY SUM(amount) DESC
 		 LIMIT 1
 )

문제2번) 대여가 한번도이라도 된 영화 카테 고리 이름을 알려주세요. (쿼리는, Exists조건을 이용하여 풀어봅시다)

SELECT DISTINCT c.name
  FROM film_category fc
 INNER JOIN category c ON fc.category_id =c.category_id 
 WHERE EXISTS (
 		SELECT DISTINCT i.film_id 
		  FROM rental r 
		 INNER JOIN inventory i 
		    ON r.inventory_id = i.inventory_id
		 WHERE i.film_id = fc.film_id 
 )
 ORDER BY c."name" 

문제3번) 대여가 한번도이라도 된 영화 카테 고리 이름을 알려주세요. (쿼리는, Any 조건을 이용하여 풀어봅시다)

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

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

SELECT c."name"
  FROM category c 
 INNER JOIN (SELECT fc.category_id, count(*) AS cn
			   FROM rental r
			  INNER JOIN inventory i ON i.inventory_id = r.inventory_id
			  INNER JOIN film f ON f.film_id = i.film_id
			  INNER JOIN film_category fc ON fc.film_id = f.film_id
			  GROUP BY fc.category_id
			 ) A ON c.category_id = A.category_id
 WHERE A.cn >= ALL(SELECT count(*)						
					  FROM rental r
					 INNER JOIN inventory i ON i.inventory_id = r.inventory_id
					 INNER JOIN film f ON f.film_id = i.film_id
					 INNER JOIN film_category fc ON fc.film_id = f.film_id
 					 GROUP BY fc.category_id)

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

SELECT c.first_name, c.last_name 
  FROM customer c 
 WHERE c.customer_id =(SELECT r.customer_id
					     FROM rental r
					    GROUP BY customer_id
					    ORDER BY count(*) desc 
					    LIMIT 1) 

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

SELECT f.title 
  FROM film f
 WHERE NOT EXISTS (
 		SELECT 1
 		  FROM film_category fc 
 		 WHERE f.film_id = fc.film_id 
 )

카테고리가 NULL인 경우는 존재하지 않는다. 한 번 더 확인을 해보자

SELECT DISTINCT c."name" 
  FROM film f 
 INNER JOIN film_category fc ON f.film_id = fc.film_id
 INNER JOIN category c ON fc.category_id = c.category_id 

name이 NULL인 경우가 없는 것을 보아 이게 정답인 것같다.