프로그래머스 고득점 Kit와 코딩테스트 SQL 문제를 풀다 서브쿼리가 헷갈리는 경우가 많아 이번 기회에 서브쿼리를 정리하고자 한다.

서브쿼리란?

• 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문!
• 반드시 괄호로 묶어야 한다!
  EX) SELECT 안에 SELECT 문, INSERT, UPDATE, DELETE 안의 SELECT문

서브쿼리 사용 가능한 곳

1. SELECT절
2. FROM절
3. WHERE절
4. HAVING절
5. ORDER BY절
6. 기타 DML (INSERT, DELETE, UPDATE)절

다만 GROUP BY절에는 서브쿼리를 사용할 수 없다.

서브 쿼리 종류

1. 동작하는 방식에 따라

1) UN-CORRELATED(비연관) 서브쿼리

• 서브쿼리가 메인쿼리 컬럼을 가지고 있지 않은 형태의 서브쿼리
• 메인쿼리에 서브쿼리가 실행된 결과 값을 제공하기 위한 목적으로 사용

2) CORRELATED(연관) 서브쿼리

• 서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태의 서브 쿼리
• 일반적으로 메인쿼리가 먼저 수행된 후에 서브쿼리에서 조건이 맞는지 확인하고자 할 때 사용

2. 위치에 따라

1) 스칼라 서브쿼리 (Scalar Subquery)

• SELECT절에 사용하는 서브쿼리
• 서브쿼리 결과를 마치 **하나의 컬럼처럼 사용하기 위해 주로 사용
• 단일값을 반환 (값을 계산), 즉 각 행마다 스칼라 서브쿼리 결과가 하나여야 한다. (단일행 서브쿼리 형태)

SELECT 
    emp_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary,
    (SELECT dept_name FROM departments d WHERE d.dept_id = e.dept_id) AS dept_name
FROM employees e;

2) 인라인 뷰 (Inline View)

• FROM절에 사용하는 서브쿼리
• 서브쿼리 결과를 테이블처럼 사용하기 위해 주로 사용 (임시 테이블처럼 사용)
• 데이터 가공 역할
• 테이블명이 존재하지 않기 때문에 다른 테이블과 조인 시 반드시 테이블 별칭 명시 (단독으로 사용하는 경우 불필요)
• WHERE절 서브쿼리와 다르게 서브쿼리 결과를 메인 쿼리의 어느 절에서도 사용할 수 있다.
• 인라인뷰의 결과와 메인쿼리 테이블과 조인할 목적으로 주로 사용
• 모든 연산자 사용 가능

SELECT 
    dept_summary.dept_id,
    dept_summary.avg_salary,
    d.dept_name
FROM (
    SELECT 
        dept_id,
        AVG(salary) as avg_salary,
        COUNT(*) as emp_count
    FROM employees 
    GROUP BY dept_id
) dept_summary
JOIN departments d ON dept_summary.dept_id = d.dept_id
WHERE dept_summary.avg_salary > 5000;

3) WHERE절 서브쿼리

• 가장 일반적인 서브쿼리
• 비교 상수 자리에 값을 전달하기 위한 목적으로 주로 사용(상수항의 대체
• 리턴 데이터의 형태에 따라 단일행 서브쿼리, 다중행 서브쿼리, 다중컬럼 서브쿼리, 상호연관 서브쿼리로 구분
• 필터링 조건으로 사용 (조건 설정)

WHERE절 서브쿼리 종류

1) 단일행 서브쿼리 (Single Row Subquery)

• 서브쿼리 결과가 1개의 행이 리턴되는 형태
• 단일행 서브쿼리 연산자 종류
• =, >, <, >=, <=, <> (같지 않다)

-- 평균 급여보다 높은 급여를 받는 직원 
SELECT emp_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 최고 급여를 받는 직원
SELECT emp_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

2) 다중행 서브쿼리 (Multiple Row Subquery)

• 서브쿼리 결과가 여러 행이 리턴되는 형태 (여러 개의 행 반환)
• IN, ANY, ALL, EXISTS 연산자
• 다중행 서브쿼리 연산자

연산자	의미
IN	같은 값을 찾음
> ANY	최솟값을 반환
< ANY	최댓값을 반환
< ALL	최솟값을 반환
> ALL	최댓값을 반환

• 예시

  ALL(2000, 3000) : 최대값(3000)보다 큰 행들 반환
  < ALL(2000, 3000) : 최소값(2000)보다 작은 행들 반환
  ANY(2000, 3000) : 최소값(2000)보다 큰 행들 반환
  < ANY(2000, 3000) : 최대값(3000)보다 작은 행들 반환

-- IN 사용: 서울에 있는 부서의 직원들
SELECT emp_name, dept_id
FROM employees
WHERE dept_id IN (
    SELECT dept_id 
    FROM departments 
    WHERE location = '서울'
);

-- ANY 사용: 어떤 부서의 평균급여보다라도 높은 급여를 받는 직원
SELECT emp_name, salary
FROM employees
WHERE salary > ANY (
    SELECT AVG(salary) 
    FROM employees 
    GROUP BY dept_id
);

-- ALL 사용: 모든 부서의 평균급여보다 높은 급여를 받는 직원
SELECT emp_name, salary
FROM employees
WHERE salary > ALL (
    SELECT AVG(salary) 
    FROM employees 
    GROUP BY dept_id
);

3) 다중컬럼 서브쿼리 (Multiple Column Subquery)

• 여러 개의 컬럼을 반환하여 동시에 비교
• 메인쿼리와의 비교 컬럼이 2개 이상
• 대소 비교 전달 불가 (두 값을 동시에 묶어 비교 x)

-- 각 부서별 최고급여와 최소급여를 동시에 받는 직원
SELECT emp_name, dept_id, salary
FROM employees
WHERE (dept_id, salary) IN (
    SELECT dept_id, MAX(salary)
    FROM employees
    GROUP BY dept_id
    UNION
    SELECT dept_id, MIN(salary)
    FROM employees
    GROUP BY dept_id
);

-- 특정 부서의 특정 직급과 일치하는 직원
SELECT emp_name, dept_id, job_title
FROM employees
WHERE (dept_id, job_title) = (
    SELECT dept_id, job_title
    FROM employees
    WHERE emp_name = '김철수'
);

4) 상호연관 서브쿼리 (Correlated Subquery)

• 메인쿼리와 서브쿼리의 비교를 수행하는 형태
• 비교할 집단이나 조건은 서브쿼리에 명시 (메인쿼리절에는 서브쿼리 컬럼이 정의되지 않았기 때문에 에러 발생)
• 외부쿼리의 컬럼을 참조하는 서브쿼리. 외부 쿼리의 각 행마다 서브쿼리가 실행된다.

-- 같은 부서 내에서 평균급여보다 높은 급여를 받는 직원
SELECT emp_name, dept_id, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id  -- 외부 쿼리 참조
);

-- EXISTS 사용: 부하직원이 있는 매니저
SELECT emp_name, emp_id
FROM employees manager
WHERE EXISTS (
    SELECT 1
    FROM employees subordinate
    WHERE subordinate.manager_id = manager.emp_id  -- 외부 쿼리 참조
);

-- NOT EXISTS 사용: 부하직원이 없는 직원
SELECT emp_name, emp_id
FROM employees e1
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e2
    WHERE e2.manager_id = e1.emp_id
);

• 대소 비교할 컬럼을 메인쿼리에, 일치 조건을 서브쿼리에 전달

SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP E1
WHERE SALARY > (SELECT AVG(SALARY)
				FROM EMP E2
                WHERE E1.DEPTNO = E2.DEPTNO
                GROUP BY DEPTNO);

서브쿼리 주의사항

• 특별한 경우 (TOP-N 분석 등)을 제외하고는 서브 쿼리절에 ORDER BY절을 사용할 수 없다.
• 단일 행 서브쿼리와 다중 행 서브쿼리에 따라 연산자의 선택이 중요하다.

SELECT *
FROM EMP
WHERE SAL IN (SELECT SALARY
			  FROM EMP
              WHERE DEPTNUM = 20
              ORDER BY SALARY);

위와 같이 작성하면 에러가 발생한다.