(SQL) 서브쿼리 개념, 종류 및 예시

1. With

• 기존 테이블에서 테이블을 "의사 생성" 하고 메인 쿼리 상단에 임시 테이블을 시각적으로 범위를 정하려는 경우

WITH subqurery_name (column_name1, ...) AS (SELECT ..._)
SELECT ...

 

2. Nested

• 임시 테이블이 더 큰 쿼리 내에서 필터 역할을 하도록 할 때 사용
• WHERE 절 안에 위치

SELECT s.s_id, s.s_name, g.final_grade
FROM student s, grades g
WHERE s.s_id = g.s_id IN 
	(SELECT final_grade
    FROM grades g
    WHERE final_grade > 3.7
    );

3. Inline

• with와 동일한 방식으로 사용되나 메인 쿼리 위에 있는 임시 테이블 대신 FROM 절에 포함

SELECT student_name
FROM
	(SELECT student_id, student_name, grade
    FROM student
    WHERE teacher = 10)
WHERE grade > 80;

4.  Scalar

• 스칼라 값을 생성하려는 경우
• SELECT 절 내에 위치

SELECT s.student_name
	(SELECT AVG(final_score)
	FROM grades g
	WHERE g.student_id = s.student_id) AS avg_score
FROM student s;

 

5. 작성 순서

1) 서브 쿼리 작성 : 메인 쿼리의 일부로 활용하려는 기존 테이블 집계

2) 서브 쿼리 실행 : 서브 쿼리는 독립적으로 존재할 수 있기 때문에 이 집계가 예상했던 중간 출력인지 여부를 파악하려면 콘텐츠 실행

3) 이름 지정 : 이 서브 쿼리를 괄호로 닫고 이름 지정

4) 다시 테스트 : 메인 쿼리 내에서 SELECT *를 실행하여 서브 쿼리의 모든 구문이 올바른지 확인

5) 외부 쿼리 작성 : 서브 쿼리를 적절하게 활용하여 문제를 해결하는 데 적합하다고 생각되는 대로 SELECT *절 작성

 

6. 예시

1) Inline 서브쿼리 예시

SELECT channel, AVG(event_count)AS avg_event_count
FROM
	(SELECT DATE_TRUNC('day', occurred_at) AS day, channel, count(*) AS event_count
    FROM web_events
    GROUNP BY 1, 2) sub
GROUP BY 1
ORDER BY 2 DESC;

 

2) Nested 서브쿼리 예시

• DATE_TRUNC 를 사용하여 orders 테이블에 있는 첫 번째 주문에 대한 month 정보 가져오기

SELECT *
FROM orders
WHERE DATE_TRUNC('month', occurred_at ) = 
	(SELECT DATE_TRUNC('month', MIN(occurred_at)) AS min_month
    FROM orders)
ORDER BY occurred_at;

• 위의 쿼리의 결과를 사용하여 첫 번째 주문과 같은 월과 연도에 발생한 주문만 찾은 다음 이 달의 용지 수량 유형별 평균 구하기

SELECT AVG(standard_qty) avg_std, AVG(gloss_qty) avg_gls, AVG(poster_qty) avg_pst
FROM orders
WHERE DATE_TRUNC('month', occurred_at) = 
	(SELECT DATE_TRUNC('month', MIN(occurred_at)) FROM orders);