[DATABASE] 서브쿼리

서브쿼리

• SQL 문장 안에 다른 쿼리문을 중첩해서 사용하는 쿼리
• 쿼리 안에 또 다른 쿼리
• 복잡한 조건을 간결하게 처리하기 위해
• 집계 결과를 필터 조건으로 쓰기 위해
• 다른 테이블과 비교하거나 조회한 값을 조건으로 쓰기 위해

---

서브쿼리의 종류

분류	설명	예시
스칼라 서브쿼리	하나의 값(1 row, 1 col)을 반환	(SELECT ...)
인라인 뷰	FROM절 안에서 테이블처럼 사용	FROM (SELCT ...)
서브쿼리 IN 절	특정 컬럼이 여러 값 중 하나와 일치하는지 체크	WHERE col IN (SELECT ...)
EXIXTS 서브쿼리	존재 여부 확인용	WHERE EXISTS (SELECT ...) WHERE NOT EXISTS (SELECT ...)

---

서브쿼리 사용시 주의사항

• 서브쿼리는 괄호로 묶어야 함
• 스칼라 서브쿼리는 반드시 1행 1열만 반환해야 함 (딱 하나의 컬럼을 지정해야하고 결과 값이 한 행만 나와야함)
• 성능 측면에서 풀 테이블 스캔이 되며 인덱스가 설정되어 있어도 안탈 수 있음 (실행 계획을 확인할 것)
• 성능 측면에서 서브쿼리보단 JOIN문 또는 WITH문(CTE) 활용 

---

종류별 문법 예시)

스칼라 서브쿼리

평균 수학점수보다 높은 학생을 추출하고 싶다.

• 수학시험본 학생은 STUDENT_SEQ 가 1, 2, 3
• '김갑용', '정호길', '최철순' 이 수학시험을 봤다.
• 평균 수학시험 점수를 계산하면 (90 + 60 + 55) / 3 = 68점이다. (소수점 버림)
• 여기서 68점 이상인 학생은 '김갑용' 90점 밖에 없다.

결과

쿼리문

SELECT
   ST.STUDENT_SEQ,
   ST.STUDENT_NAME AS 학생,
   SC.SUBJECT AS 과목,
   SC.SCORE AS 점수
FROM STUDENT ST LEFT JOIN SCORE SC
   ON ST.STUDENT_SEQ = SC.STUDENT_SEQ
WHERE SC.SUBJECT = '수학' 
AND SC.SCORE > (SELECT AVG(SCORE) FROM SCORE WHERE SUBJECT = '수학');

설명

• 학생과 성적을 확인하기위해 학생테이블과 성적 테이블을 JOIN 했다.
• 서브쿼리부터 조회하면 68점이 나온다.

• AND SC.SCORE > 68 로 조건이 적용되어 아래와 같은 SQL문처럼 실행된다. 

SELECT
   ST.STUDENT_SEQ,
   ST.STUDENT_NAME AS 학생,
   SC.SUBJECT AS 과목,
   SC.SCORE AS 점수
FROM STUDENT ST LEFT JOIN SCORE SC
   ON ST.STUDENT_SEQ = SC.STUDENT_SEQ
WHERE SC.SUBJECT = '수학' 
AND SC.SCORE > 68;

---

서브쿼리 IN 절

체육시험만 본 학생을 추출하고 싶다.

• 체육 시험을 본 학생의 SEQ 값은 4, 5번 학생 
• 4, 5번 학생은 학생 테이블에서 '박나라', '이금순'

결과

쿼리문

SELECT 
   STUDENT_NAME
FROM STUDENT  
WHERE STUDENT_SEQ IN (
   SELECT 
      STUDENT_SEQ
   FROM SCORE
   WHERE SUBJECT = '체육'
);

설명

• 서브쿼리가 먼저 실행되기 때문에 안쪽 서브쿼리가 어떤 결과 값을 리턴하는지 보면 STUDENT_SEQ 5, 4번을 리턴한다.

• 그럼 저 5, 4번이 IN절에 적용되어 아래와 같은 SQL문처럼 실행된다.

SELECT 
   STUDENT_NAME
FROM STUDENT  
WHERE STUDENT_SEQ IN (
   5, 4
);

---

인라인 뷰 (FROM절 서브쿼리)

과목별 최고 점수를 확인하고 싶다.

• 과학 90, 85, 88 중 최고 점수 = 90
• 국어 85, 70, 80 중 최고 점수 = 85
• 수학 90, 60, 55 중 최고 점수 = 90
• 체육 100, 55 중 최고점수 = 100

결과

쿼리문

SELECT
   T.*
FROM (
   SELECT 
      STUDENT_SEQ AS STUDENT_SEQ,
      SUBJECT AS SUBJECT,
      SCORE AS SCORE,
      RANK() OVER(PARTITION BY SUBJECT ORDER BY SCORE DESC) AS RANK
   FROM SCORE 
) T
WHERE T.RANK = 1

설명

• FROM 절 안에 서브쿼리를 우선 보면 RANK() OVER(PARTITION BY SUBJECT ORDER BY SCORE DESC) 절이 있다.
• 해석하면 순위를 과목(SUBJECT)별로, 점수가(SCORE)가 높은순으로 정렬한다.
• PARTITION BY = GROUP BY 라고 생각하면 쉽다 / ORDER BY 는 흔히알고 있는 정렬
• 서브쿼리 먼저 조회하면 아래와 같은 결과가 나온다.

• RANK() OVER() 절의 결과를 보면 과목별 최고 점수한테 RANK 컬럼으로 순위가 부여되어 있다.
• 과학 = 90 / 국어 = 85 / 수학 = 90 / 체육 = 100
• 해당 결과가 하나의 테이블이라고 보면된다. 그래서 인라인 뷰라고 하는 이유가 이때문이다.
• 서브쿼리 결과로 나온 인라인 뷰 테이블한테 별칭을 주고 (나같은 경우 T라고 줬다)
• T.RANK = 1을 통해 1위만 필터링하면 과목별 최고 점수결과가 출력된다.

---

EXIXTS 서브쿼리

학생들 중 시험을 본 학생들이 누군지 확인하고자 한다.

• 성적 테이블에서 STUDENT_SEQ 가 없는 번호는 6, 7 즉 '김대호', '박만기' 만 없다.
• 그 둘을 뺀 '김갑용', '정호길', '최철순', '박나라', '이금순'이 조회가 되어야 한다.

결과

쿼리문

SELECT 
   ST.STUDENT_NAME
FROM STUDENT ST
WHERE EXISTS (
   SELECT 
      SC.STUDENT_SEQ
   FROM SCORE SC
   WHERE ST.STUDENT_SEQ = SC.STUDENT_SEQ
)

설명

• WHERE EXISTS 괄호 안에 성적(SCORE) 테이블안 조건문을 보면
• 학생(STUDENT)테이블의 STUDENT_SEQ 와 성적(SCORE) 테이블의 STUDENT_SEQ 끼리 일치하는 값이 있는지 확인
• 성적이 있는 학생들만 조회된다.

번외

그럼 반대로 학생들 중 시험을 안 본 학생을 찾고자 한다.

쿼리문

SELECT 
   ST.STUDENT_NAME
FROM STUDENT ST
WHERE NOT EXISTS (
   SELECT 
      SC.STUDENT_SEQ
   FROM SCORE SC
   WHERE ST.STUDENT_SEQ = SC.STUDENT_SEQ
)

결과

• NOT EXISTS 사용하면 된다.

---

 

WITH - CTE (Common Table Expression)

• 임시테이블이라고 보면된다.
• 복잡한 서브쿼리를 분리시킬 때 유용하고 가독성이 향상된다.
• 재사용성도 가능하다
• 재귀 쿼리도 가능하다 (계층형 트리 구조 등 처리 가능)

---

WITH 문법

WITH 별칭 AS(
	SELECT ...
)

-- 예시)
WHTE STU AS(
	SELECT *
    FROM STUDENT
)

-- 여러개도 가능하다.
WHTE STU AS(
	SELECT *
    FROM STUDENT
), SC AS(
	SELECT *
    FROM SCORE
)

---

예시)

과목별 최고 점수 받은 학생을 보고 싶다.

• 쿼리 결과를 보면 과목별 최고점수자한테 1순위가 매핑되어 있고 학생 식별자인 STUDENT_SEQ도 확인된다.
• 학생(STUDENT) 테이블과 성적(SCORE) 테이블을 사용
• 위에서 배운 서브쿼리로 먼저 진행한다고 했을 때 아래와 같은 쿼리문이 나온다.

서브쿼리로 작성한 쿼리문

SELECT 
   ST.STUDENT_NAME AS 이름,
   TR.SUBJECT AS 과목,
   TR.SCORE AS 점수
FROM STUDENT ST LEFT JOIN (
            SELECT 
               STUDENT_SEQ,
               SUBJECT,
               SCORE,
               RANK() OVER(PARTITION BY SUBJECT ORDER BY SCORE DESC) AS RANK
            FROM SCORE
) AS TR 
   ON ST.STUDENT_SEQ = TR.STUDENT_SEQ
WHERE TR.RANK = 1;

• 가독성이 뒤떨어져 보이는 쿼리문이다.
• 이걸 WITH (CTE) 형태로 변경해서 사용하면 아래와 같은 쿼리문으로 변경된다.

WITH (CTE) 구문을 활용한 쿼리문

WITH TOPRANK AS (
   SELECT 
      STUDENT_SEQ AS STUDENT_SEQ,
      SUBJECT AS SUBJECT,
      SCORE AS SCORE,
      RANK() OVER(PARTITION BY SUBJECT ORDER BY SCORE DESC) AS RANK
   FROM SCORE 
)
SELECT 
   ST.STUDENT_NAME AS 이름,
   TR.SUBJECT AS 과목,
   TR.SCORE AS 점수
FROM STUDENT ST LEFT JOIN TOPRANK TR
   ON ST.STUDENT_SEQ = TR.STUDENT_SEQ
WHERE TR.RANK = 1

결과

• 서브쿼리나 WITH나 서로 결과 값은 같다.

WITH (CTE) 해석

• WITH 구문은 가독성이 뛰어나다
• 별칭을 부여해 여러번 재사용이 가능하다. (TOPRANK 가 별칭)