9장 서브쿼리

서브쿼리를 사용해서 데이터를 필터링하고 값을 생성하며 임시 데이터셋을 구성하는 방법을 알아보자.

9.1 서브쿼리

다른 SQL 구문 ( 포함 구문 )에 포함된 쿼리
서브쿼리는 항상 괄호 안에 들어가며, 일반적으로 포함 구문보다 먼저 실행된다.
다른 쿼리와 마찬가지로 서브쿼리는 다음 요소를 포함한 결과를 반환한다.

단일열을 가진 단일 행
다중 행
다중열을 가진 다중 행

서브쿼리가 반환한 데이터를 처리할 때 포함 구문이 사용할 연산자를 결정한다.

서브쿼리는 구문 범위가 있는 임시테이블처럼 동작하므로, 포함 구문 실행이 완료되면 서브쿼리가 반환한 데이터는 폐기됩니다.

9.2 서브쿼리의 유형

완전 독립적으로 실행되는 비상관 서브쿼리

다른 서브쿼리는 포함 구문의 열을 참조하는 상관 서브쿼리

9.3 비상관 서브쿼리

단독으로 실행될 수 있으며 포함 구문에서 아무것도 참조하지 않습니다.
update나 delete 문을 사용하지 않는 한, 이러한 유형의 서브쿼리를 가장 많이 사용할 것임.

일반적인 연산자 (=, <>, <, >, <=, >=)

India가 포함되지 않은 국가id 반환하는 서브쿼리

9.3.1 단일 열을 가진 다중 행을 반환하는 서브쿼리

서브쿼리가 두개 이상의 행을 반환할 경우, 이전 예제처럼 동등조건을 사용할 수 없음.
4개의 추가 연산자가 있다.

in 연산자와 not in 연산자

하나의 값을 값 집합과 동등하게 비교할 수는 없지만, 값 집합 내에서 하나의 값을 찾을 수 있는지 여부를 확인 할 수 있다.

두 개의 동등조건을 사용해서 같은 결과를 얻을 수 있다.
만약, 수십개라면 in 연산자를 사용하는 편이 더 바람직 하다.

가끔 조건의 한쪽에 사용할 문자열, 날짜 또는 숫자 집합을 만들기도 하지만, 하나 이상의 행을 반환하는 서브쿼리로 집합을 생성할 가능성이 더 크다.

all 연산자

all 연산자를 사용하면 한 집합의 모든 값과 하나의 값을 비교할 수 있다.
all 연산자와 함께 비교연산자(=, <>, <, > 등)을 사용해야 한다.

무료 영화를 대여한 적이 없는 모든 고객을 찾는 쿼리

이 방식이 어색하다면, 당신은 좋은 업체에서 일하는 셈입니다 (!?)
대부분의 사람들은 all 연산자의 사용을 선호하지 않습니다.

not in을 사용한 이전 쿼리와 동일한 결과 생성

not in 또는 all을 사용해 하나의 값을 집합 내의 값들과 비교할 경우
집합에 null값을 포함하지 않도록 주의해야 한다!

null과 비교하면 unknown이 발생하기 때문!

having절에 서브쿼리가 있는 예제
서브쿼리는 북미의 모든 고객에 대한 총 영화 대여횟수를 반환하고, 포함쿼리는 총 영화 대여횟수가 북미 고객의 대여 횟수를 초과하는 모든 고객을 반환.

any 연산자

서브쿼리: 볼리비아, 파라과이, 칠레의 모든 고객에 대한 총 영화 대여료
포함쿼리: 이 세국가 중 한 곳에서라도 더 많은 대여료를 지불한 모든 고객

= any 는 in 연산자를 사용하는 것과 같다.

9.3.2 다중 열 서브쿼리

둘 이상의 열을 반환하는 다중 열 서브쿼리를 사용해야 할 수도 있음.

성이 Monroe 인 배우
PG등급인 영화

-> Monroe 배우가 PG영화에 출연한 모든 사례 검색

각각 단일 열을 반환하는 두개의 서브쿼리 대신, 두개의 열을 반환하는 하나의 서브쿼리

교차 조인 사용

9.4 상관 서브쿼리

지금까지 살펴본 모든 서브쿼리는 포함 구문과 독립적이었다.
즉, 서브쿼리만으로 실행할 수 있고 결과를 확인할 수 있었다.

반면, 상관 서브쿼리는 하나 이상의 열을 참조하는 포함 구문에 의존적이다.

비상관 서브쿼리와 달리 상관 서브쿼리는 포함구문을 실행하기 전에 실행되지 않는다.

각 고객의 영화 대여 횟수를 계산 후, 정확히 20편의 영화를 대여한 고객 검색

서브쿼리의 맨 끝에 있는 c.customer_id에 대한 참조는 서브쿼리를 상호 연관 시킴.
포함 쿼리는 서브쿼리를 실행하려면 c.customer_id값을 제공해야 한다.

포함쿼리는 customer테이블에서 599개의 행을 모두 검색하고, 각 고객에 대해 한번씩 서브쿼리를 실행해서 해당하는 고객ID를 전달한다.
서브쿼리가 값20을 반환하면 필터조건이 충족되고 해당 행은 결과셋에 추가된다.

상관쿼리는 포함쿼리의 각 행에 대해 한번씩 실행되므로, 포함쿼리가 많은 행을 반환할 경우 성능 문제가 발생할 수 있다.

범위 조건에도 아래와 같은 상관서브쿼리를 사용할 수 있다.

서브쿼리를 실행할 때마다 지정된 고객의 총계좌 잔액이 반환됨.

9.4.1 exists 연산자

상관 서브쿼리로 조건을 작성할 때 가장 일반적으로 사용되는 exists

수량에 관계없이 관계가 존재하는지 확인하고자 할때 사용

2005-05-25 이전에 한편의 이상의 영화를 대여한 모든 고객 검색

exists를 사용할 때는 1 또는 *를 사용하는게 규칙

not exists 사용

R등급 영화에 한번도 등장한 적 없는 모든 배우

9.4.2 상관 서브쿼리를 이용한 데이터 조작

상관 서브쿼리는 update문과 delete문에서 자주 쓰임

customer 테이블에서 last_update열을 수정할 때 쓰는 상관 서브쿼리 예제1

rental 테이블에서 각 고객의 최신 대여날짜를 찾아 customer 테이블의 모든 last_update 행을 수정한다.
모든 고객이 영화를 한번 이상 대여했을 것이라고 예상하는게 합리적이지만, 확인해야한다.
만약 그렇지 않다면, 서브쿼리가 행을 반환하지 않으므로, 열이 null로 설정된다.

예제2

last update 열의 데이터가 null로 덮어씌워지지 않도록 방지함.

지난 1년동안 영화를 대여하지 않은 고객의 행을 제거

MySQL에서는 delete문을 실행할 때 테이블 별칭이 허용되지 않으므로 delete문과 함께 상관 서브쿼리를 사용할 때는 서브쿼리에 전체 테이블 이름을 적어줘야 함.

9.5 서브쿼리를 사용하는 경우

서브쿼리로 사용자 정의 테이블을 구성하고, 조건을 작성하고, 결과셋에서 열의 값을 생성하는 법을 알아보자

9.5.1 데이터 소스로서의 서브쿼리

서브쿼리는 영화 대여횟수, 총 지불액과 함께 고객ID목록을 생성하고 pymnt라는 이름으로 지정
포함쿼리는 pymnt를 조인하고 customer테이블에서 고객의 이름 검색

from절에서 사용되는 서브쿼리는 비상관 관계여야 한다.
서브 쿼리가 먼저 실행되고, 포함쿼리가 실행을 완료할 때까지 데이터는 메모리에 보관됨.
서브쿼리는 사용가능한 테이블 집합을 훨씬 넘어서 원하는 데이터의 모든 뷰를 만든 다음 그 결과를 다른 테이블 또는 서브쿼리에 조인할 수 있으므로 쿼리작성에 탁월한 유연성을 제공.

데이터 구성

존재하지 않는 데이터를 생성할 수 있다.

단계1. 그룹을 정의할 쿼리 작성

단계2. 또다른 쿼리의 from절에 넣어서 고객 그룹 생성

영화 대여 횟수와 총 지불액을 반환, pymnt_grps라는 두번째 서브쿼리는 세 개의 고객 그룹을 생성.

두 개의 서브쿼리는 각 고객이 세 그룹중 어느 그룹에 속하는지 찾아서 조인되고, 행들은 각 그룹의 고객 수를 계산하기 위해 사용된다.

이때, 첫번째 서브쿼리에서 count(*) num_rentals 는 사실 필요없다.

똑같은 결과가 출력된다.
실행시간도 0.013sec -> 0.010sec으로 줄었다.

태스크 지향 서브쿼리

그룹화에 필요한 customer_id, amount 정보는 payment 테이블에 있다.
따라서 그룹을 생성하는 작업을 서브쿼리로 분리 한다음,
다른 세 테이블을 서브쿼리에서 생성된 테이블에 결합하면 원하는 최종결과를 얻을 수 있다.

공통 테이블 표현식

버전 8.0에서 추가된 CTE.
with절 쿼리의 맨 위에 표시되는 서브쿼리. 쉼표로 구분된 여러 CTE를 포함할 수 있다.
쿼리를 더 이해하기 쉽게 만들 수 있고, 각 CTE가 동일한 with절에서 앞에 정의된 다른 CTE를 참조할 수 있다.

3개의 CTE를 포함한다.
성이 S로 시작하는 배우가 출연하는 PG등급 영화 대여로 발생한 총 수익을 계산

서브쿼리1: 성이 S로 시작하는 모든 배우 찾기
서브쿼리2: pg등급의 영화를 필터링한 결과 데이터셋과 film테이블 조인
서브쿼리3 payment테이블에 조인해서 이들 영화 대여에 지불된 금액 검색

최종쿼리는 이름/성으로 그룹화하고 수익 합산.

with 이름 as (서브쿼리), 이름 as (서브쿼리), 이름 as (서브쿼리) 최종쿼리; 형태를 가짐

9.5.2 표현식 생성기로서의 서브쿼리

스칼라 서브쿼리는 select, order by절, insert문의 values 절 포함하여 모든 곳에서 사용 가능

이 쿼리와 from 절에서 서브쿼리를 사용하던 이전 쿼리에는 두 가지 주요한 차이점이 있다.

• payment테이블에 조인하는 대신, 상관관계가 있는 스칼라 서브쿼리를 select 절에 사용해서 고객의 이름/성 도시를 조회
• customer 테이블에는 한번만 엑세스 하지않고 세번 엑세스한다.

다음은 배우의 이름과 성을 검색하고 배우가 출연한 영화 수를 기준으로 정렬한다.

9.6 서브쿼리 요약 정리

• 단일 열과 행, 다중 행이 있는 단일 열, 다중 열과 행을 반환
• 독립적인 포함 구문(비상관 서브쿼리)
• 포함 구문에서 하나 이상의 열을 참조(상관 서브쿼리)
• 특수 목적 연산자인 in, not in, exists, not exists와 같은 연산자를 조건에서 사용
• 서브쿼리를 select,update,delete,insert 문에 적용
• 쿼리에서 다른 테이블(또는 서브쿼리)과 조인할 수 있는 결과셋 생성
• 값을 생성해서 테이블을 채우거나 쿼리의 결과와 열을 채우는데 사용
• select, from, where, having, order by 절에서 사용

서브 쿼리는 매우 유용한 도구이다.

9.7 실습

9-1

9-2

9-3

USE sakila;

##9.1 서브쿼리
SELECT customer_id, first_name, last_name
FROM customer
WHERE customer_id = (SELECT MAX(customer_id) FROM customer);

##9.3 비상관 서브쿼리
SELECT * FROM country;

SELECT city_id, city
FROM city
WHERE country_id <>
(SELECT country_id FROM country WHERE country = 'India');

SELECT country_id, country FROM country WHERE country <> 'India';

### IN절
SELECT country_id
FROM country
WHERE country IN ('Canada', 'Mexico');

SELECT country_id
FROM country
WHERE country ='Canada' OR country = 'Mexico';


SELECT city_id, city
FROM city
WHERE country_id IN
(SELECT country_id 
	FROM country 
	WHERE country IN ('Canada', 'Mexico')
	);

### ALL연산자
SELECT first_name, last_name
FROM customer
WHERE customer_id <> ALL
	(SELECT customer_id
	FROM payment
	WHERE amount = 0);
	
SELECT first_name, last_name
FROM customer
WHERE customer_id NOT IN
	(SELECT customer_id
	FROM payment
	WHERE amount = 0);

SELECT customer_id, COUNT(*)
FROM rental
GROUP BY customer_id
HAVING COUNT(*) > ALL
	(SELECT COUNT(*)
	FROM rental r
		INNER JOIN customer c
		ON r.customer_id = c.customer_id
		INNER JOIN address a
		ON c.address_id = a.address_id
		INNER JOIN city ct
		ON a.city_id = ct.city_id
		INNER JOIN country co
		ON ct.country_id = co.country_id
	WHERE co.country IN ('United States', 'Mexico', 'Canada')
	GROUP BY r.customer_id);

### any연산자
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > ANY
	(SELECT SUM(p.amount)
	FROM payment p
		INNER JOIN customer c
		ON p.customer_id = c.customer_id
		INNER JOIN address a
		ON c.address_id = a.address_id
		INNER JOIN city ct
		ON a.city_id = ct.city_id
		INNER JOIN country co
		ON ct.country_id = co.country_id
	WHERE co.country IN ('Bolivia', 'Paraguay', 'Chile')
	GROUP BY co.country);

## 9.3.2 다중 열 서브쿼리
SELECT fa.actor_id, fa.film_id
FROM film_actor fa
WHERE fa.actor_id IN
(SELECT actor_id FROM actor WHERE last_name = 'MONROE')
 AND fa.film_id IN 
(SELECT film_id FROM film WHERE rating = 'PG');

SELECT actor_id, film_id
FROM film_actor 
WHERE (actor_id, film_id) IN
(SELECT a.actor_id, f.film_id 
FROM actor a
	CROSS JOIN film f
WHERE a.last_name = 'MONROE'
AND f.rating = 'PG');

#9.4 상관 서브 쿼리
SELECT c.first_name, c.last_name
FROM customer c
WHERE 20 = 
(SELECT COUNT(*) FROM rental r
WHERE r.customer_id = c.customer_id);

SELECT c.first_name, c.last_name
FROM customer c
WHERE (SELECT SUM(p.amount) 
	FROM payment p 
	WHERE p.customer_id = c.customer_id)
BETWEEN 180 AND 240;

##9.4.1 exists 연산자
SELECT c.first_name, c.last_name
FROM customer c
WHERE EXISTS
(SELECT 1 FROM rental r
WHERE r.customer_id = c.customer_id
AND DATE(r.rental_date) < '2005-05-25'); 

SELECT a.first_name, a.last_name
FROM actor a
WHERE NOT EXISTS
(SELECT 1 FROM film_actor fa
INNER JOIN film f ON f.film_id = fa.film_id
WHERE fa.actor_id = a.actor_id
AND f.rating = 'R');

## 9.4.2 상관 서브쿼리를 이용한 데이터 조작
UPDATE customer c
SET c.last_update =
(SELECT MAX(r.rental_date) FROM rental r
WHERE r.customer_id = c.customer_id);

UPDATE customer c
SET c.last_update =
(SELECT MAX(r.rental_date) FROM rental r
WHERE r.customer_id = c.customer_id)
WHERE EXISTS
(SELECT 1 FROM rental r
WHERE r.customer_id = c.customer_id);

DELETE FROM customer
WHERE 365 < ALL
(SELECT DATEDIFF(NOW(), r.rental_date) days_since_last_rental
FROM rental r
WHERE r.customer_id = customer.customer_id);

## 9.5.1 데이터 소스로서의 서브쿼리
SELECT c.first_name, c.last_name,
pymnt.num_rentals, pymnt.tot_payments
FROM customer c
	INNER JOIN 
	(SELECT customer_id, COUNT(*) num_rentals, SUM(amount) tot_payments
	FROM payment
	GROUP BY customer_id) pymnt
ON c.customer_id = pymnt.customer_id;

SELECT 'Small Fry' NAME, 0 low_limit, 74.99 high_limit
UNION ALL
SELECT 'Average Joes' NAME, 75 low_limit, 149.99 high_limit
UNION ALL
SELECT 'Heavy Hitters' NAME, 150 low_limit, 999999.99 high_limit;

SELECT pymnt_grps.name, COUNT(*) num_customers 
FROM
(SELECT customer_id, COUNT(*) num_rentals, SUM(amount) tot_payments
FROM payment
GROUP BY customer_id
) pymnt
	INNER JOIN
	(SELECT 'Small Fry' NAME, 0 low_limit, 74.99 high_limit
	UNION ALL
	SELECT 'Average Joes' NAME, 75 low_limit, 149.99 high_limit
	UNION ALL
	SELECT 'Heavy Hitters' NAME, 150 low_limit, 999999.99 high_limit
	) pymnt_grps
	ON pymnt.tot_payments
		BETWEEN pymnt_grps.low_limit AND pymnt_grps.high_limit
GROUP BY pymnt_grps.name;

SELECT pymnt_grps.name, COUNT(*) num_customers 
FROM
(SELECT customer_id, SUM(amount) tot_payments
FROM payment
GROUP BY customer_id
) pymnt
	INNER JOIN
	(SELECT 'Small Fry' NAME, 0 low_limit, 74.99 high_limit
	UNION ALL
	SELECT 'Average Joes' NAME, 75 low_limit, 149.99 high_limit
	UNION ALL
	SELECT 'Heavy Hitters' NAME, 150 low_limit, 999999.99 high_limit
	) pymnt_grps
	ON pymnt.tot_payments
		BETWEEN pymnt_grps.low_limit AND pymnt_grps.high_limit
GROUP BY pymnt_grps.name;

#### 태스크 지향 서브쿼리(task-oriented)
SELECT c.first_name, c.last_name, ct.city,
SUM(p.amount) tot_payments, COUNT(*) tot_rentals
FROM payment p
	INNER JOIN customer c
	ON p.customer_id = c.customer_id
	INNER JOIN address a
	ON c.address_id = a.address_id
	INNER JOIN city ct
	ON a.city_id = ct.city_id
GROUP BY p.customer_id;

SELECT customer_id, COUNT(*) tot_rentals, SUM(amount) tot_payments
FROM payment
GROUP BY customer_id;

SELECT c.first_name, c.last_name, ct.city,
pymnt.tot_payments, pymnt.tot_rentals
FROM 
(SELECT customer_id, COUNT(*) tot_rentals, SUM(amount) tot_payments
FROM payment
GROUP BY customer_id
) pymnt
	INNER JOIN customer c
	ON pymnt.customer_id = c.customer_id
	INNER JOIN address a
	ON c.address_id = a.address_id
	INNER JOIN city ct
	ON a.city_id = ct.city_id;
	
	
#### 공통 테이블 표현식
WITH actors_s AS 
(SELECT actor_id, first_name, last_name
FROM actor
WHERE last_name LIKE 'S%'
), 
actors_s_pg AS
(SELECT s.actor_id, s.first_name, s.last_name, f.film_id, f.title
FROM actors_s s
	INNER JOIN film_actor fa
	ON s.actor_id = fa.actor_id
	INNER JOIN film f
	ON f. film_id = fa.film_id
WHERE f.rating = 'PG'
), 
actors_s_pg_revenue AS
(SELECT spg.first_name, spg.last_name, p.amount
FROM actors_s_pg spg
	INNER JOIN inventory i
	ON i.film_id = spg. film_id
	INNER JOIN rental r
	ON i.inventory_id = r.inventory_id
	INNER JOIN payment p
	ON r.rental_id = p.rental_id
) -- end of With clause
SELECT spg_rev.first_name, spg_rev.last_name, SUM(spg_rev.amount) tot_revenue
FROM actors_s_pg_revenue spg_rev
GROUP BY spg_rev.first_name, spg_rev.last_name
ORDER BY 3 DESC;

### 9.5.2 표현식 생성기로서의 서브쿼리
SELECT
(SELECT c.first_name FROM customer c
WHERE c.customer_id = p.customer_id) first_name,
(SELECT c.last_name FROM customer c
WHERE c.customer_id = p.customer_id) last_name,
(SELECT ct.city FROM customer c
	INNER JOIN
	address a
	ON c.address_id = a.address_id
	INNER JOIN
	city ct
	ON a.city_id = ct.city_id
	WHERE c.customer_id = p.customer_id
) city,
SUM(p.amount) tot_payments,
COUNT(*) tot_rentals
FROM payment p
GROUP BY p.customer_id;

SELECT a.actor_id, a.first_name, a.last_name
FROM actor a
ORDER BY
(SELECT COUNT(*) FROM film_actor fa
WHERE fa.actor_id = a.actor_id) DESC;

SELECT * FROM film;
SELECT * FROM category;
SELECT * FROM film_category;

## 실습 9-1
SELECT * 
FROM film f
WHERE f.film_id IN
(SELECT fc.film_id FROM film_category fc 
	INNER JOIN
	category c
	ON c.category_id = fc.category_id
WHERE c.name IN ('action'));

## 실습9-2
SELECT * 
FROM 
(SELECT film_id
FROM category c
	INNER JOIN film_category fc
	ON c.category_id = fc.category_id 
WHERE c.name = 'Action') af
	INNER JOIN film f
	ON f.film_id = af.film_id;
	
## 실습 9-3

SELECT actor_id, levels.level, count_roles.roles FROM 
(SELECT actor_id, COUNT(*) roles 
FROM film_actor 
GROUP BY actor_id) count_roles
INNER JOIN 
	(SELECT 'Hollywood Star' LEVEL, 30 min_roles, 99999 max_roles
	UNION ALL
	SELECT 'Prolific Actor' LEVEL, 20 min_roles, 29 max_roles
	UNION ALL
	SELECT 'Newcomer' LEVEL, 1 min_roles, 19 max_roles) levels

	ON count_roles.roles
			BETWEEN levels.min_roles AND levels.max_roles;