02_05 서브쿼리와 고급 DML 실습

하위 태스크 1 ~ 2

단일행 서브쿼리 예제 실행

06-1.sql 등에서 단일행 서브쿼리 예제를 실행하고 구조 분석

다중행 서브쿼리 예제 실행

IN/ANY/ALL 을 사용하는 예제를 실행하고 차이를 체감

06-1.sql은 SQL 함수 예제를 포함하고 있음을 이유로 서브쿼리 예제를 포함하는 05-2.sql을 분석했다.

다음은 = 비교 연산자를 사용하고 단일 행 서브쿼리를 포함하는 SELECT 문이다. 서브쿼리는 customer 테이블에서 first_name 열의 값이 ROSA인 행의 customer_id를 조회한다. 메인 쿼리는 customer 테이블로부터 customer_id가 서브 쿼리에서 조회된 결과와 같은 행의 모든 열을 조회한다.

SELECT -- 메인쿼리
	*
FROM
	customer
WHERE
	customer_id = (
	SELECT -- 서브쿼리
		customer_id
	FROM
		customer
	WHERE
		first_name = 'ROSA');

다음은 = 비교 연산자를 사용하고 다중 행 서브쿼리를 포함하는 SELECT 문이다. = 비교 연산자를 사용되었으나 연산자의 우항에 다중 행 서브쿼리의 조회 결과가 위치해 있으므로 오류가 발생한다.

SELECT -- 메인쿼리
	*
FROM
	customer
WHERE
	customer_id = (
	SELECT -- 서브쿼리
		customer_id
	FROM
		customer
	WHERE
		first_name IN ('ROSA', 'ANA'));

다음은 IN 연산자를 사용하고 다중 행 서브쿼리를 포함하는 SELECT 문이다. 서브 쿼리는 customer_id 열의 2개 행을 조회회한다. 메인쿼리의 WHERE 절은 first_name 열의 값이 서브쿼리의 조회 결과 중 하나에 해당하는지 검사한다.

SELECT -- 메인쿼리
	*
FROM
	customer
WHERE
	customer_id IN (
	SELECT -- 서브쿼리
		customer_id
	FROM
		customer
	WHERE
		first_name IN ('ROSA', 'ANA'));

다음은 IN 연산자를 사용하고 다중 행 서브쿼리를 포함하는 SELECT 문이다. 서브쿼리는 customer_id 열의 64개 행을 조회한다. 메인쿼리는 film_id 열의 값이 서브쿼리의 조회 결과 중 하나에 해당하는지 검사한다.

SELECT -- 메인쿼리
	film_id,
	title
FROM
	film
WHERE
	film_id IN (
	SELECT -- 서브쿼리
		a.film_id
	FROM
		film_category AS a
	INNER JOIN category AS b ON
		a.category_id = b.category_id
	WHERE
		b.name = 'Action'
	);

다음은 NOT IN 연산자를 사용하고 다중 행 서브 쿼리를 포함하는 SELECT 문이다. 서브쿼리는 film_id 열의 64개 행을 조회한다. 메인쿼리는 film_id 열의 값이 서브쿼리의 조회 결과 중 어느 것에도 해당하지 않는지 검사한다.

SELECT -- 메인쿼리
	film_id,
	title
FROM
	film
WHERE
	film_id NOT IN (
	SELECT -- 서브쿼리리
		a.film_id
	FROM
		film_category AS a
	INNER JOIN category AS b ON
		a.category_id = b.category_id
	WHERE
		b.name = 'Action'
	);

다음은 = ANY 연산자를 사용하고 다중 행 서브쿼리를 포함하는 SELECT 문이다. 서브쿼리는 customer_id 열의 2개 행을 조회한다. 메인쿼리는 customer_id 열의 값이 서브쿼리의 조회 결과에 속하는지 검사한다.

SELECT -- 메인쿼리
	*
FROM
	customer
WHERE
	customer_id = ANY (
	SELECT -- 서브쿼리
		customer_id
	FROM
		customer
	WHERE
		first_name IN ('ROSA', 'ANA'));
 

다음은 < ANY 연산자를 사용하고 다중 행 서브쿼리를 포함하는 SELECT 문이다. 서브쿼리는 customer_id 열의 2개 행을 조회한다. 메인쿼리는 customer_id 열의 값이 서브쿼리의 조회 결과 중 최대값보다 작은지 검사한다.

SELECT -- 메인쿼리
	*
FROM
	customer
WHERE
	customer_id < ANY (
	SELECT -- 서브쿼리
		customer_id
	FROM
		customer
	WHERE
		first_name IN ('ROSA', 'ANA'));

다음은 > ANY 연산자를 사용하고 다중 행 서브쿼리를 포함하는 SELECT 문이다. 서브쿼리는 customer_id 열의 2개 행을 조회한다. 메인쿼리는 customer_id 열의 값이 서브쿼리의 조회 결과 중 최소값보다 큰지 검사한다.

SELECT -- 메인 쿼리
	*
FROM
	customer
WHERE
	customer_id > ANY (
	SELECT -- 서브 쿼리
		customer_id
	FROM
		customer
	WHERE
		first_name IN ('ROSA', 'ANA'));

다음은 = ALL 연산자를 사용하고 다중 행 서브쿼리를 포함하는 SELECT 문이다. 서브쿼리는 customer_id 열의 2개 행을 조회한다. 메인쿼리는 customer_id 열의 값이 서브쿼리의 조회 결과 모두와 일치하는지 검사한다.

SELECT -- 메인쿼리리
	*
FROM
	customer
WHERE
	customer_id = ALL (
	SELECT -- 서브쿼리
		customer_id
	FROM
		customer
	WHERE
		first_name IN ('ROSA', 'ANA'));

하위 태스크 3

조인 vs 서브쿼리 비교

동일한 결과를 JOIN 과 서브쿼리 두 방식으로 구현해 비교

다음은 고객별 총 결제 금액과 결제 횟수를 조회하는 SELECT 문이다. customer_id열을 조인키로 사용해서 customer 테이블과 payment 테이블을 조인했다.

SELECT
	c.first_name ,
	c.last_name,
	SUM(p.amount) AS total_amount
FROM
	customer c
JOIN payment p ON
	c.customer_id = p.customer_id
GROUP BY
	c.customer_id
HAVING
	total_amount > 100;

서브 쿼리를 사용해서 앞선 SELECT 문과 동일한 조회 결과를 내는 SELECT 문은 다음과 같다. JOIN을 사용했을 때보다 쿼리문의 길이가 길어졌고 중첩된 서브쿼리 때문에 가독성이 하락했다.

SELECT
	c.first_name,
	c.last_name,
	p_sum.total_amount
FROM
	customer c,
	(
	SELECT
		customer_id,
		SUM(amount) AS total_amount
	FROM
		payment
	GROUP BY
		customer_id
	HAVING
		SUM(amount) > 100) p_sum
WHERE
	c.customer_id = p_sum.customer_id;
 

하위 태스크 4

EXISTS/NOT EXISTS 연습

관련 데이터 존재 여부에 따라 결과가 달라지는 쿼리 작성

다음은 EXISTS 연산자를 사용하고 다중 행 서브쿼리를 포함하는 SELECT 문이다. 서브쿼리는 customer_id 열의 2개 행을 조회한다. 메인쿼리는 서브쿼리의 조회 결과가 1개 이상인지 검사한다.

SELECT -- 메인쿼리
	*
FROM
	customer
WHERE
	EXISTS (
	SELECT -- 서브쿼리
		customer_id
	FROM
		customer
	WHERE
		first_name IN ('ROSA', 'ANA'));

다음은 NOT EXISTS 연산자를 사용하고 다중 행 서브쿼리를 포함하는 SELECT 문이다. 서브쿼리는 customer_id 열의 0개 행을 조회한다. 메인쿼리는 서브쿼리의 조회 결과가 0개인지 검사한다.

SELECT -- 메인쿼리
	*
FROM
	customer
WHERE
	NOT EXISTS (
	SELECT -- 서브쿼리리
		customer_id
	FROM
		customer
	WHERE
		first_name IN ('KANG'));

다음은 city 테이블의 city 열의 값이 SEOUL인 행이 있는 경우에만 address 테이블 전체를 조회한다. 실행 시 city 열의 값이 SEOUL인 경우는 존재하지 않으므로 0개 행이 조회된다.

SELECT
	*
FROM
	address
WHERE
	EXISTS (
	SELECT
		city_id
	FROM
		city
	WHERE
		city IN ('SEOUL'));

하위 태스크 5

상관 서브쿼리 분석

메인 쿼리 컬럼을 참조하는 서브쿼리의 실행 방식을 이해

다음은 서브쿼리에서 메인쿼리의 컬럼을 참조하는 상관 서브쿼리 예제다. 서브쿼리는 WHERE 절에서 a.film_id를 지정하는데, a는 메인쿼리의 FROM 절에서 film 테이블의 별칭으로 지정된다.

SELECT -- 메인쿼리
	a.film_id,
	a.title,
	a.special_features,
	(
	SELECT -- 서브쿼리
		c.name
	FROM
		film_category as
	b
	INNER JOIN category AS c ON
		b.category_id = c.category_id
	WHERE
		a.film_id =
	b.film_id) AS name
FROM
	film AS a
WHERE
	a.film_id > 10
	AND a.film_id < 20;

하위 태스크 6 ~ 7

인라인 뷰 실습

FROM 절 서브쿼리로 중간 결과를 만들고, 이를 다시 조인

복잡 서브쿼리 단계 나누기

중첩된 서브쿼리를 단계별로 나누어 디버깅 가능한 형태로 재작성

다음은 FROM 절에 서브쿼리를 사용한 예제다.

SELECT -- 메인쿼리
	a.film_id,
	a.title,
	a.special_features,
	x.name
FROM
	film AS a
INNER JOIN (
	SELECT -- 서브쿼리
		b.film_id,
		c.name
	FROM
		film_category AS b
	INNER JOIN category AS c ON
		b.category_id = c.category_id
	WHERE
		b.film_id > 10
		AND b.film_id < 20
	) AS x ON
	a.film_id = x.film_id;

서브쿼리의 실행 결과는 다음과 같다. film_id 열과 name 열을 포함하는 다중 행 서브쿼리다.

SELECT
	b.film_id,
	c.name
FROM
	film_category AS b
INNER JOIN category AS c ON
	b.category_id = c.category_id
WHERE
	b.film_id > 10
	AND b.film_id < 20;

이 서브쿼리는 메인쿼리에서 film_id 열을 조인키로 하여 film_category 테이블과 조인된다.

SELECT
	*
FROM
	film AS a
INNER JOIN (
	SELECT
		b.film_id,
		c.name
	FROM
		film_category AS b
	INNER JOIN category AS c ON
		b.category_id = c.category_id
	WHERE
		b.film_id > 10
		AND b.film_id < 20
	) AS x ON
	a.film_id = x.film_id;

SELECT 문에 조회 결과에 포함할 열만을 나열하면 다음과 같다.

SELECT
	a.film_id,
	a.title,
	a.special_features,
	x.name
FROM
	film AS a
INNER JOIN (
	SELECT
		b.film_id,
		c.name
	FROM
		film_category AS b
	INNER JOIN category AS c ON
		b.category_id = c.category_id
	WHERE
		b.film_id > 10
		AND b.film_id < 20
	) AS x ON
	a.film_id = x.film_id;