SQL 서브쿼리와 집합 연산 - EXISTS vs IN, UNION INTERSECT EXCEPT (SQL 중급 8단원)

이 단원에서 배우는 것

7단원에서는 여러 테이블을 옆으로 붙이는 조인을 다뤘고, 마지막 트랩에서 FROM 절 안에 SELECT 를 넣는 형태를 슬쩍 썼다. 조인은 "행을 옆으로 넓히는" 연산이라 "자기 부서 평균보다 많이 받는 사원"처럼 집계 결과를 다시 조건으로 쓰는 요구에는 잘 맞지 않는다. 이번 단원은 쿼리 안에 쿼리를 넣는 서브쿼리와, 결과 집합을 위아래로 합치는 집합 연산을 다룬다. 테이블은 7단원의 dept · emp · proj 를 그대로 쓴다.

• 스칼라 서브쿼리 · 인라인 뷰 · 상관 서브쿼리를 놓는 자리와 비용 차이를 안다
• EXISTS 와 IN 을 언제 바꿔 쓸 수 있고, NOT IN 이 언제 조용히 빈 결과를 내는지 안다
• UNION · UNION ALL · INTERSECT · EXCEPT 를 구분하고 불필요한 정렬 비용을 없앤다

개념

SQL 은 결과가 다시 테이블인 언어다. SELECT 한 덩어리를 괄호로 감싸면, 그 자리에 값 하나가 필요하면 값처럼, 테이블이 필요하면 테이블처럼 끼워 넣을 수 있다. 어디에 놓느냐에 따라 부르는 이름이 다를 뿐 문법은 같다.

• 스칼라 서브쿼리 — SELECT 목록이나 WHERE 우변에 놓여 값 하나를 돌려준다. 두 행이 나오면 런타임 에러다.
• 인라인 뷰 — FROM 절에 놓여 테이블 역할을 한다. 집계한 결과를 다시 조인하거나 거를 때 쓴다.
• 상관 서브쿼리 — 안쪽 쿼리가 바깥 행의 컬럼을 참조한다. 바깥 행마다 한 번씩 평가되는 개념이라 결과는 정확하지만 비용이 크다.

집합 연산은 방향이 다르다. 조인이 컬럼을 늘린다면 UNION 계열은 행을 늘린다. 컬럼 개수와 타입이 맞는 두 결과를 위아래로 이어 붙이는 연산이다.

표준 SQL 문법과 예제

스칼라 서브쿼리

SELECT emp_name, salary,
       salary - (SELECT AVG(salary) FROM emp) AS 평균차
FROM emp
ORDER BY 평균차 DESC;

사원 열 명의 급여 합이 48,300 이므로 전체 평균은 4,830.00 이다. 여기서 안쪽 쿼리는 바깥 행과 무관하므로 한 번만 계산된다. WHERE 절에서도 같은 방식으로 쓴다.

SELECT emp_name, salary
FROM emp
WHERE salary > (SELECT AVG(salary) FROM emp);

김유신(5200) · 유관순(7200) · 안중근(6800) · 김구(5000) 네 명이 나온다.

인라인 뷰

"부서 평균 급여가 5,000 이상인 부서의 이름과 평균"처럼 집계 결과를 다시 조건으로 쓰는 경우다. HAVING 으로도 되지만, 집계 결과를 다른 테이블과 조인해야 하면 인라인 뷰가 자연스럽다.

SELECT d.dept_name, ROUND(a.avg_sal, 1) AS avg_sal, a.cnt
FROM (SELECT dept_id, AVG(salary) AS avg_sal, COUNT(*) AS cnt
      FROM emp
      GROUP BY dept_id) a
JOIN dept d ON d.dept_id = a.dept_id
WHERE a.avg_sal >= 5000
ORDER BY a.avg_sal DESC;

개발팀 5,733.3(3명)과 재무팀 5,000.0(1명)이 나온다. 영업팀은 4,933.3 이라 빠진다. 인라인 뷰에는 반드시 별칭을 붙인다(Oracle 만 선택). 별칭 없이 쓰면 MySQL 은 Every derived table must have its own alias 로 거절한다.

상관 서브쿼리

SELECT e.emp_name, e.salary
FROM emp e
WHERE e.salary > (SELECT AVG(x.salary)
                  FROM emp x
                  WHERE x.dept_id = e.dept_id);

유관순 · 안중근 · 윤봉길 세 명이 나온다. 여기서 놓치기 쉬운 것이 홍범도다. 부서가 NULL 이라 안쪽 쿼리가 한 행도 못 찾고 AVG 는 NULL 을 돌려주며, 4400 > NULL 은 참이 아니므로 제외된다. 에러가 아니라 조용한 누락이다.

EXISTS 와 IN

-- 사원이 한 명이라도 있는 부서
SELECT d.dept_name
FROM dept d
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.dept_id = d.dept_id);

-- 같은 결과, IN 버전
SELECT d.dept_name
FROM dept d
WHERE d.dept_id IN (SELECT e.dept_id FROM emp e);

둘 다 인사팀 · 개발팀 · 영업팀 · 재무팀 네 부서를 돌려준다. EXISTS 는 조건에 맞는 행을 하나 찾는 순간 멈춘다. SELECT 1 이든 SELECT * 든 성능은 같다. 관례상 1을 쓴다. 반대로 부정형은 사정이 완전히 다르다.

-- 사원이 한 명도 없는 부서를 찾으려는 의도
SELECT dept_name FROM dept
WHERE dept_id NOT IN (SELECT dept_id FROM emp);   -- 결과 0건 (틀림)

SELECT dept_name FROM dept d
WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.dept_id = d.dept_id);  -- 총무팀 (맞음)

이 차이는 아래 "자주 틀리는 것"에서 따로 설명한다. 실무 규칙만 먼저 적으면, 부정 조건은 NOT EXISTS 로 쓴다.

ANY / ALL

-- 영업팀의 어느 한 명보다라도 많이 받는 사원
SELECT emp_name, salary FROM emp
WHERE salary > ANY (SELECT salary FROM emp WHERE dept_id = 30);

-- 영업팀 전원보다 많이 받는 사원
SELECT emp_name, salary FROM emp
WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = 30);

영업팀 급여는 3,900 · 4,100 · 6,800 이다. > ANY 는 최솟값 3,900 보다 크면 되고, > ALL 은 최댓값 6,800 보다 커야 해서 유관순만 남는다. 읽기 어려운 문법이라 실무에서는 > (SELECT MIN(...)), > (SELECT MAX(...)) 로 풀어 쓰는 편이 낫다. = ANY 는 IN 과 같고, ALL 은 서브쿼리가 빈 결과일 때 무조건 참이 되는 함정까지 있다.

UNION 계열

SELECT emp_name, '고연봉' AS 구분 FROM emp WHERE salary >= 6000
UNION ALL
SELECT emp_name, '개발팀'        FROM emp WHERE dept_id = 20
ORDER BY 1;

유관순은 두 조건을 다 만족해서 두 번 나온다. UNION ALL 은 그대로 붙이고, UNION 은 중복을 제거한다. 중복 제거는 공짜가 아니라 정렬 또는 해시가 필요하다. 중복이 없다는 걸 아는 상황이면 UNION ALL 을 쓴다.

-- 교집합: 서울 근무이면서 급여 5000 이상인 사원 번호
SELECT e.emp_id FROM emp e JOIN dept d ON d.dept_id = e.dept_id WHERE d.location = '서울'
INTERSECT
SELECT emp_id FROM emp WHERE salary >= 5000;

-- 차집합: 프로젝트가 하나도 없는 부서
SELECT dept_id FROM dept
EXCEPT
SELECT dept_id FROM proj WHERE dept_id IS NOT NULL;

교집합은 김유신(1001)과 유관순(1005)이다. 차집합은 인사팀 · 재무팀 · 총무팀이다. ORDER BY 는 집합 연산 전체에 한 번, 맨 마지막에만 쓸 수 있다. 각 SELECT 마다 붙일 수 없다. 정렬 기준은 첫 번째 SELECT 의 컬럼 이름이나 순번(ORDER BY 1)으로 지정한다.

DB별 차이

항목	MySQL · MariaDB	PostgreSQL	Oracle	SQL Server
INTERSECT	MySQL 8.0.31+ / MariaDB 10.3+	지원	지원	지원
차집합 키워드	EXCEPT (버전 조건 동일)	EXCEPT	MINUS	EXCEPT
인라인 뷰 별칭	필수	필수	선택	필수
IN 서브쿼리 안의 LIMIT	불가 (오류 1235). 파생 테이블로 한 겹 감싸 우회	가능	ROWNUM / FETCH 로 대체	TOP 사용 시 가능
FROM 없는 SELECT	가능	가능	FROM DUAL 필요	가능

과거 MySQL 은 IN (서브쿼리) 를 상관 서브쿼리로 바꿔 실행해서 극단적으로 느렸다. 5.6 이후 준결합(semi-join) 최적화가 들어가면서 대부분 해소됐지만, 오래된 5.5/5.6 환경에서 IN 이 느리면 JOIN 이나 EXISTS 로 바꿔 보는 것이 여전히 유효한 처방이다.

실무에서 자주 틀리는 것

1. NOT IN 은 서브쿼리에 NULL 이 하나만 있어도 전체가 빈 결과가 된다

dept_id NOT IN (10, 20, 30, 40, NULL) 은 내부적으로 dept_id <> 10 AND ... AND dept_id <> NULL 로 풀린다. 마지막 항이 언제나 UNKNOWN 이므로 AND 전체가 참이 될 수 없다. 우리 데이터에서 홍범도의 dept_id 가 NULL 이라 총무팀조차 걸러진다. 대처는 셋 중 하나다.

-- (권장) NOT EXISTS
SELECT dept_name FROM dept d
WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.dept_id = d.dept_id);

-- 서브쿼리에서 NULL 제거
SELECT dept_name FROM dept
WHERE dept_id NOT IN (SELECT dept_id FROM emp WHERE dept_id IS NOT NULL);

-- 외부 조인 + IS NULL (7단원의 안티 조인)
SELECT d.dept_name FROM dept d
LEFT JOIN emp e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

NOT IN 은 개발 DB 에 NULL 이 없을 때 잘 돌다가, 운영에서 NULL 이 한 건 들어오는 순간 결과가 0건이 된다. 배치가 아무것도 처리하지 않고 성공으로 끝나는 사고가 여기서 나온다.

2. 스칼라 서브쿼리가 두 행을 돌려주는 날

SELECT emp_name,
       (SELECT dept_name FROM dept WHERE location = '대전') AS d
FROM emp;

대전에 부서가 재무팀 하나뿐일 때는 잘 돈다. 지사가 하나 더 생기는 순간 Subquery returns more than 1 row(MySQL) / ORA-01427(Oracle) 로 쿼리가 죽는다. 스칼라 서브쿼리를 쓸 때는 안쪽 조건이 유일성을 보장하는지를 항상 확인하고, 아니면 조인으로 바꾼다.

3. 상관 서브쿼리를 SELECT 목록에 넣고 방치한다

SELECT e.emp_name,
       (SELECT COUNT(*) FROM proj p WHERE p.dept_id = e.dept_id) AS proj_cnt
FROM emp e;

사원이 10명이면 10번, 80만 명이면 80만 번 평가되는 형태다. 요즘 옵티마이저는 상당수를 조인으로 바꿔 주지만 항상은 아니다. 결과 건수가 많은 목록 화면에서 이 패턴이 보이면 인라인 뷰로 미리 집계해 조인하거나, 10단원에서 배울 윈도우 함수로 바꾸는 것이 정석이다.

4. 습관적으로 UNION 을 쓴다

월별로 나눠 둔 로그 테이블 열두 개를 합칠 때 UNION 을 쓰면, 중복이 있을 리 없는데도 DB 는 전체를 정렬해 중복을 찾는다. 수천만 행이면 임시 파일까지 만든다. 중복 가능성이 없으면 UNION ALL 이 맞다.

스스로 확인하기

1. 자기 부서의 평균 급여보다 적게 받는 사원의 이름 · 급여 · 부서명을 출력하라.
2. 진행 중인 프로젝트가 하나도 없는 부서의 이름을 NOT EXISTS 로 찾아라.
3. 급여 상위 3명과 입사일 빠른 순 3명을 중복 없이 한 목록으로 뽑아라.

-- 1
SELECT e.emp_name, e.salary, d.dept_name
FROM emp e
JOIN dept d ON d.dept_id = e.dept_id
WHERE e.salary < (SELECT AVG(x.salary) FROM emp x WHERE x.dept_id = e.dept_id);

-- 2
SELECT d.dept_name
FROM dept d
WHERE NOT EXISTS (SELECT 1 FROM proj p WHERE p.dept_id = d.dept_id);

-- 3  (MySQL/MariaDB/PostgreSQL 기준. 각 SELECT 를 괄호로 감싸야 LIMIT 이 개별 적용된다)
(SELECT emp_id, emp_name FROM emp ORDER BY salary DESC LIMIT 3)
UNION
(SELECT emp_id, emp_name FROM emp ORDER BY hire_date ASC LIMIT 3)
ORDER BY 1;

1번 결과는 김유신 · 이순신 · 강감찬 · 을지문덕 · 신채호 다섯 명이다. 김구는 부서에 혼자라 평균이 곧 자기 급여이므로 빠지고, 홍범도는 상관 서브쿼리가 NULL 이라 또 빠진다. 2번은 인사팀 · 재무팀 · 총무팀이다. 3번에서 괄호를 빼면 LIMIT 이 합쳐진 결과 전체에 걸려 3건만 나온다.

다음 9단원에서는 여기서 만든 쿼리들이 실제로 어떤 경로로 실행되는지, 왜 어떤 것은 빠르고 어떤 것은 테이블 전체를 읽는지를 인덱스와 EXPLAIN 으로 확인한다.

참고: MySQL EXISTS 서브쿼리 문서, PostgreSQL 집합 연산 문서