[개념정리] 서브쿼리(Subquery)

다른 SQL 안에 들어있는 SELECT

MySQL은 서브쿼리가 반환하는 형태가 4종류

• scalar(값 1개)
• row(행 1개)
• column(열 1개 = 리스트)
• table(표)

---

<Scalar Subquery; 독립된 값1개로 반환>

Q. 평균보다 비싼 강좌 찾기
(1. 서브 쿼리)모든 강좌 평균 구하기 (2. 바깥 쿼리) 그것보다 비싼 강좌 찾기

SELECT
  course_id,
  course_name,
  list_price
FROM basic.courses
WHERE list_price > (SELECT AVG(list_price) FROM basic.courses)
ORDER BY list_price DESC;

• (SELECT AVG(list_price) FROM basic.courses) = 120428.5714
  - 바깥 쿼리와 연동되지 않은 독립된 값 출력
• 서브 쿼리 단독 실행 시, 작동함.
• where에는 집계함수 사용 불가, 서브쿼리 형태 이용.

---

<Column Subquery; 열1개, 리스트>
IN()

Q. 'sql 카테고리 강좌'를 신청한 내역만 보기
(1. 서브 쿼리) T.courses - C.category = 'sql'
(2. 바깥 쿼리) T.enrollments

• (T)courses와 (T)enrollments의 공통 컬럼: course_id
  (*결국 두 개의 쿼리가 연동되는 것 - ⭐️ 공통 컬럼을 연동해주는 게 중요)
• IN ( ... ) 안쪽은 한 컬럼짜리 리스트가 나오면 성공

SELECT enrollment_id, final_price
FROM basic.enrollments
where course_id IN 
	(
	SELECT course_id -- 한 줄짜리 컬럼 출력
	FROM basic.courses
	where category = 'sql'
	);

• 서브쿼리 컬럼을 여러 개 출력하면, 에러.

---

<Table Subquery; 표>
EXISTS / NOT EXISTS (“한 번이라도 있다 / 한 번도 없다” 찾기에 용이)

• 서브쿼리의 SELECT는 결과에 무관 (select *, select 1, select 5 등 어떤 걸 써도 결과 동일)
• 두 테이블의 공통 컬럼을 where에서 묶어줌. (= 두 테이블에 모두 존재하는 행 찾기)
• exists: 매칭되는 게 있는 것 / not exists: 매칭되지 않는 것.
• True/False 값을 반환하는 거여서, 서브 쿼리 내 결과 행을 밖으로 가져올 수 없음.

Q. 수강신청을 "한 번이라도 한 / 한 번도 안 한" 학생 찾기
(1. 서브 쿼리) (T)enrollments ⚯ (T)students
(2. 바깥 쿼리) (T)students 출력

-- 한 번이라도 한 학생 찾기
SELECT s.student_id,
	   s.student_name
FROM basic.students s
where exists
	(
	select *
	from basic.enrollments e
	where e.student_id=s.student_id
	);

-- 한 번도 안 한 학생 찾기
SELECT s.student_id,
	   s.student_name
FROM basic.students s
where not exists
	(
	select *
	from basic.enrollments e
	where e.student_id=s.student_id
	);

---

<FROM 서브쿼리(파생 테이블)>
From에서 사용할 테이블을 서브쿼리로 생성

• JOIN에서 N쪽 테이블을 '우선 요약'하여 사용하는 개념
• FROM 테이블 이미 필터링된 상태 → 최종 결과에서 행이 늘어나지 않음.
• AS 별칭 필수

Q. 강좌별 신청 건수 "요약표" 만들기
(1. 서브 쿼리) 신청 건수 테이블 만들기
(2. 바깥 쿼리) 강좌별로 출력하기

-- 강좌별
SELECT c.course_id,
       c.course_name,
       ifnull(enr.enroll_cnt,0)
from basic.courses c
left join (-- 신청건수
	select course_id,
		   count(*) as enroll_cnt
	from basic.enrollments
	group by course_id
	) as enr
ON c.course_id=enr.course_id;

• 서브쿼리 안에서 course_id로 이미 group by했기 때문에,
  바깥 쿼리에서 추가로 그룹핑할 필요 X

---

<CTE (WITH) : 이름 붙인 파생테이블>

• 이름이 있는 임시 결과

WITH 이름 AS (
  SELECT ...
)
SELECT *
FROM 이름;

 

Q. "active 신청만" 먼저 골라서 보기
(1. CTE 서브 쿼리) active 결과만 뽑아서 임시표 만들기
(2. 바깥 쿼리) 임시표에서 출력하기

WITH active_enrollment as
	(-- CTE
        select enrollment_id,
               student_id,
               course_id,
               enroll_date
        from basic.enrollments
        where enrollment_status = 'active'
        )
-- 바깥 쿼리
select *
from active_enrollment;

---

<CTE (WITH) + CASE 같이 사용하기>

• CTE 파생테이블 생성
• 'CTE' JOIN '원래 테이블(case when 사용)' => 결과

Q. 학생별 신청건수 구하기 + 건수에 따른 라벨링
(1. CTE) 파생테이블: 학생별 신청건수
(2. JOIN) 학생별 join 신청건수(→CTE)
                + 라벨링 case when 조건

WITH enr_by_stu AS (
	SELECT student_id,
	       count(*) AS enroll_cnt
	FROM basic.enrollments
	GROUP BY student_id
	)
SELECT s.student_id,
       s.student_name,
       ifnull(enroll_cnt, 0) AS enroll_cnt,
       CASE WHEN ifnull(e.enroll_cnt, 0) = 0 THEN 'no_enroll'
         	WHEN e.enroll_cnt >= 1 THEN 'has_enroll'
        	END AS enroll_flag
FROM basic.students s
	LEFT JOIN enr_by_stu e
	ON s.student_id=e.student_id;

---

<상관 서브쿼리(Correlated Subquery)>

: 서브쿼리 안에서 바깥 쿼리의 테이블 컬럼을 참조하는 서브쿼리

“바깥 쿼리에서 한 행을 꺼낼 때마다, 그 행에 맞춰서 안쪽 쿼리가 계산된다”

• 비상관 서브쿼리: 서브쿼리의 결과가 조건으로 사용된다.
• 상관 서브쿼리: 바깥 쿼리의 한 행을 출력할 때마다, 그 행에 매칭되는 서브쿼리가 출력된다.

SELECT
  s.student_id,
  s.student_name,
  (
    SELECT COUNT(*)
    FROM basic.enrollments e
    WHERE e.student_id = s.student_id
  ) AS enroll_cnt
FROM basic.students s
ORDER BY s.student_id;