서브쿼리와 EXISTS·IN·ANY·ALL

서브쿼리는 결과의 모양과 사용 문맥으로 읽는다

서브쿼리(subquery)는 다른 SQL 문 안에 괄호로 포함된 SELECT 문이다. 상위 질의가 서브쿼리의 결과를 한 값, 값의 집합, 행의 존재 여부, 테이블 형태 중 무엇으로 사용하는지부터 확인해야 한다. 같은 서브쿼리라도 놓인 위치와 연산자가 기대하는 결과 형태가 맞지 않으면 오류가 나거나 뜻이 달라진다.

단일 행 서브쿼리와 다중 행 서브쿼리는 결과 행 수에 따른 분류다. 비상관 서브쿼리와 상관 서브쿼리는 외부 질의 열을 참조하는지에 따른 분류다. 두 기준은 서로 독립적이므로, 상관 서브쿼리도 집계 함수를 사용하면 스칼라 결과를 만들 수 있고 비상관 서브쿼리도 여러 행을 반환할 수 있다.

아래 입력을 각 예제에서 사용한다. 별도 언급이 없으면 질의는 데이터를 변경하지 않는다.

부서는 (10,개발)·(20,기획)·(30,영업)·(40,법무)다. assignment(employee_id,project_code)는 (1,A)·(1,B)·(3,A)·(4,C), blocked_employee(employee_id)는 {2,NULL}이다. 기준 점수 benchmark_score(score)는 {60,80,NULL}, 후보 candidate_score(candidate_name,score)는 (A,50)·(B,70)·(C,90)이다. 직원 1은 프로젝트가 두 개이지만 한 직원이라는 점에 유의한다.

스칼라 서브쿼리: 한 값이 필요한 자리에 사용한다

스칼라 서브쿼리(scalar subquery)는 한 열에서 최대 한 행을 반환해 하나의 값처럼 사용되는 서브쿼리다. SELECT 목록, 비교식의 한쪽, 계산식 등 값 표현식이 허용되는 위치에 놓을 수 있다.

다음 질의의 내부 AVG는 전체 직원의 평균 급여 한 값을 반환한다. AVG는 NULL 급여를 제외하므로 평균은 (5000 + 7000 + 4000 + 6000 + 8000) ÷ 5 = 6000이다.

SELECT employee_id, employee_name, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee)
ORDER BY employee_id;

스칼라 서브쿼리의 행 수는 다음처럼 판정한다.

-- 조건에 맞는 행이 없으므로 스칼라 결과는 NULL이다.
SELECT (SELECT salary
        FROM employee
        WHERE employee_id = 999) AS missing_salary;

반면 다음 내부 질의는 개발 부서의 급여 5000, 7000 두 행을 반환하므로 =가 요구하는 한 값을 정할 수 없다.

SELECT employee_name
FROM employee
WHERE salary = (SELECT salary
                FROM employee
                WHERE department_id = 10);

정보처리기사에서 다루는 일반 규칙은 다중 행을 임의로 첫 행으로 선택하지 않고 오류로 처리한다는 것이다. 단일값을 구조적으로 보장하려면 기본키·UNIQUE 열에 대한 조건, 전체 집계, 또는 명시적인 행 제한을 사용한다. 다만 행 제한으로 한 행을 고를 때에는 ORDER BY로 어느 행을 선택할지 결정해야 한다. DISTINCT는 중복값만 제거할 뿐 서로 다른 값이 여러 개 남을 수 있으므로 단일 행을 보장하지 않는다.

=, <>, >, >=, <, <= 같은 일반 비교 연산자 한쪽에 서브쿼리를 직접 놓는다면 보통 한 값이 필요하다. 여러 행을 의도했다면 IN, ANY, ALL, EXISTS 중 자연어 조건에 맞는 연산자를 선택한다.

FROM 절의 서브쿼리는 파생 테이블이 된다

FROM 절의 서브쿼리는 파생 테이블(derived table), 또는 제품에 따라 인라인 뷰(inline view)라고 부른다. 스칼라 서브쿼리와 달리 여러 열과 여러 행을 반환할 수 있고, 상위 질의는 그 결과를 일반 테이블처럼 조인하거나 필터링한다.

SELECT d.department_name,
       x.employee_count,
       x.avg_salary
FROM (
    SELECT department_id,
           COUNT(*) AS employee_count,
           AVG(salary) AS avg_salary
    FROM employee
    WHERE department_id IS NOT NULL
    GROUP BY department_id
) AS x
JOIN department AS d
  ON d.department_id = x.department_id
ORDER BY d.department_id;

영업 부서는 직원 행이 1개이므로 COUNT(*)는 1이지만, 유일한 급여가 NULL이어서 AVG(salary)는 NULL이다. 파생 테이블에는 상위 질의에서 참조할 별칭을 붙이는 것이 이식성과 가독성에 유리하다.

상관 서브쿼리: 외부 질의의 현재 행을 참조한다

상관 서브쿼리(correlated subquery)는 내부 질의가 외부 질의의 열을 참조한다. 논리적으로는 외부 후보 행 하나가 정해질 때마다 그 값이 내부 질의의 조건에 전달된다. 따라서 내부 질의만 떼어 실행하면 외부 별칭을 알 수 없어 완전한 의미를 갖지 못한다.

다음 질의는 각 직원의 급여를 그 직원이 속한 부서의 평균 급여와 비교한다.

SELECT e.employee_id, e.employee_name, e.salary
FROM employee AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employee AS e2
    WHERE e2.department_id = e.department_id
)
ORDER BY e.employee_id;

개발 부서 평균은 6000이므로 지수만 남고, 기획 부서 평균은 5000이므로 서연만 남는다. 하늘의 급여는 NULL이어서 비교 결과가 UNKNOWN이다. 도윤의 department_id도 NULL인데, 일반 등호에서 NULL = NULL은 TRUE가 아니므로 내부 평균은 NULL이 되고 외부 비교도 TRUE가 되지 않는다.

상관 서브쿼리는 논리적으로 외부 행마다 평가한다고 이해하면 결과를 추적하기 쉽다. 그러나 실제로 내부 질의가 물리적으로 반드시 외부 행 수만큼 반복 실행된다고 단정하면 안 된다. 옵티마이저는 의미가 같다면 조인, 세미 조인, 안티 조인 또는 다른 계획으로 변환할 수 있다. 성능은 상관 서브쿼리라서 항상 느리다, 조인이라서 항상 빠르다처럼 문법만으로 판정하지 않고 실행계획과 데이터 분포로 확인한다.

외부와 내부에 같은 열 이름이 있으면 별칭을 모두 명시한다. 다음 두 참조의 역할은 다르다.

e.department_id   : 지금 판정 중인 외부 직원의 부서

e2.department_id  : 내부에서 평균을 계산할 직원들의 부서

EXISTS: 반환값이 아니라 행의 존재를 검사한다

EXISTS (subquery)는 내부 질의가 한 행이라도 반환하면 TRUE, 한 행도 반환하지 않으면 FALSE다. 내부 SELECT 목록의 실제 값은 일반적인 존재 판정에 사용되지 않으므로 SELECT 1, SELECT NULL, 열 이름 등을 쓸 수 있다. SELECT 1은 존재 검사의 의도를 드러내는 관례일 뿐 숫자 1을 비교하는 것이 아니다.

다음 질의는 프로젝트 배정이 하나 이상 있는 직원을 찾는다.

SELECT e.employee_id, e.employee_name
FROM employee AS e
WHERE EXISTS (
    SELECT 1
    FROM assignment AS a
    WHERE a.employee_id = e.employee_id
)
ORDER BY e.employee_id;

민수는 배정 행이 2개지만 외부 직원 행은 한 번만 반환된다. EXISTS는 내부에서 몇 행이 일치하는지가 아니라 최소 한 행이 있는지만 판정하기 때문이다. 같은 관계를 일반 INNER JOIN으로 작성하면 민수 행이 프로젝트 수만큼 반복될 수 있다.

SELECT e.employee_id, e.employee_name, a.project_code
FROM employee AS e
JOIN assignment AS a
  ON a.employee_id = e.employee_id
ORDER BY e.employee_id, a.project_code;

NOT EXISTS는 조건을 만족하는 내부 행이 하나도 없을 때 TRUE다. 부모 행에 대응하는 자식 행이 없는지 찾는 안티 존재 검사에 적합하다.

IN: 값이 한 열의 결과 집합에 속하는지 비교한다

x IN (subquery)는 서브쿼리가 반환한 한 열의 값 중 x와 같은 값이 하나라도 있는지 판정한다. 서브쿼리는 0개 이상의 행을 반환할 수 있지만, 스칼라 왼쪽 식과 비교하는 기본 형태에서는 정확히 한 열을 반환해야 한다.

SELECT e.employee_id, e.employee_name
FROM employee AS e
WHERE e.employee_id IN (
    SELECT a.employee_id
    FROM assignment AS a
)
ORDER BY e.employee_id;

이 예제에서는 employee_id가 양쪽에서 NULL이 아니므로 앞의 EXISTS 질의와 같은 직원 1·3·4를 반환한다. 배정 테이블에 직원 1이 두 번 있어도 IN의 TRUE·FALSE·UNKNOWN 판정은 달라지지 않는다.

IN은 = ANY와 같은 양화 의미를 갖는다. 결과 집합 S에 대해 다음처럼 읽는다.

x IN S  ↔  x = ANY S  ↔  S 안에 x와 같은 값이 적어도 하나 있다

다만 EXISTS와 IN이 비슷한 행을 반환하는 사례가 많다고 해서 언제나 같은 불리언 값을 갖는 것은 아니다. 특히 NULL이 관여하면 IN은 UNKNOWN이 될 수 있지만, EXISTS는 조건을 만족하는 행이 없으면 FALSE다. WHERE에서는 FALSE와 UNKNOWN이 모두 제거되어 우연히 같은 행 집합처럼 보일 수 있다.

NOT IN의 NULL 함정과 NOT EXISTS

x NOT IN (subquery)는 x <> ALL (subquery)와 같다. 즉, 결과의 모든 값과 달라야 TRUE다. 결과 집합에 NULL이 있으면 x <> NULL이 UNKNOWN이므로, 일치값이 없더라도 전체 조건이 TRUE로 확정되지 않을 수 있다.

예제의 차단 목록에는 직원 2와 NULL이 들어 있다.

SELECT employee_id, employee_name
FROM employee
WHERE employee_id NOT IN (
    SELECT employee_id
    FROM blocked_employee
)
ORDER BY employee_id;

이 질의는 0행을 반환한다.

• 직원 2: 2 <> 2가 FALSE이므로 제외된다.
• 직원 1·3·4·5·6: 차단된 2와는 다르지만 employee_id <> NULL이 UNKNOWN이다.
• WHERE는 TRUE인 행만 남기므로 FALSE와 UNKNOWN 모두 제거된다.

같은 업무 의도가 “현재 직원과 같은 차단 행이 존재하지 않는다”라면 다음처럼 쓸 수 있다.

SELECT e.employee_id, e.employee_name
FROM employee AS e
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_employee AS b
    WHERE b.employee_id = e.employee_id
)
ORDER BY e.employee_id;

또는 NOT IN을 유지해야 한다면 비교 대상에서 NULL을 명시적으로 제외할 수 있다.

SELECT employee_id, employee_name
FROM employee
WHERE employee_id NOT IN (
    SELECT employee_id
    FROM blocked_employee
    WHERE employee_id IS NOT NULL
)
ORDER BY employee_id;

이 경우에도 1·3·4·5·6이 반환된다. 다만 다음 조건을 함께 확인해야 한다.

• 서브쿼리 열에 NOT NULL 제약이 있는가, 아니면 NULL 제거 조건이 있는가?
• 외부 비교값 자체가 NULL일 수 있는가?
• 외부 NULL을 “목록에 없는 값”으로 포함할지 제외할지 업무 규칙이 정해졌는가?

따라서 NOT EXISTS가 언제나 NOT IN과 완전히 같다거나 NOT EXISTS가 무조건 더 빠르다고 단정하지 않는다. NULL 의미를 먼저 맞춘 뒤, 성능은 실행계획으로 확인한다.

ANY·SOME: 비교가 하나라도 TRUE이면 TRUE다

ANY는 왼쪽 값과 서브쿼리의 각 값을 지정된 비교 연산자로 비교해 하나라도 TRUE이면 TRUE가 된다. SOME은 ANY의 동의어다.

expression comparison_operator ANY (subquery)
expression comparison_operator SOME (subquery)

NULL을 제외한 기준 점수 집합이 {60, 80}일 때 점수 > ANY(S)는 “기준 중 적어도 하나보다 높다”는 뜻이다.

SELECT candidate_name, score
FROM candidate_score
WHERE score > ANY (
    SELECT score
    FROM benchmark_score
    WHERE score IS NOT NULL
)
ORDER BY score;

결과는 B(70), C(90)다. B의 70은 60보다 높으므로 다른 비교가 FALSE여도 ANY 전체는 TRUE다.

ALL: 모든 비교가 TRUE여야 TRUE다

ALL은 왼쪽 값과 서브쿼리의 모든 값을 비교해 모든 비교가 TRUE일 때 TRUE가 된다. 하나라도 FALSE가 있으면 즉시 FALSE다.

SELECT candidate_name, score
FROM candidate_score
WHERE score > ALL (
    SELECT score
    FROM benchmark_score
    WHERE score IS NOT NULL
)
ORDER BY score;

결과는 C(90)만 남는다. 90은 60과 80보다 모두 크지만, 70은 80보다 크지 않다.

비교 연산자의 방향까지 함께 읽는다

NULL이 없고 비어 있지 않은 집합 S에서는 다음과 같이 최솟값·최댓값으로 의미를 확인할 수 있다.

이 표는 집합이 비어 있지 않고 NULL이 없을 때 의미를 확인하는 보조 규칙이다. 집계 함수 MIN·MAX는 NULL을 무시하고 빈 입력에서는 NULL을 반환하므로, NULL이나 빈 집합이 있을 때 ANY·ALL을 무조건 MIN·MAX로 바꾸면 3값 논리 결과가 달라질 수 있다.

특히 다음 두 오답을 주의한다.

• x <> ANY(S)는 “적어도 한 값과 다르다”이므로 NOT IN이 아니다. NOT IN은 x <> ALL(S)다.
• x = ALL(S)는 “모든 값이 x와 같다”는 뜻이므로 IN이 아니다. IN은 x = ANY(S)다.

ANY·ALL에서 NULL과 빈 집합을 판정하는 법

ANY와 ALL은 각 비교 결과를 TRUE·FALSE·UNKNOWN으로 만든 뒤 양화 규칙을 적용한다.

기준 집합이 {60, 80, NULL}일 때 > 비교 결과는 다음과 같다.

빈 집합은 비교할 행이 하나도 없는 경우다.

70 > ANY(empty set)  → FALSE
70 > ALL(empty set)  → TRUE

ANY는 TRUE를 만들어 줄 대상이 하나도 없으므로 FALSE다. ALL은 조건을 깨뜨리는 반례가 하나도 없으므로 TRUE다. 같은 원리로 빈 집합에 대한 IN은 FALSE, NOT IN은 TRUE, EXISTS는 FALSE, NOT EXISTS는 TRUE다.

NULL이 들어 있는 문제에서는 최종 조건이 UNKNOWN인지 반드시 확인한다. WHERE와 HAVING은 TRUE인 행이나 그룹만 남기므로 UNKNOWN은 FALSE처럼 결과에서 제외되지만, 불리언 식 자체의 값은 FALSE와 다르다.

좌우로 이동해 그림을 확인하세요.그림 크게 보기

ANY·ALL의 NULL과 빈 집합

빈 집합에서 ANY는 FALSE, ALL은 TRUE다. ANY는 TRUE가 하나라도 있으면 TRUE, ALL은 FALSE가 하나라도 있으면 FALSE다. 결정값이 없고 NULL 비교가 남으면 UNKNOWN이다. 나머지는 ANY=FALSE·ALL=TRUE다. WHERE는 TRUE만 통과시킨다.

모든 조건을 충족하는 대상과 NULL 후보

'모든 필수 과목을 이수한 학생'은 그 학생이 이수하지 않은 필수 과목이 존재하지 않는다고 바꾸어 표현할 수 있다. 바깥 NOT EXISTS는 미충족 필수 과목을 찾고, 안쪽 NOT EXISTS는 해당 학생과 과목의 이수 행이 없음을 찾는다. 후보 학생의 범위와 필수 과목 집합을 함께 확인한다. 필수 과목 집합이 비어 있으면 미충족 과목도 없으므로 바깥 후보 집합 전체가 조건을 만족할 수 있다.

NOT IN을 NOT EXISTS로 바꿀 때 내부 NULL만이 아니라 외부 비교 값의 NULL도 확인한다. 외부 값이 NULL이고 내부 집합이 비어 있지 않다면 일반 NOT IN은 TRUE가 되지 않는 반면, 내부에서 일반 등호로 상관시킨 NOT EXISTS는 일치 행이 없어 TRUE가 될 수 있다. 두 표현이 모든 입력에서 동등하다고 단정하지 않는다.

PostgreSQL의 단일 값 문맥에서는 스칼라 서브쿼리가 0행이면 NULL, 1행·1열이면 그 값, 두 행 이상이면 오류다. 일부 다른 DBMS의 예외적인 처리를 이 규칙의 실행 증거로 사용하지 않는다.

상관 서브쿼리는 SELECT에만 쓰는 것이 아니다. UPDATE의 SET에서 현재 수정 중인 행의 키를 참조하여 다른 테이블의 대응 값을 읽을 수도 있다. 반환값이 하나인지, 대응 행이 없을 때 NULL이 허용되는지, 수정 대상 조건이 맞는지를 함께 검토한다.