복잡한 중첩 쿼리 예시: EXISTS, IN, HAVING 조합하기

축하해, 이제 진짜 재밌는 부분에 왔어! 오늘은 여러 종류의 서브쿼리를 조합해서 복잡한 문제를 어떻게 푸는지 볼 거야. EXISTS, IN, HAVING — 이 세 가지가 있으면 데이터베이스 마법사 느낌 제대로 날걸. 한 테이블에서 데이터를 뽑고, 다른 테이블 데이터로 필터링하고, 그룹화하고, 또 그룹화한 걸 다시 필터링할 거야. 그리고 보너스로 쿼리를 더 효율적으로 만드는 팁도 알아볼 거야.

일단 오늘 강의 내내 풀어볼 공통 문제부터 세팅해보자.

문제 세팅

우리한테는 세 개의 테이블이 있는 대학 데이터베이스가 있다고 가정하자:

students 테이블

courses 테이블

enrollments 테이블

다음 조건을 만족하는 모든 학생을 뽑아야 해:

1. 최소 한 과목이라도 수강 신청한 학생 EXISTS.
2. 수강 신청한 과목 중 적어도 하나는 점수가 없는 학생 IN.
3. 평균 점수가 80점 넘는 그룹에 속한 학생 HAVING.

EXISTS와 IN을 이용한 풀이

1단계: 수강 신청한 학생 찾기 (EXISTS). 제일 쉬운 조건부터 시작하자. 누가 최소 한 과목이라도 신청했는지 알아야 해. 여기서 EXISTS를 쓰면 돼.

SELECT name
FROM students s
WHERE EXISTS (
  SELECT 1
  FROM enrollments e
  WHERE e.student_id = s.id
);

1. 바깥 쿼리는 students 테이블에서 이름을 뽑아.
2. 서브쿼리에서는 enrollments 테이블에 해당 학생의 기록이 있는지 확인해 (WHERE e.student_id = s.id).
3. SELECT 1은 그냥 기록이 있으면 된다는 의미야, 실제 값은 필요 없어.

결과:

이제 누가 과목을 신청했는지 알았어. 근데 여기서 끝이 아니지. 점수가 없는 학생만 필터링하고 싶어.

2단계: 점수 없는 학생 찾기 (IN + NULL). 이제 필터링을 추가하자: 수강한 과목 중 적어도 하나는 점수가 없는 학생만 필요해. 여기서 IN과 NULL을 써야 해.

SELECT name
FROM students s
WHERE id IN (
  SELECT e.student_id
  FROM enrollments e
  WHERE e.grade IS NULL
);

1. 바깥 쿼리에서 학생 이름을 뽑아.
2. 서브쿼리는 enrollments 테이블에서 grade IS NULL인 student_id 리스트를 만들어.

결과:

Otto만 점수 없는 과목이 있네. 뭔가 드라마틱하지? 하지만 아직 끝난 게 아니야: 이제 평균 점수 80점 넘는 그룹만 고려해야 해.

HAVING을 이용한 풀이

3단계: 그룹화와 HAVING으로 필터링.

이제 모든 걸 합칠 차례야. 해야 할 일:

1. 각 그룹의 평균 점수 계산.
2. 평균 점수 80점 넘는 그룹만 필터링.
3. 이 그룹에 속한 학생 중 앞 조건도 만족하는 학생 뽑기.

SELECT name
FROM students s
WHERE s.group_id IN (
  SELECT group_id
  FROM students
  JOIN enrollments ON students.id = enrollments.student_id
  WHERE grade IS NOT NULL
  GROUP BY group_id
  HAVING AVG(grade) > 80
)
AND id IN (
  SELECT e.student_id
  FROM enrollments e
  WHERE e.grade IS NULL
);

1. 바깥 쿼리는 모든 조건을 만족하는 학생 이름을 뽑아.
2. WHERE 안 첫 번째 서브쿼리는 평균 점수 80점 넘는 그룹의 group_id 리스트를 반환해.
   • students와 enrollments를 조인해서 점수를 가져와.
   • grade IS NOT NULL인 것만 필터링.
   • group_id로 그룹화.
   • HAVING으로 그룹 필터링.
3. 두 번째 서브쿼리는 학생이 점수 없는 과목이 있는지 확인해.
4. 두 조건은 AND로 합쳐져 있어.

결과:

결국 Otto가 점수 없는 과목도 있고, 잘 나가는 그룹에도 속해 있다는 걸 알게 됐어.

접근법 비교: EXISTS vs IN

EXISTS는 기록이 있는지 빠르게 확인할 때 최고야. 첫 번째 기록만 찾으면 바로 멈추니까 큰 테이블에서 특히 효율적이야.

반면 IN은 데이터 내용에 집중할 때 좋아. 예를 들어, id 리스트를 뽑아서 필터링할 때. 근데 IN은 반환 값이 많아지면 느려질 수 있다는 점도 기억해.

HAVING을 언제 써야 할까?

집계 데이터에서 결과로 필터링해야 할 때 HAVING이 최고야. 근데 만약 WHERE로 조건을 옮길 수 있다면(예: 컬럼 값 필터링), 쿼리가 더 간단해지고 빨라질 수 있어.

전체 예시

복습 겸 하나 더 해보자: 점수 75점 미만인 학생이 한 명이라도 있지만, "철학" 과목은 수강하지 않은 그룹을 뽑아보자.

테이블 다시 한 번 확인:

students 테이블

courses 테이블

enrollments 테이블

SELECT DISTINCT group_id
FROM students s
WHERE group_id IN (
  SELECT s.group_id
  FROM students s
  JOIN enrollments e ON s.id = e.student_id
  WHERE e.grade < 75
)
AND group_id NOT IN (
  SELECT s.group_id                                 -- 1단계 서브쿼리
  FROM students s
  JOIN enrollments e ON s.id = e.student_id
  WHERE e.course_id = (
    SELECT id FROM courses WHERE name = '철학' -- 2단계 서브쿼리 :P
  )
);

1. 첫 번째 서브쿼리는 점수 75점 미만 학생이 있는 그룹을 뽑아.
2. 두 번째 서브쿼리는 "철학" 과목과 연결된 그룹을 제외해.
3. IN과 NOT IN을 조합해서 최종 결과를 만들어.

결과:

이게 얼마나 쓸모 있을까?

실제로 이런 방식은 데이터 간 복잡한 관계를 분석할 때 진짜 유용해. 예를 들면:

• 분석에서 "특별한" 고객 그룹(VIP, 문제 고객 등)을 뽑을 때.
• 추천 시스템 개발할 때, 여러 기준으로 유저를 필터링할 때.
• 면접에서 복잡한 SQL 쿼리 최적화하라고 할 때.

연습 많이 해! 이게 실력으로 가는 길이야.