SQL Advanced: 중급 서브쿼리

하하호호훈이·2025년 5월 30일

RDBMS Sub Query mysql sql

SQL(MySQL) MUST KNOW

목록 보기

14/23

이번 포스팅에서는 서브쿼리를 좀 더 딥하게 다뤄보겠습니다!

다루는 주제:

기본 서브쿼리
GROUP BY가 있는 서브쿼리
최대값을 가진 행 찾기
상관 서브쿼리 (Correlated Subquery)

💡 모든 예시는 company_db의 employees, projects 테이블을 기반으로 작성됩니다. 혹시 까먹었을 수도 있을 거 같아서 다시 정의해요!

🏗️ 예시 테이블 구조 및 데이터

-- employees 테이블
CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10,2)
);

-- projects 테이블
CREATE TABLE projects (
  project_id INT PRIMARY KEY,
  emp_id INT,
  project_name VARCHAR(100),
  budget DECIMAL(10,2),
  FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

-- 샘플 데이터
INSERT INTO employees VALUES
(1, 'Alice', 'Engineering', 6500.50),
(2, 'Bob', 'Marketing', 5400.00),
(3, 'Charlie', 'Engineering', 7100.00),
(4, 'Diana', 'HR', 4300.75);

INSERT INTO projects VALUES
(101, 1, 'AI Platform', 100000.00),
(102, 2, 'Rebranding', 40000.00),
(103, 1, 'Data Lake', 80000.00),
(104, 3, 'Automation', 60000.00);

1. 기본 서브쿼리 — 평균보다 급여가 높은 직원 조회

SELECT first_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees
);

예시 결과:

first_name	salary
Alice	6500.50
Charlie	7100.00

2. GROUP BY가 있는 서브쿼리 — 평균 프로젝트 수보다 더 많은 프로젝트를 가진 직원

SELECT emp_id, first_name
FROM employees
WHERE emp_id IN (
  SELECT emp_id
  FROM projects
  GROUP BY emp_id
  HAVING COUNT(*) > (
    SELECT AVG(project_count)
    FROM (
      SELECT COUNT(*) AS project_count
      FROM projects
      GROUP BY emp_id
    ) AS sub_counts
  )
);

예시 결과:

emp_id	first_name
1	Alice

3. 최대값을 가진 행 찾기 — 가장 많은 프로젝트를 수행한 직원

SELECT e.first_name, e.department
FROM employees e
WHERE e.emp_id = (
  SELECT emp_id
  FROM (
    SELECT emp_id, COUNT(*) AS proj_count
    FROM projects
    GROUP BY emp_id
    ORDER BY proj_count DESC
    LIMIT 1
  ) AS top_emp
);

예시 결과:

first_name	department
Alice	Engineering

4. 상관 서브쿼리 — 각 직원의 평균 프로젝트 예산보다 높은 예산 프로젝트만 보기

SELECT p.project_name, p.emp_id, p.budget
FROM projects p
WHERE p.budget > (
  SELECT AVG(budget)
  FROM projects
  WHERE emp_id = p.emp_id
);

*이 쿼리에서는 각 프로젝트에 대해 해당 프로젝트를 담당한 직원의 평균 프로젝트 예산을 계산하고, 그 평균보다 예산이 큰 프로젝트만을 조회합니다. 이런 종류의 서브쿼리를 상관 서브쿼리(Correlated Subquery) 라고 합니다. 왜냐하면 서브쿼리가 외부 쿼리의 emp_id 값을 참조하여 매번 다른 기준으로 실행되기 때문입니다.

예시 결과:

project_name	emp_id	budget
AI Platform	1	100000.00

✅ 정리 요약: 서브쿼리 활용법 비교

유형	설명	예시 활용
기본 서브쿼리	집계 결과 등 단일 값을 기준으로 비교	평균 급여보다 높은 직원
GROUP BY 서브쿼리	그룹 평균과 비교하거나 그룹 통계를 기준으로 조건 필터링	프로젝트 많은 직원 찾기
MAX값 찾기	정렬 + LIMIT으로 최대값 조건 추출	가장 많이 프로젝트를 수행한 직원
상관 서브쿼리	서브쿼리가 외부 쿼리의 값을 참조하며 반복적으로 실행됨	직원별 평균보다 큰 프로젝트 예산