Correlated Subqueries

상관 서브쿼리란 외부 쿼리의 컬럼을 참조하는 서브쿼리를 뜻한다. 비상관 서브쿼리와 달리, 상관 서브쿼리는 개념적으로는 외부 쿼리의 모든 행마다 다시 평가되는 것으로 동작한다. 이러한 동적 특성 덕분에 행 단위 비교, 필터링, 조건부 업데이트 등 다양한 문제에 활용된다.

다만 논리적으로 행마다 반복 평가되는 모델이므로, 옵티마이저가 이를 조인 형태로 풀어내지 못하거나 서브쿼리의 비교 컬럼에 적절한 인덱스가 없을 경우 성능상 불이익이 발생할 수 있다. 실제로 대부분의 현대 DB는 가능한 경우 상관 서브쿼리를 세미조인/안티조인 등으로 자동 변환(decorrelation)하여 실행한다.

사용사례

특정 Row의 조건에 따른 데이터 Fetching

상관관계 서브쿼리는 외부쿼리의 값 조건에 기반한 데이터를 비교하고 필터링할 때 사용할 수 있다.

SELECT last_name, salary, department_id
FROM employees AS outer
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = outer.department_id
);

UPDATE와 함께 사용하기

상관관계 서브쿼리를 이용해 다른 테이블의 데이터를 기반으로 UPDATE를 할 수 있다.

UPDATE employees
SET salary = ROUND(
    (SELECT AVG(salary)
     FROM employees
     WHERE department_id = employees.department_id), 2
)
WHERE department_id = 101;

DELETE와 함께 사용하기

DELETE FROM employees
WHERE employee_id IN (
    SELECT employee_id
    FROM employees
    WHERE department_id = 101
);

employees 테이블에서 departmemt_id가 101인 row가 삭제된다.

EXISTS와 함께 사용하기

EXIST 연산자는 상관관계 서브쿼리에서 자주 사용되는데, 서브쿼리가 행을 하나라도 반환하는지 테스트하기 위해 사용한다. 서브쿼리에 행이 최소 하나라도 있으면 TRUE를 반환한다.

SELECT e.employee_id, e.last_name, e.job_id, e.department_id
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM employees sub
    WHERE sub.manager_id = e.employee_id
);

현재 매니저로써 부하직원이 있는 사원의 정보를 가져오는 쿼리이다.

NOT EXISTS와 함께 사용하기

NOT EXISTS 연산자는 서브쿼리가 행을 하나도 반환하지 않는 것을 검색한다. 이는 특정 조건에 부합하지 않는 레코드를 찾을 때 유용하다.

SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

출처

https://www.geeksforgeeks.org/sql/sql-correlated-subqueries/

Correlated Subqueries (상관관계 서브쿼리)