MySQL 문법 및 예제 정리 - 서브쿼리, IN 응용

SQL에서 서브쿼리(Subquery)는 하나의 쿼리 안에 중첩된 또 다른 쿼리로, 복잡한 조건 필터링과 다단계 데이터 조회를 가능하게 한다. 이 글은 기본 개념부터 IN 응용까지 실전 예제 중심으로 정리한다.

서브쿼리(Subquery)

• 서브쿼리(Subquery)는 쿼리 내에 포함된 또 다른 쿼리를 의미한다. 이는 하나의 SQL 문장 안에서, 다른 쿼리의 결과를 참조하거나 사용하기 위해 작성된다. 서브쿼리는 일반적으로 SELECT, INSERT, UPDATE, DELETE 문에서 사용되며, 주 쿼리(Main Query)에 필요한 데이터를 제공하는 역할을 한다.

• 서브쿼리의 특징

  1. 위치: 서브쿼리는 주로 SELECT, FROM, WHERE, HAVING 절 내에서 사용된다.
  2. 독립성: 서브쿼리는 독립적인 SQL 문장으로 실행될 수 있다. 하지만 주 쿼리와 결합될 때, 특정 조건을 만족하는 데이터만 반환한다.
  3. 중첩 구조: 주 쿼리 안에 중첩된 형태로 존재하며, 주 쿼리의 일부로 사용된다.
• 서브쿼리의 활용
  • 필터링: 주 쿼리에서 반환할 데이터를 특정하기 위해 서브쿼리에서 데이터를 필터링한다.
  • 계산: 주 쿼리에서 직접 계산하기 어려운 값을 서브쿼리에서 계산하여 반환한다.
  • 데이터 추출: 주 쿼리에서 필요한 데이터를 서브쿼리를 통해 가져온다.

서브쿼리의 종류

1. 단일 행 서브쿼리

   • 하나의 결과만 반환하는 서브쿼리

     # 예: 특정 부서의 최대 급여를 찾을 때
     SELECT employee_name 
     FROM Employees 
     WHERE salary = (SELECT MAX(salary) FROM Employees WHERE department_id = 1);
     

2. 다중 행 서브쿼리
   • 여러 행의 결과를 반환하는 서브쿼리

     # 특정 부서에 속한 모든 직원의 이름을 찾을 때
     SELECT employee_name 
     FROM Employees 
     WHERE department_id IN (SELECT department_id FROM Departments WHERE location = 'New York');
     

3. 다중 열 서브쿼리
   • 여러 열을 반환하는 서브쿼리. 여러 행이 나올 수 있으므로 행 비교(=) 대신 IN과 함께 쓴다.

     # 각 부서의 최대 급여를 받는 직원을 찾을 때
     SELECT employee_name 
     FROM Employees 
     WHERE (department_id, salary) IN (SELECT department_id, MAX(salary) FROM Employees GROUP BY department_id);
     

4. 상관 서브쿼리
   • 주 쿼리의 각 행에 대해 서브쿼리가 실행되는 형태

     # 각 직원의 급여가 그 직원이 속한 부서의 평균 급여보다 높은지 확인할 때
     SELECT employee_name 
     FROM Employees e 
     WHERE salary > (SELECT AVG(salary) FROM Employees WHERE department_id = e.department_id);
     

서브 쿼리 - IN

• IN 연산자는 SQL에서 값이 특정 목록이나 서브쿼리의 결과 집합에 포함되는지 여부를 확인할 때 사용된다.
• 이를 통해 여러 값을 한 번에 비교하거나, 서브쿼리를 사용하여 동적으로 값을 확인할 수 있다.

IN 연산자의 기본 문법

column_name IN (value1, value2, ..., valueN)

• column_name: 비교할 컬럼 또는 표현식
• value1, value2, ..., valueN: 비교할 값 목록이다. 목록은 정수, 문자열, 또는 서브쿼리의 결과로 구성될 수 있다.

사용 목적

1. 여러 값에 대해 비교:
   • IN 연산자는 다수의 값에 대해 한 번에 조건을 검사할 수 있으므로, 반복적인 OR 조건문을 사용하지 않아도 된다.
2. 서브쿼리 결과와 비교:
   • IN 연산자는 서브쿼리와 결합하여, 동적으로 생성된 값 집합과 비교할 수 있다.

기본 예제

SELECT *
FROM Employees
WHERE department_id IN (1, 2, 3);

• 설명: department_id가 1, 2, 3 중 하나일 경우 해당 행을 반환한다.

IN과 서브쿼리

• IN은 서브쿼리와 결합하여 동적으로 값을 비교할 수 있다. 서브쿼리는 다른 테이블이나 쿼리의 결과를 반환하는 데 사용되며, 동적인 값 집합과 비교할 때 유용하다.

• 서브쿼리 예제:

# 설명: `Employees` 테이블에서 `department_id`가 **'Sales' 부서**에 속한 직원만 반환한다.
SELECT *
FROM Employees
WHERE department_id IN (
    SELECT department_id
    FROM Departments
    WHERE department_name = 'Sales'
);

NOT IN 연산자

• NOT IN은 IN과 반대로, 값이 목록에 포함되지 않은 경우 해당 행을 반환한다.

NOT IN 예제:

# 설명: `department_id`가 **1, 2, 3이 아닌** 모든 행을 반환한다.
SELECT *
FROM Employees
WHERE department_id NOT IN (1, 2, 3);

다중 컬럼에 IN 사용

• SQL에서는 튜플 비교를 사용하여 다중 컬럼에 대해 IN 조건을 사용할 수 있다. 이를 통해 여러 컬럼을 함께 비교할 수 있다.
• 다중 컬럼 IN 예제:

# 설명: `Employees` 테이블에서 `department_id`와 `job_id`의 조합이 `(1, 'IT_PROG')` 또는 `(2, 'SA_REP')`인 직원들을 반환한다.
SELECT *
FROM Employees
WHERE (department_id, job_id) IN (
    (1, 'IT_PROG'),
    (2, 'SA_REP')
);

주의: 서브쿼리를 사용하여 다중 컬럼을 비교하려면 튜플 형식으로 반환해야 한다.

---

성능 고려사항

아래 내용은 MySQL 8.0 기준이며, 실제 비용은 인덱스 구성과 옵티마이저가 고르는 실행 계획에 따라 달라진다. 따라서 결론은 추측이 아니라 EXPLAIN으로 확인하는 편이 좋다.

1. 대상 목록 크기:
   • 소규모 목록: 값이 몇 개 되지 않는 상수 목록이라면 인덱스 탐색이나 정렬된 값 목록에 대한 이진 탐색으로 처리될 수 있어 부담이 작다.
   • 대규모 목록: 목록이 커질수록 비교 비용이 늘고, 옵티마이저가 인덱스를 포기하고 풀 스캔을 고를 수도 있다. 같은 조건이라면 조인으로 바꾼 형태와 EXPLAIN 결과를 비교해 더 나은 쪽을 택한다.
2. 서브쿼리 성능:
   • IN/EXISTS 서브쿼리는 옵티마이저가 세미조인 변환(Semijoin)이나 구체화(Materialization)로 다시 쓰는 경우가 있어, 서브쿼리 형태라고 해서 항상 느린 것은 아니다. 다만 서브쿼리가 반환하는 행이 많거나 옵티마이저가 변환을 포기하면 비용이 커질 수 있으므로, 조인으로 다시 쓴 형태와 실행 계획을 비교해 본다.

한계와 주의사항

• NOT IN과 NULL: MySQL 문서에 따르면 IN()은 왼쪽 값이 NULL이거나, 목록(서브쿼리 결과 포함)에 NULL이 있는데 일치하는 값이 없으면 NULL을 반환한다. NOT IN은 NOT (IN)이므로 서브쿼리 결과에 NULL이 하나라도 섞이면 조건이 참이 되는 행이 하나도 없을 수 있다. NOT IN을 쓸 때는 서브쿼리에 WHERE col IS NOT NULL 같은 조건을 붙여 NULL을 먼저 걸러내는 편이 안전하다.
• 행 비교(=)와 다중 행 서브쿼리: (a, b) = (SELECT ...) 형태는 서브쿼리가 정확히 한 행을 반환할 때만 성립한다. 두 행 이상을 반환하면 Subquery returns more than 1 row 오류가 나므로, 여러 행을 비교할 때는 IN이나 EXISTS를 쓴다.
• 목록 크기 상한: IN 목록에 넣을 수 있는 값의 개수는 별도 상수가 아니라 max_allowed_packet 값으로 제한된다.
• LIMIT 사용 제한: IN/ALL/ANY/SOME 서브쿼리 안에서는 LIMIT을 쓸 수 없다. (ERROR 1235 ... This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery')
• FROM 절 서브쿼리와 수정문: MySQL 8.0.14 이전에는 FROM 절 서브쿼리(파생 테이블)를 상관 서브쿼리로 쓸 수 없었다. 또 단일 테이블을 수정하는 UPDATE/DELETE에 서브쿼리를 쓰면 옵티마이저가 세미조인·구체화 최적화를 적용하지 않으므로, 조인을 쓰는 다중 테이블 문장으로 다시 쓰는 것이 우회책으로 문서에 안내되어 있다.

References

• MySQL 8.0 Reference Manual — Subqueries
• MySQL 8.0 Reference Manual — Comparison Functions and Operators (IN(), NOT IN())
• MySQL 8.0 Reference Manual — Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions
• MySQL 8.0 Reference Manual — Restrictions on Subqueries