SQL 완전 정리 ⑤ 서브쿼리·집합 연산
서브쿼리는 쿼리 안에 들어간 쿼리, 집합 연산은 두 쿼리의 결과를 위아래로 합치는 연산이다. 둘 다 "결과 집합을 값처럼 다룬다"는 같은 생각에서 나왔다. 이 편은 서브쿼리를 어디에 쓰는가 (위치) 와 무엇을 돌려주는가(반환 형태) 두 축으로 나눈 뒤, 비교 연산자·IN·ANY/ALL·EXISTS, 상관 서브쿼리의 실행 흐름, CTE, 조인으로의 변환을 차례로 다룬다. 후반부는 UNION·INTERSECT· EXCEPT 와 그 규칙이다. 본문의 모든 쿼리와 결과 표는 sqlite3 와 PostgreSQL 로 실행해 맞췄다 (ANY/ALL 처럼 sqlite 에 없는 문법은 PostgreSQL 로만).
0. 이 편에서 쓰는 테이블
부서
| dno | dname | loc |
|---|---|---|
| 10 | 개발 | 서울 |
| 20 | 영업 | 부산 |
| 30 | 인사 | 서울 |
| 40 | 기획 | NULL |
사원 (이 편에서 쓰는 열만)
| eno | ename | dno | job | sal | mgr | bonus |
|---|---|---|---|---|---|---|
| 1 | 김하늘 | 10 | 팀장 | 500 | NULL | NULL |
| 2 | 이도윤 | 10 | 대리 | 350 | 1 | 30 |
| 3 | 박서준 | 10 | 사원 | 280 | 2 | NULL |
| 4 | 최지우 | 20 | 팀장 | 450 | NULL | 50 |
| 5 | 정민재 | 20 | 사원 | 300 | 4 | 20 |
| 6 | 한유나 | 20 | 사원 | 300 | 4 | NULL |
| 7 | 오세린 | NULL | 사원 | 250 | NULL | NULL |
프로젝트
| pno | pname | dno |
|---|---|---|
| 100 | 결제 | 10 |
| 200 | 광고 | 20 |
| 300 | 신규 | NULL |
참여
| eno | pno | hours |
|---|---|---|
| 1 | 100 | 20 |
| 2 | 100 | 40 |
| 3 | 100 | 30 |
| 2 | 200 | 10 |
| 5 | 200 | 25 |
눈여겨볼 NULL 두 개 — 오세린의 dno 가 NULL(부서 미배정), 신규 프로젝트의 dno 가 NULL. 이 편의 함정 대부분이 이 두 칸에서 나온다.
1. 서브쿼리란 — "값이 들어갈 자리에 쿼리를 넣는다"
SELECT ename, sal
FROM 사원
WHERE sal > (SELECT AVG(sal) FROM 사원); -- 괄호 안이 서브쿼리
바깥 쿼리를 메인 쿼리(main/outer query), 괄호 안을 서브쿼리(subquery/inner query) 라 한다. 서브쿼리는 반드시 괄호로 감싼다. 이 쿼리는 다음 두 단계로 읽으면 된다.
- 서브쿼리
SELECT AVG(sal) FROM 사원을 먼저 계산 → (500+350+280+450+300+300+250)/7 = 347.14… - 그 값을 그 자리에 끼워 넣은 쿼리
WHERE sal > 347.14…를 실행
| ename | sal |
|---|---|
| 김하늘 | 500 |
| 이도윤 | 350 |
| 최지우 | 450 |
"평균보다 많이 받는 사원"처럼 조건에 쓸 값을 미리 알 수 없을 때, 그 값을 구하는 쿼리를 통째로
조건 자리에 넣는 것이 서브쿼리의 존재 이유다. WHERE sal > AVG(sal) 처럼 집계를 WHERE 에 바로 쓰면
안 되는 이유(WHERE 는 그룹이 만들어지기 전에 실행된다 — ③편 논리적 처리 순서)를 서브쿼리가 우회한다.
1.1 두 가지 분류 축
| 축 | 종류 | 핵심 질문 |
|---|---|---|
| 위치 | SELECT 절(스칼라 서브쿼리) / FROM 절(인라인 뷰) / WHERE·HAVING 절(중첩 서브쿼리) | 어디에 썼나? |
| 반환 형태 | 단일행 / 다중행 / 다중열 | 몇 행 몇 열을 돌려주나? |
| 바깥 참조 | 비상관(non-correlated) / 상관(correlated) | 바깥 쿼리의 열을 쓰나? |
같은 서브쿼리가 세 축에서 동시에 하나씩 분류된다. 예: §1 의 쿼리는 "WHERE 절 · 단일행 · 비상관".
2. 위치별 분류
2.1 SELECT 절 — 스칼라 서브쿼리
SELECT 열, (SELECT 단일값 FROM … [WHERE …]) [AS 별칭]
FROM 테이블
스칼라(scalar) 는 "값 하나"라는 뜻이다. SELECT 절의 서브쿼리는 결과 표의 한 칸을 채우므로 반드시 1행 1열(또는 0행) 을 돌려줘야 한다.
SELECT ename, sal, (SELECT AVG(sal) FROM 사원) AS 전체평균
FROM 사원
ORDER BY eno;
| ename | sal | 전체평균 |
|---|---|---|
| 김하늘 | 500 | 347.14… |
| 이도윤 | 350 | 347.14… |
| 박서준 | 280 | 347.14… |
| 최지우 | 450 | 347.14… |
| 정민재 | 300 | 347.14… |
| 한유나 | 300 | 347.14… |
| 오세린 | 250 | 347.14… |
GROUP BY 없이도 "각 행 옆에 전체 평균"을 붙일 수 있다. (⑥편 윈도 함수 AVG(sal) OVER () 가
같은 일을 더 간결하게 한다.)
바깥 행의 값을 참조하면 조인처럼 쓸 수도 있다.
SELECT ename,
(SELECT dname FROM 부서 d WHERE d.dno = e.dno) AS 부서명
FROM 사원 e
ORDER BY eno;
| ename | 부서명 |
|---|---|
| 김하늘 | 개발 |
| 이도윤 | 개발 |
| 박서준 | 개발 |
| 최지우 | 영업 |
| 정민재 | 영업 |
| 한유나 | 영업 |
| 오세린 | NULL |
0행이면 NULL — 오세린은 dno 가 NULL 이라
d.dno = NULL을 만족하는 부서가 없다. 스칼라 서브쿼리는 행이 없으면 오류가 아니라 NULL 을 돌려준다. 결과적으로 LEFT OUTER JOIN 과 같은 결과(사원이 빠지지 않음)가 된다. 반대로 2행 이상이면 오류다(§3.1).
2.2 FROM 절 — 인라인 뷰(inline view)
SELECT …
FROM (SELECT … FROM … [GROUP BY …]) [AS] 별칭
[WHERE …]
FROM 에는 원래 테이블이 온다. 그 자리에 쿼리를 넣으면, 그 결과가 이름 없는 임시 테이블처럼 쓰인다. 저장되지 않고 쿼리 안에서만(inline) 존재하는 뷰라서 인라인 뷰라고 부른다.
용도 ① 집계 결과를 다시 조건으로 거르기 — 부서별 평균을 만든 뒤 사원과 조인:
SELECT e.ename, e.dno, e.sal, a.avg_sal
FROM 사원 e
JOIN (SELECT dno, AVG(sal) AS avg_sal
FROM 사원
GROUP BY dno) a
ON e.dno = a.dno
WHERE e.sal > a.avg_sal
ORDER BY e.eno;
중간 단계 — 인라인 뷰 a 의 내용:
| dno | avg_sal |
|---|---|
| 10 | 376.67 |
| 20 | 350.00 |
| NULL | 250.00 |
조인 + WHERE 뒤 최종 결과:
| ename | dno | sal | avg_sal |
|---|---|---|---|
| 김하늘 | 10 | 500 | 376.67 |
| 최지우 | 20 | 450 | 350.00 |
오세린(dno NULL)은 e.dno = a.dno 가 NULL = NULL → UNKNOWN 이라 조인에서 빠진다.
용도 ② SELECT 별칭을 WHERE 에서 쓰기 — WHERE 는 SELECT 보다 먼저 실행되므로 SELECT 의 별칭을 모른다. 한 겹 감싸면 안쪽 SELECT 가 먼저 끝나 별칭이 열 이름이 된다.
SELECT dno, cnt
FROM (SELECT dno, COUNT(*) AS cnt
FROM 사원
WHERE dno IS NOT NULL
GROUP BY dno) t
WHERE cnt >= 3;
| dno | cnt |
|---|---|
| 10 | 3 |
| 20 | 3 |
(같은 결과를 HAVING COUNT(*) >= 3 으로도 낼 수 있다. 인라인 뷰가 꼭 필요한 대표 사례는 ⑥편의
윈도 함수 결과로 거르기(Top-N) 다.)
별칭 규칙의 엔진 차이 — 인라인 뷰 별칭은 MySQL 에서 필수("Every derived table must have its own alias"), PostgreSQL 은 15 까지 필수·16 부터 선택, Oracle·SQLite 는 선택이다. Oracle 은 테이블 별칭 앞에
AS를 쓰면 오류다(FROM (…) t만 가능). 시험 답안엔 별칭을 붙이되AS없이 쓰는 것이 가장 안전하다.
2.3 WHERE·HAVING 절 — 중첩 서브쿼리
조건식의 한쪽에 들어가는, 가장 흔한 형태다. §1 이 WHERE 의 예. HAVING 에서는 그룹 집계값과 비교한다.
SELECT dno, SUM(sal)
FROM 사원
GROUP BY dno
HAVING SUM(sal) > (SELECT SUM(sal) FROM 사원 WHERE dno = 20)
ORDER BY dno;
서브쿼리 = 20번 부서 합계 450+300+300 = 1050. 그룹별 합계(10:1130, 20:1050, NULL:250) 중 1050 초과:
| dno | SUM(sal) |
|---|---|
| 10 | 1130 |
2.4 위치별 요약
| 위치 | 이름 | 허용 반환 형태 | 결과가 0행이면 |
|---|---|---|---|
| SELECT 절 | 스칼라 서브쿼리 | 1행 1열만 | NULL |
| FROM 절 | 인라인 뷰 | 몇 행 몇 열이든 | 빈 테이블 |
| WHERE / HAVING | 중첩 서브쿼리 | 연산자에 따라 다름(§3) | 연산자에 따라 다름 |
| (참고) INSERT·UPDATE·DELETE | — | INSERT … SELECT, SET col = (스칼라) 등 | — |
3. 반환 형태별 분류 — 연산자가 받아 줄 수 있는가
| 반환 형태 | 모양 | 쓸 수 있는 연산자 |
|---|---|---|
| 단일행 | 1행 1열 | = <> > >= < <= (단일행 비교 연산자) |
| 다중행 | n행 1열 | IN NOT IN ANY/SOME ALL EXISTS |
| 다중열 | n행 m열 | (a, b) IN (SELECT x, y …) (행 값 비교) |
3.1 단일행 연산자 + 다중행 결과 = 오류
= 는 "값 하나 = 값 하나"를 비교한다. 서브쿼리가 여러 행을 내면 무엇과 비교할지 정할 수 없다.
SELECT ename FROM 사원
WHERE sal = (SELECT sal FROM 사원 WHERE dno = 20); -- 450, 300, 300 세 행
| 엔진 | 결과 |
|---|---|
| Oracle | ORA-01427: single-row subquery returns more than one row |
| PostgreSQL | ERROR: more than one row returned by a subquery used as an expression |
| MySQL | ERROR 1242: Subquery returns more than 1 row |
| SQLite | 오류 없음 — 첫 행(450)만 쓰고 조용히 진행 → 최지우 1행 |
SQLite 의 동작은 표준이 아니다. 시험·실무 기준 정답은 "오류". 해결은 연산자를
IN(같은 값 중 하나라도) 으로 바꾸거나, 서브쿼리를MAX(sal)처럼 집계해 1행으로 만드는 것.
스칼라 서브쿼리(§2.1)도 같다. 사원별 참여 프로젝트를 (SELECT pno FROM 참여 c WHERE c.eno = e.eno) 로
붙이면, 두 프로젝트에 참여한 이도윤 행에서 2행이 나와 PostgreSQL 은 오류를 낸다(SQLite 는 100 만 표시).
3.2 다중행 — IN / NOT IN
식 [NOT] IN (서브쿼리)
IN 은 "목록의 값 중 하나라도 같으면 참". 내부적으로는 = 를 OR 로 이은 것과 같다.
SELECT ename, dno FROM 사원
WHERE dno IN (SELECT dno FROM 부서 WHERE loc = '서울') -- (10, 30)
ORDER BY eno;
dno IN (10, 30) ≡ dno = 10 OR dno = 30.
| ename | dno |
|---|---|
| 김하늘 | 10 |
| 이도윤 | 10 |
| 박서준 | 10 |
30번(인사) 부서엔 사원이 없으므로 10번만 남는다. NOT IN 은 그 반대 — 목록의 모든 값과 달라야
참이며, <> 를 AND 로 이은 것과 같다. 이 AND 가 §5 의 NULL 함정을 만든다.
3.3 다중행 — ANY(SOME) / ALL
식 비교연산자 ANY (서브쿼리) -- 하나라도 만족하면 참 (SOME 은 ANY 의 동의어)
식 비교연산자 ALL (서브쿼리) -- 전부 만족해야 참
예제의 서브쿼리는 20번 부서 급여 목록 (450, 300, 300) 으로 고정한다.
SELECT ename, sal FROM 사원
WHERE sal > ANY (SELECT sal FROM 사원 WHERE dno = 20)
ORDER BY eno;
> ANY = 목록 중 아무거나 하나보다 크면 참 = 최솟값(300)보다 크면 참.
| ename | sal |
|---|---|
| 김하늘 | 500 |
| 이도윤 | 350 |
| 최지우 | 450 |
SELECT ename, sal FROM 사원
WHERE sal > ALL (SELECT sal FROM 사원 WHERE dno = 20)
ORDER BY eno;
> ALL = 목록의 모든 값보다 크면 참 = 최댓값(450)보다 크면 참.
| ename | sal |
|---|---|
| 김하늘 | 500 |
MIN/MAX 로 바꿔 이해하기 — 외우지 말고 "가장 통과하기 쉬운 값/어려운 값"을 떠올리면 된다.
| 표현 | 의미 | 바꿔 쓰면 (빈 집합·NULL 없을 때) | 위 예 결과 |
|---|---|---|---|
> ANY | 최소보다 크다 | > (SELECT MIN …) | 500, 350, 450 |
> ALL | 최대보다 크다 | > (SELECT MAX …) | 500 |
< ANY | 최대보다 작다 | < (SELECT MAX …) | 350, 280, 300, 300, 250 |
< ALL | 최소보다 작다 | < (SELECT MIN …) | 280, 250 |
= ANY | 목록 중 하나와 같다 | IN | 450, 300, 300 (최지우·정민재·한유나) |
<> ALL | 목록 전부와 다르다 | NOT IN | 500, 350, 280, 250 |
<> ANY | 목록 중 하나와라도 다르다 | (목록 값이 2종 이상이면 사실상 항상 참) | — |
<> ANY≠NOT IN— 자주 틀린다.NOT IN의 짝은<> ALL이다.<> ANY는 "어느 하나와라도 다르면 참"이라 목록에 서로 다른 값이 둘만 있어도 모든 행이 통과한다.
빈 집합에서 ALL 은 참, ANY 는 거짓 — 30번 부서(인사)는 사원이 없어 서브쿼리가 0행이다.
SELECT COUNT(*) FROM 사원 WHERE sal > ALL (SELECT sal FROM 사원 WHERE dno = 30); -- 7
SELECT COUNT(*) FROM 사원 WHERE sal > ANY (SELECT sal FROM 사원 WHERE dno = 30); -- 0
SELECT COUNT(*) FROM 사원 WHERE sal > (SELECT MAX(sal) FROM 사원 WHERE dno = 30); -- 0
| 쿼리 | 결과 | 이유 |
|---|---|---|
> ALL (빈 집합) | 7 | "모든 원소에 대해 참" — 반례가 하나도 없으므로 참(공허한 참, vacuous truth) |
> ANY (빈 집합) | 0 | "참인 원소가 하나라도 있다" — 원소가 없으니 거짓 |
> (SELECT MAX …) | 0 | 빈 집합의 MAX 는 NULL → sal > NULL 은 UNKNOWN |
그래서 > ALL 과 > (SELECT MAX …) 는 빈 집합에서 결과가 다르다. 위 MIN/MAX 변환표에 "빈 집합
없을 때"라는 조건을 붙인 이유다.
목록에 NULL 이 섞이면 — ALL 은 하나라도 UNKNOWN 이면 참이 될 수 없다.
SELECT ename, sal FROM 사원
WHERE sal > ALL (SELECT bonus FROM 사원 WHERE dno = 10); -- (NULL, 30, NULL)
-- 결과: 0행
500 > 30 은 참이지만 500 > NULL 이 UNKNOWN 이라, 전체 AND 가 UNKNOWN → 탈락. 모든 행이 탈락해 0행.
반면 MAX(bonus) 는 NULL 을 무시하고 30 을 돌려주므로 > (SELECT MAX(bonus) …) 는 7행 모두 통과한다.
엔진 지원 — ANY/SOME/ALL 은 표준 SQL 이며 Oracle·PostgreSQL·MySQL·SQL Server 모두 지원한다. SQLite 는 지원하지 않는다(구문 오류). Oracle 은
sal > ANY (300, 450)처럼 서브쿼리 대신 값 목록도 받는다.
3.4 다중열 서브쿼리 — 행 값 비교
(열1, 열2) IN (SELECT x, y FROM …)
두 열을 짝으로 묶어 비교한다. "부서별 최고 급여자"를 한 번에 구할 수 있다.
SELECT ename, dno, sal FROM 사원
WHERE (dno, sal) IN (SELECT dno, MAX(sal) FROM 사원 GROUP BY dno)
ORDER BY eno;
서브쿼리 결과 = (10, 500), (20, 450), (NULL, 250).
| ename | dno | sal |
|---|---|---|
| 김하늘 | 10 | 500 |
| 최지우 | 20 | 450 |
오세린의 (NULL, 250) 은 (NULL, 250) 과 짝이 같아 보이지만 NULL = NULL 이 UNKNOWN 이라 빠진다.
왜 열을 따로 비교하면 안 되나 —
WHERE dno IN (…dno…) AND sal IN (…MAX(sal)…)로 쪼개면 "10번 부서인데 급여가 20번 부서 최고값(450)"인 사람도 통과할 수 있다. 짝을 유지하려면 행 값 비교나 상관 서브쿼리(§6)를 써야 한다.엔진 지원 — Oracle·PostgreSQL·MySQL·SQLite(3.15+) 지원, SQL Server 는 미지원(EXISTS 로 바꿔 씀).
4. EXISTS / NOT EXISTS — 행이 "있는지"만 본다
[NOT] EXISTS (서브쿼리)
EXISTS 는 서브쿼리가 1행이라도 돌려주면 참, 0행이면 거짓이다. 값을 비교하지 않으므로 SELECT
목록은 무엇이든 상관없다(SELECT 1, SELECT *, 심지어 SELECT NULL 도 결과는 같다). 거의 항상
바깥 열을 참조하는 상관 서브쿼리로 쓴다.
SELECT dname FROM 부서 d
WHERE EXISTS (SELECT 1 FROM 사원 e WHERE e.dno = d.dno)
ORDER BY dno;
부서 한 행씩 꺼내 "이 부서 번호를 가진 사원이 있나?"를 묻는다.
| 바깥 행 (d) | 서브쿼리 WHERE e.dno = d.dno 결과 | EXISTS |
|---|---|---|
| 10 개발 | 김하늘, 이도윤, 박서준 (3행) | 참 |
| 20 영업 | 최지우, 정민재, 한유나 (3행) | 참 |
| 30 인사 | 0행 | 거짓 |
| 40 기획 | 0행 | 거짓 |
| dname |
|---|
| 개발 |
| 영업 |
NOT EXISTS 로 바꾸면 인사·기획 두 행이 나온다.
EXISTS 의 특징 세 가지
- 참/거짓 두 값만 낸다 — IN·비교 연산자와 달리 UNKNOWN 이 나오지 않는다. 그래서 NULL 에 강하다(§5).
- 첫 행을 찾으면 멈출 수 있다 — "있느냐"만 보므로 DBMS 는 일치하는 행을 하나 찾는 순간 탐색을 끝낸다(semi join). 행 수가 많은 쪽을 확인할 때 유리한 이유.
- 서브쿼리 안에 조건을 더 붙여 "~한 행이 있는가"로 확장할 수 있다.
-- 급여 450 이상인 사원이 있는 부서
SELECT dname FROM 부서 d
WHERE EXISTS (SELECT NULL FROM 사원 e WHERE e.dno = d.dno AND e.sal >= 450)
ORDER BY dno;
-- 개발(김하늘 500), 영업(최지우 450)
5. NOT IN 과 NULL 함정 vs NOT EXISTS
질문: 사원이 한 명도 없는 부서는? (정답은 인사·기획)
SELECT dname FROM 부서
WHERE dno NOT IN (SELECT dno FROM 사원);
| 결과 |
|---|
| (0행) |
인사·기획이 나와야 하는데 아무것도 안 나온다. 오세린의 dno 가 NULL 이기 때문이다.
5.1 왜 0행이 되나 — NOT IN 을 AND 로 펼치기
서브쿼리 결과 = (10, 10, 10, 20, 20, 20, NULL). 30번 부서 행에 대해:
30 NOT IN (10, 20, NULL)
≡ 30 <> 10 AND 30 <> 20 AND 30 <> NULL
≡ TRUE AND TRUE AND UNKNOWN
≡ UNKNOWN → WHERE 탈락
| 부서 dno | <> 10 | <> 20 | <> NULL | AND 결과 | 통과? |
|---|---|---|---|---|---|
| 10 | FALSE | TRUE | UNKNOWN | FALSE | ✗ |
| 20 | TRUE | FALSE | UNKNOWN | FALSE | ✗ |
| 30 | TRUE | TRUE | UNKNOWN | UNKNOWN | ✗ |
| 40 | TRUE | TRUE | UNKNOWN | UNKNOWN | ✗ |
AND 에서는 FALSE 가 하나라도 있으면 FALSE, 아니면 UNKNOWN 이 하나만 있어도 UNKNOWN 이다. 목록에 NULL 이 하나라도 있으면 NOT IN 은 결코 TRUE 가 될 수 없다 → 항상 0행.
5.2 해결 두 가지
-- ① 서브쿼리에서 NULL 을 걸러 낸다
SELECT dname FROM 부서
WHERE dno NOT IN (SELECT dno FROM 사원 WHERE dno IS NOT NULL);
-- ② NOT EXISTS 로 바꾼다 (권장)
SELECT dname FROM 부서 d
WHERE NOT EXISTS (SELECT 1 FROM 사원 e WHERE e.dno = d.dno);
두 쿼리 모두:
| dname |
|---|
| 인사 |
| 기획 |
NOT EXISTS 가 안전한 이유: 오세린 행에서 e.dno = d.dno 는 NULL = 30 → UNKNOWN → 그 행은 그냥
안 맞는 행으로 처리될 뿐이다. EXISTS 는 "맞는 행이 있느냐"만 보므로 UNKNOWN 이 결과 전체를 오염시키지
않는다.
5.3 반대쪽 NULL — 바깥 값이 NULL 일 때
이번엔 "10번 부서가 아닌 사원"을 구한다. 바깥 쪽(오세린)이 NULL 이다.
SELECT ename FROM 사원
WHERE dno NOT IN (SELECT dno FROM 부서 WHERE dno = 10)
ORDER BY eno;
| ename |
|---|
| 최지우 |
| 정민재 |
| 한유나 |
SELECT ename FROM 사원 e
WHERE NOT EXISTS (SELECT 1 FROM 부서 d WHERE d.dno = e.dno AND d.dno = 10)
ORDER BY eno;
| ename |
|---|
| 최지우 |
| 정민재 |
| 한유나 |
| 오세린 |
NULL NOT IN (10) 은 NULL <> 10 → UNKNOWN 이라 오세린이 빠지고, NOT EXISTS 는 "10번 부서와 맞는
행이 없다"가 참이라 오세린을 포함한다. 두 쿼리는 NULL 이 있는 순간 서로 다른 질문이 된다.
5.4 정리 표
| 서브쿼리에 NULL | 바깥 값이 NULL | |
|---|---|---|
IN | 영향 없음 (맞는 값이 있으면 TRUE) | 그 행 탈락 |
NOT IN | 전체 0행 | 그 행 탈락 |
EXISTS | 영향 없음 | 그 행 탈락 (맞는 행 없음) |
NOT EXISTS | 영향 없음 | 그 행 포함 |
같은 질문에
IN과EXISTS는 대개 결과가 같다(IN 이 서브쿼리 NULL 에 영향받지 않으므로). 결과가 갈리는 것은 부정형(NOT) 쪽이다. 실무 규칙: "부정형은 NOT EXISTS".
6. 비상관 vs 상관 서브쿼리
| 비상관(non-correlated) | 상관(correlated) | |
|---|---|---|
| 바깥 열 참조 | 안 함 — 혼자 실행 가능 | 함 — 바깥 행 없이는 실행 불가 |
| 논리적 실행 횟수 | 1번 실행 후 결과 재사용 | 바깥 행마다 다시 실행 |
| 예 | WHERE sal > (SELECT AVG(sal) FROM 사원) | WHERE sal > (SELECT AVG(sal) FROM 사원 e2 WHERE e2.dno = e1.dno) |
"상관"의 표시는 서브쿼리 안의 e1.dno 처럼 바깥 테이블 별칭이 등장하는 것이다. 같은 테이블을
안팎에서 쓰므로 별칭(e1, e2)으로 구분해야 한다.
"행마다 다시 실행"은 논리적 의미다. 실제 DBMS 옵티마이저는 상관 서브쿼리를 조인으로 바꾸거나 결과를 캐시하는 등 다르게 실행할 수 있다. 결과는 언제나 "행마다 실행한 것"과 같다.
6.1 실행 흐름 추적 — 자기 부서 평균보다 많이 받는 사원
SELECT ename, dno, sal
FROM 사원 e1
WHERE sal > (SELECT AVG(sal)
FROM 사원 e2
WHERE e2.dno = e1.dno) -- 바깥 행의 dno 를 받는다
ORDER BY eno;
바깥 행을 하나 꺼낼 때마다 그 행의 e1.dno 를 서브쿼리에 넣어 다시 계산한다.
| 단계 | 바깥 행 (e1) | e1.dno | 서브쿼리가 보는 행 (e2) | AVG | sal > AVG | 결과 |
|---|---|---|---|---|---|---|
| 1 | 김하늘 500 | 10 | 500, 350, 280 | 376.67 | 500 > 376.67 TRUE | ✓ |
| 2 | 이도윤 350 | 10 | 500, 350, 280 | 376.67 | FALSE | ✗ |
| 3 | 박서준 280 | 10 | 500, 350, 280 | 376.67 | FALSE | ✗ |
| 4 | 최지우 450 | 20 | 450, 300, 300 | 350 | TRUE | ✓ |
| 5 | 정민재 300 | 20 | 450, 300, 300 | 350 | FALSE | ✗ |
| 6 | 한유나 300 | 20 | 450, 300, 300 | 350 | FALSE | ✗ |
| 7 | 오세린 250 | NULL | e2.dno = NULL → 0행 | NULL | 250 > NULL UNKNOWN | ✗ |
| ename | dno | sal |
|---|---|---|
| 김하늘 | 10 | 500 |
| 최지우 | 20 | 450 |
비상관 버전 sal > (SELECT AVG(sal) FROM 사원) 은 서브쿼리가 347.14 로 한 번 고정돼 김하늘·이도윤·최지우
3명이 나왔다(§1). 상관 여부가 비교 기준을 "전체"에서 "자기 그룹"으로 바꾼다.
6.2 부서별 최고 급여자 — 세 가지 작성법과 NULL 차이
-- (가) = MAX
SELECT ename, dno, sal FROM 사원 e1
WHERE sal = (SELECT MAX(sal) FROM 사원 e2 WHERE e2.dno = e1.dno)
ORDER BY eno;
-- (나) >= ALL (PostgreSQL 등 ANY/ALL 지원 엔진)
SELECT ename, dno, sal FROM 사원 e1
WHERE sal >= ALL (SELECT sal FROM 사원 e2 WHERE e2.dno = e1.dno)
ORDER BY eno;
-- (다) 나보다 많이 받는 동료가 없다
SELECT ename, dno, sal FROM 사원 e1
WHERE NOT EXISTS (SELECT 1 FROM 사원 e2
WHERE e2.dno = e1.dno AND e2.sal > e1.sal)
ORDER BY eno;
| 작성법 | 김하늘(10, 500) | 최지우(20, 450) | 오세린(NULL, 250) | 행 수 |
|---|---|---|---|---|
(가) = MAX | ✓ | ✓ | ✗ — 0행의 MAX = NULL, 250 = NULL UNKNOWN | 2 |
(나) >= ALL | ✓ | ✓ | ✓ — 빈 집합에 대한 ALL 은 참 | 3 |
(다) NOT EXISTS | ✓ | ✓ | ✓ — 더 많이 받는 동료 행이 없음 | 3 |
부서가 있는 사원만 보면 셋 다 같지만, 부서가 NULL 인 행에서 (가)와 (나)(다)가 갈린다. §3.3 의
"빈 집합에서 ALL 은 참"이 실제로 결과를 바꾸는 장면이다. 미배정 사원을 빼려면 바깥에
WHERE e1.dno IS NOT NULL 을 붙인다. 참고로 동점 최고자가 있으면 세 방법 모두 둘 다 돌려준다.
6.3 Top-N — 부서별 급여 상위 2명
"나보다 급여가 높은 같은 부서 사람이 2명 미만"이면 상위 2위 안이다.
SELECT ename, dno, sal FROM 사원 e1
WHERE e1.dno IS NOT NULL
AND (SELECT COUNT(*) FROM 사원 e2
WHERE e2.dno = e1.dno AND e2.sal > e1.sal) < 2
ORDER BY dno, sal DESC, eno;
| 바깥 행 | 같은 부서에서 나보다 높은 사람 | COUNT | < 2 |
|---|---|---|---|
| 김하늘 500 | — | 0 | ✓ |
| 이도윤 350 | 김하늘 | 1 | ✓ |
| 박서준 280 | 김하늘, 이도윤 | 2 | ✗ |
| 최지우 450 | — | 0 | ✓ |
| 정민재 300 | 최지우 | 1 | ✓ |
| 한유나 300 | 최지우 (300 은 > 가 아님) | 1 | ✓ |
| ename | dno | sal |
|---|---|---|
| 김하늘 | 10 | 500 |
| 이도윤 | 10 | 350 |
| 최지우 | 20 | 450 |
| 정민재 | 20 | 300 |
| 한유나 | 20 | 300 |
20번 부서는 동점 때문에 3명이 나온다. "나보다 높은 사람 수 + 1" 은 곧 RANK() 순위이므로, 이 방법은
⑥편의 RANK() … <= 2 와 같은 결과를 낸다. 정확히 2명만 원하면 ⑥편의 ROW_NUMBER() 를 쓴다.
e1.dno IS NOT NULL 을 빼면 오세린(COUNT 0)도 끼어든다 — 부서 없는 사람이 "자기 부서 1등"이 되는 셈.
같은 원리로 전체 순위도 만들 수 있다.
SELECT ename, sal,
(SELECT COUNT(*) FROM 사원 e2 WHERE e2.sal > e1.sal) + 1 AS 순위
FROM 사원 e1
ORDER BY 순위, eno;
| ename | sal | 순위 |
|---|---|---|
| 김하늘 | 500 | 1 |
| 최지우 | 450 | 2 |
| 이도윤 | 350 | 3 |
| 정민재 | 300 | 4 |
| 한유나 | 300 | 4 |
| 박서준 | 280 | 6 |
| 오세린 | 250 | 7 |
동점 다음 순위가 5를 건너뛰고 6 — RANK() 와 같은 방식이다.
7. WITH 절 (CTE, 공통 테이블 식)
WITH 이름1 [(열, …)] AS (서브쿼리1)
[, 이름2 AS (서브쿼리2) …]
SELECT … FROM 이름1 …;
인라인 뷰에 이름을 붙여 쿼리 맨 앞으로 끌어올린 것이 CTE(Common Table Expression)다. 위에서 아래로 읽히고, 같은 결과를 여러 번 참조할 수 있으며, 뒤의 CTE 가 앞의 CTE 를 참조할 수 있다.
WITH 부서평균 AS (
SELECT dno, AVG(sal) AS avg_sal
FROM 사원
WHERE dno IS NOT NULL
GROUP BY dno
)
SELECT e.ename, e.sal, ROUND(b.avg_sal, 1) AS 부서평균
FROM 사원 e
JOIN 부서평균 b ON e.dno = b.dno
WHERE e.sal < b.avg_sal
ORDER BY e.eno;
| ename | sal | 부서평균 |
|---|---|---|
| 이도윤 | 350 | 376.7 |
| 박서준 | 280 | 376.7 |
| 정민재 | 300 | 350.0 |
| 한유나 | 300 | 350.0 |
CTE 여러 개를 사슬처럼:
WITH 고액 AS (SELECT * FROM 사원 WHERE sal >= 350),
부서별 AS (SELECT dno, COUNT(*) AS cnt FROM 고액 GROUP BY dno)
SELECT d.dname, b.cnt
FROM 부서별 b JOIN 부서 d ON d.dno = b.dno
ORDER BY d.dno;
| dname | cnt |
|---|---|
| 개발 | 2 |
| 영업 | 1 |
재귀 CTE (참고) — 자기 자신을 참조해 계층(조직도·카테고리 트리)을 내려간다.
WITH RECURSIVE 조직(eno, ename, mgr, 깊이) AS (
SELECT eno, ename, mgr, 1 FROM 사원 WHERE eno = 1 -- 시작점(앵커)
UNION ALL
SELECT e.eno, e.ename, e.mgr, o.깊이 + 1
FROM 사원 e JOIN 조직 o ON e.mgr = o.eno -- 한 단계 아래
)
SELECT ename, 깊이 FROM 조직 ORDER BY 깊이;
| ename | 깊이 |
|---|---|
| 김하늘 | 1 |
| 이도윤 | 2 |
| 박서준 | 3 |
| 엔진 | WITH | 재귀 |
|---|---|---|
| 표준·PostgreSQL·SQLite·MySQL 8.0+ | 지원 | WITH RECURSIVE |
| Oracle | 지원 (9i+) | RECURSIVE 키워드 없이 WITH 만 (계층은 전용 CONNECT BY 도 있음) |
| MySQL 5.7 이하 | 미지원 | — |
8. 서브쿼리 ↔ 조인 변환
같은 질문을 서브쿼리와 조인 양쪽으로 쓸 수 있는 경우가 많다. 둘의 결과가 언제 같고 언제 다른지가 핵심이다.
8.1 IN / EXISTS → 조인: 중복에 주의
"프로젝트에 참여한 사원":
SELECT ename FROM 사원
WHERE eno IN (SELECT eno FROM 참여)
ORDER BY eno;
| ename |
|---|
| 김하늘 |
| 이도윤 |
| 박서준 |
| 정민재 |
SELECT e.ename FROM 사원 e JOIN 참여 c ON e.eno = c.eno
ORDER BY e.eno;
| ename |
|---|
| 김하늘 |
| 이도윤 |
| 이도윤 |
| 박서준 |
| 정민재 |
이도윤은 프로젝트 두 개(100, 200)에 참여해 조인에서는 2행이 된다. IN·EXISTS 는 "있는지"만 보는
세미 조인(semi join) 이라 바깥 행을 늘리지 않는다. 조인으로 바꾸려면 SELECT DISTINCT 가 필요하다
(단, DISTINCT 는 원래 동명이인처럼 정말 다른 행까지 합쳐 버릴 수 있으니 키 열을 같이 고른다).
8.2 NOT EXISTS → LEFT JOIN … IS NULL (안티 조인)
"어떤 프로젝트에도 참여하지 않은 사원":
-- 서브쿼리
SELECT ename FROM 사원 e
WHERE NOT EXISTS (SELECT 1 FROM 참여 c WHERE c.eno = e.eno)
ORDER BY eno;
-- 조인
SELECT e.ename
FROM 사원 e LEFT JOIN 참여 c ON e.eno = c.eno
WHERE c.eno IS NULL
ORDER BY e.eno;
둘 다:
| ename |
|---|
| 최지우 |
| 한유나 |
| 오세린 |
LEFT JOIN 은 짝이 없는 사원 행의 참여 쪽 열을 NULL 로 채운다. 그 NULL 을 IS NULL 로 골라내면 "짝 없는
행"만 남는다. 이것이 안티 조인(anti join) 이다. (WHERE c.eno IS NULL 대신 c.hours IS NULL 처럼
원래 NULL 이 들어 있을 수 있는 열을 쓰면 오답이 된다 — 조인 키 또는 NOT NULL 열을 검사한다.)
8.3 스칼라 서브쿼리 → 외부 조인 + 집계
SELECT pname,
(SELECT SUM(hours) FROM 참여 c WHERE c.pno = p.pno) AS 총시간
FROM 프로젝트 p
ORDER BY pno;
| pname | 총시간 |
|---|---|
| 결제 | 90 |
| 광고 | 35 |
| 신규 | NULL |
조인으로 바꾸면 FROM 프로젝트 p LEFT JOIN 참여 c ON c.pno = p.pno GROUP BY p.pno, p.pname 이다.
LEFT 가 아니면 참여자가 없는 '신규'가 사라진다(스칼라 서브쿼리는 0행 → NULL 로 행을 남긴다).
8.4 단일행 서브쿼리 → 셀프 조인
-- 최지우의 부하 직원
SELECT ename, sal FROM 사원
WHERE mgr = (SELECT eno FROM 사원 WHERE ename = '최지우');
SELECT e.ename, e.sal
FROM 사원 e JOIN 사원 m ON e.mgr = m.eno
WHERE m.ename = '최지우';
| ename | sal |
|---|---|
| 정민재 | 300 |
| 한유나 | 300 |
(같은 이름의 '최지우'가 둘이면 서브쿼리 버전은 §3.1 의 오류, 조인 버전은 두 최지우의 부하가 모두 나온다.)
8.5 변환표
| 서브쿼리 | 대응 조인 | 주의 |
|---|---|---|
IN / EXISTS | INNER JOIN + DISTINCT | 1:N 이면 행이 늘어남 |
NOT EXISTS | LEFT JOIN … WHERE 오른쪽키 IS NULL | NOT IN 은 NULL 때문에 동치가 아님 |
| 스칼라 서브쿼리 | LEFT JOIN (+ GROUP BY) | INNER 로 쓰면 짝 없는 행 소실 |
| 상관 집계 비교 | 인라인 뷰(그룹 집계) + 조인 | §2.2 예제가 §6.1 의 변환본 |
9. 집합 연산 — 두 결과를 위아래로 합친다
SELECT … {UNION [ALL] | INTERSECT [ALL] | EXCEPT [ALL]} SELECT …
[ORDER BY …]
조인이 옆으로(열을) 붙인다면, 집합 연산은 아래로(행을) 붙인다. 두 결과를 수학의 집합처럼 합집합·교집합·차집합한다.
| 연산 | 의미 | 중복 |
|---|---|---|
UNION | 합집합 | 제거 |
UNION ALL | 합집합 | 유지 (그냥 이어 붙임) |
INTERSECT | 교집합 | 제거 |
EXCEPT (Oracle: MINUS) | 차집합 (앞 − 뒤) | 제거 |
예제 집합 — 부서.dno = {10, 20, 30, 40}, 사원.dno = [10, 10, 10, 20, 20, 20, NULL].
9.1 UNION vs UNION ALL
SELECT dno FROM 부서
UNION
SELECT dno FROM 사원
ORDER BY dno;
| dno |
|---|
| 10 |
| 20 |
| 30 |
| 40 |
| NULL |
(NULL 의 위치는 엔진에 따라 다르다 — PostgreSQL·Oracle 은 오름차순에서 맨 뒤, SQLite·MySQL·SQL Server 는 맨 앞.)
SELECT dno FROM 부서
UNION ALL
SELECT dno FROM 사원;
4 + 7 = 11행이 그대로 나온다(10 이 네 번, 20 이 네 번, 30·40·NULL 각 한 번).
동작 원리 — UNION 은 두 결과를 이어 붙인 뒤 정렬 또는 해싱으로 중복을 제거한다. 이 추가 작업
때문에 중복이 없다는 것이 확실하면 UNION ALL 이 더 빠르다. 또 UNION 의 결과 순서는 보장되지 않는다
(정렬로 중복을 지우는 엔진에서 우연히 정렬돼 보일 뿐) — 순서가 필요하면 반드시 ORDER BY.
9.2 INTERSECT / EXCEPT
SELECT dno FROM 부서 INTERSECT SELECT dno FROM 사원 ORDER BY dno; -- 10, 20
SELECT dno FROM 부서 EXCEPT SELECT dno FROM 사원 ORDER BY dno; -- 30, 40
| INTERSECT | EXCEPT (부서 − 사원) |
|---|---|
| 10 | 30 |
| 20 | 40 |
EXCEPT 는 순서가 중요하다. 거꾸로 사원 EXCEPT 부서 를 하면:
SELECT dno FROM 사원 EXCEPT SELECT dno FROM 부서;
| dno |
|---|
| NULL |
9.3 집합 연산에서 NULL 은 NULL 과 "같다"
방금 결과가 흥미롭다. EXCEPT 가 사원 쪽 NULL 을 남겼고, §9.1 의 UNION 은 NULL 을 한 번만 남겼다.
집합 연산은 중복 판정을 = 비교가 아니라 "구별되지 않음(IS NOT DISTINCT FROM)" 으로 한다. 그래서
NULL 끼리 같은 값으로 취급된다. (DISTINCT·GROUP BY 가 NULL 을 하나의 그룹으로 묶는 것과 같은 규칙.)
SELECT dno FROM 프로젝트 INTERSECT SELECT dno FROM 사원 ORDER BY dno;
| dno |
|---|
| 10 |
| 20 |
| NULL |
프로젝트 쪽 NULL 과 사원 쪽 NULL 이 "같다"고 봐서 교집합에 들어간다.
SELECT dno FROM 프로젝트 EXCEPT SELECT dno FROM 부서; -- NULL 1행 (부서엔 NULL 이 없으니)
WHERE 비교와의 대조 — 같은 데이터를 서브쿼리로 물으면 NULL 은 사라진다.
| 질문 | 집합 연산 | WHERE / 조인 |
|---|---|---|
| NULL 과 NULL 은? | 같다 (중복 제거·교집합 대상) | UNKNOWN (조건 탈락) |
| 사원.dno 와 프로젝트.dno 공통 | INTERSECT → 10, 20, NULL | IN / JOIN → 10, 20 만 |
9.4 ALL 변형 — 중복을 세는 집합 연산
INTERSECT ALL, EXCEPT ALL 은 중복을 지우지 않고 개수로 계산한다(다중집합, bag).
SELECT dno FROM 사원 EXCEPT ALL SELECT dno FROM 부서 ORDER BY dno; -- PostgreSQL
| 값 | 사원 쪽 개수 | 부서 쪽 개수 | EXCEPT ALL (빼기) | INTERSECT ALL (작은 쪽) |
|---|---|---|---|---|
| 10 | 3 | 1 | 2 | 1 |
| 20 | 3 | 1 | 2 | 1 |
| 30 | 0 | 1 | 0 | 0 |
| 40 | 0 | 1 | 0 | 0 |
| NULL | 1 | 0 | 1 | 0 |
결과: 10, 10, 20, 20, NULL. (사원 INTERSECT ALL 프로젝트 는 10·20 이 각각 min(3,1)=1개, NULL 이
min(1,1)=1개 → 10, 20, NULL.)
9.5 규칙 ① 열 개수와 타입이 맞아야 한다
SELECT dno, dname FROM 부서 UNION SELECT dno FROM 사원; -- 열 개수 2 vs 1
모든 엔진에서 오류다(PostgreSQL: each UNION query must have the same number of columns). 열은 이름이
아니라 위치(1번째 열끼리, 2번째 열끼리) 로 짝지어진다.
SELECT dno FROM 부서 UNION SELECT ename FROM 사원; -- 정수 vs 문자열
| 엔진 | 결과 |
|---|---|
| PostgreSQL | 오류: UNION types integer and character varying cannot be matched |
| Oracle | 오류: ORA-01790: expression must have same datatype as corresponding expression |
| MySQL | 허용 (문자열로 변환) |
| SQLite | 허용 (동적 타입이라 숫자·문자가 한 열에 섞여 11행) |
표준 답은 "각 열의 타입이 호환돼야 한다". 부족한 열은 NULL 이나 상수로 채워 맞춘다.
SELECT ename, sal FROM 사원 WHERE dno = 10
UNION ALL
SELECT dname, NULL FROM 부서 WHERE loc IS NULL;
| ename | sal |
|---|---|
| 김하늘 | 500 |
| 이도윤 | 350 |
| 박서준 | 280 |
| 기획 | NULL |
9.6 규칙 ② 결과 열 이름은 첫 번째 SELECT 를 따른다
SELECT dno AS 번호 FROM 부서 WHERE dno <= 20
UNION
SELECT pno AS 코드 FROM 프로젝트
ORDER BY 번호;
| 번호 |
|---|
| 10 |
| 20 |
| 100 |
| 200 |
| 300 |
열 머리글은 번호 다. 두 번째 SELECT 의 별칭 코드 는 무시된다. 따라서 ORDER BY 에서도 첫 SELECT 의
이름(또는 위치 번호 ORDER BY 1)을 써야 한다. ORDER BY 코드 나 ORDER BY pno 는 PostgreSQL·Oracle 에서
오류다(SQLite 는 관대하게 받아 주지만 표준이 아니다).
9.7 규칙 ③ ORDER BY 는 맨 끝에 한 번
SELECT dno FROM 부서 ORDER BY dno -- ✗ 오류
UNION
SELECT dno FROM 사원;
ORDER BY 는 집합 연산이 끝난 최종 결과 전체에 한 번만 적용된다. 각 SELECT 의 정렬은 어차피 합치는 과정 (중복 제거)에서 의미를 잃기 때문이다. 각 부분을 정렬·제한하고 싶으면 인라인 뷰로 감싼다.
SELECT * FROM (SELECT ename, sal FROM 사원 ORDER BY sal DESC LIMIT 2) a -- 상위 2명
UNION ALL
SELECT * FROM (SELECT ename, sal FROM 사원 ORDER BY sal LIMIT 1) b; -- 하위 1명
| ename | sal |
|---|---|
| 김하늘 | 500 |
| 최지우 | 450 |
| 오세린 | 250 |
(LIMIT 은 PostgreSQL·MySQL·SQLite 문법. Oracle 은 FETCH FIRST n ROWS ONLY(12c+) 또는 ROWNUM.)
9.8 규칙 ④ 우선순위 — INTERSECT 가 먼저
표준 SQL 에서 INTERSECT 는 UNION·EXCEPT 보다 먼저 묶인다(AND 가 OR 보다 먼저인 것과 같은 관계).
UNION 과 EXCEPT 끼리는 같은 순위로 왼쪽부터.
SELECT dno FROM 부서 WHERE dno = 30 -- A = {30}
UNION
SELECT dno FROM 부서 WHERE dno = 10 -- B = {10}
INTERSECT
SELECT dno FROM 사원 -- C = {10, 20, NULL}
ORDER BY dno;
| 해석 | 계산 | 결과 |
|---|---|---|
| 표준: A ∪ (B ∩ C) | {30} ∪ ({10} ∩ {10,20,NULL}) = {30} ∪ {10} | 10, 30 |
| 왼쪽부터: (A ∪ B) ∩ C | {30,10} ∩ {10,20,NULL} | 10 |
| 엔진 | 동작 |
|---|---|
| 표준 · PostgreSQL · MySQL 8.0.31+ · SQL Server | INTERSECT 우선 → 10, 30 |
| Oracle · SQLite | 모든 집합 연산자 동일 순위, 왼쪽부터 → 10 |
엔진마다 다르니 섞어 쓸 때는 괄호로 의도를 고정하는 것이 정답이다. PostgreSQL 은
A UNION (B INTERSECT C)처럼 괄호를 받는다(SQLite 는 이 괄호를 구문 오류로 거부하므로SELECT * FROM (B INTERSECT C)처럼 인라인 뷰로 감싼다).
9.9 엔진별 지원 요약
| UNION / UNION ALL | INTERSECT | EXCEPT | MINUS | INTERSECT ALL / EXCEPT ALL | |
|---|---|---|---|---|---|
| 표준 SQL | ○ | ○ | ○ | — | ○ |
| Oracle | ○ | ○ | 21c+ | ○ (전통 이름) | 21c+ |
| PostgreSQL | ○ | ○ | ○ | ✗ | ○ |
| MySQL | ○ | 8.0.31+ | 8.0.31+ | ✗ | 8.0.31+ |
| SQLite | ○ | ○ | ○ | ✗ | ✗ |
정보처리기사에서 차집합 연산자를 묻는 빈칸은 MINUS(Oracle) 또는 EXCEPT(표준) — 보기·지문의 표기를
따른다.
9.10 같은 데이터, 네 연산 한눈에
10번 부서 직급 = [팀장, 대리, 사원], 20번 부서 직급 = [팀장, 사원, 사원].
| 연산 | 결과 (정렬) | 행 수 |
|---|---|---|
UNION | 대리, 사원, 팀장 | 3 |
UNION ALL | 팀장, 대리, 사원, 팀장, 사원, 사원 | 6 |
INTERSECT | 사원, 팀장 | 2 |
EXCEPT (10 − 20) | 대리 | 1 |
확인 문제
1. (결과 쓰기) 다음 쿼리의 결과 행 수는?
SELECT dname FROM 부서
WHERE dno NOT IN (SELECT dno FROM 프로젝트);
2. (결과 쓰기) 다음 쿼리가 반환하는 ename 을 모두 쓰시오. (ANY/ALL 지원 엔진 기준)
SELECT ename FROM 사원
WHERE sal > ALL (SELECT sal FROM 사원 WHERE job = '대리');
3. (결과 쓰기) 다음 쿼리의 결과는? (30번 부서에는 사원이 없다)
SELECT COUNT(*) FROM 사원
WHERE sal > ALL (SELECT sal FROM 사원 WHERE dno = 30);
4. (빈칸) 각 부서에서 급여가 가장 높은 사원을 구하는 상관 서브쿼리를 완성하시오.
SELECT ename, dno, sal FROM 사원 e1
WHERE sal = (SELECT MAX(sal) FROM 사원 e2 WHERE ( ① ));
5. (결과 쓰기) 두 쿼리의 결과를 각각 쓰시오.
SELECT COUNT(*) FROM (SELECT job FROM 사원 UNION SELECT job FROM 사원 WHERE dno = 10) t;
SELECT COUNT(*) FROM (SELECT job FROM 사원 UNION ALL SELECT job FROM 사원 WHERE dno = 10) t;
6. (결과 쓰기) 다음 쿼리의 결과 행을 쓰시오.
SELECT dno FROM 프로젝트 EXCEPT SELECT dno FROM 부서;
7. (OX)
- (가)
UNION으로 합칠 때 각 SELECT 마다 ORDER BY 를 붙일 수 있다. - (나)
<> ANY (서브쿼리)는NOT IN (서브쿼리)와 같다. - (다) EXISTS 서브쿼리의 SELECT 목록을
SELECT *로 쓰든SELECT 1로 쓰든 결과는 같다. - (라) 집합 연산의 결과 열 이름은 마지막 SELECT 의 별칭을 따른다.
8. (결과 쓰기) 참여 사원이 2명 이상인 프로젝트를 구하는 다음 쿼리의 결과 행 수는?
SELECT pname FROM 프로젝트 p
WHERE (SELECT COUNT(*) FROM 참여 c WHERE c.pno = p.pno) >= 2;
정답과 풀이
1. 0행. 프로젝트.dno 에 NULL(신규 프로젝트)이 있다. dno NOT IN (10, 20, NULL) 은 마지막 항
dno <> NULL 이 UNKNOWN 이라 어떤 행도 TRUE 가 될 수 없다(§5.1). 30·40번 부서가 나올 것 같지만 함정.
2. 김하늘, 최지우. 대리는 이도윤 한 명(350)뿐이라 > ALL (350) = 350 초과 → 500, 450.
3. 7. 서브쿼리가 빈 집합이면 > ALL 은 공허하게 참이다(§3.3). > (SELECT MAX …) 였다면 MAX 가
NULL 이라 0.
4. ① e2.dno = e1.dno. 바깥 행의 부서 번호를 안쪽에 넘겨 "그 부서의 MAX" 를 행마다 다시 계산한다.
결과는 김하늘(10, 500), 최지우(20, 450). (dno 가 NULL 인 오세린은 MAX 가 NULL 이 돼 빠진다.)
5. 3 / 10. 직급 종류는 팀장·대리·사원 3개라 UNION 은 중복을 지워 3. UNION ALL 은 7행 + 10번 부서 3행 = 10.
6. NULL 1행. 프로젝트.dno = {10, 20, NULL}, 부서.dno = {10, 20, 30, 40}. 집합 연산은 NULL 을 하나의 값으로 다루므로 NULL 이 부서 쪽에 없어 남는다(§9.3).
7. (가) X — ORDER BY 는 맨 끝에 한 번(§9.7). (나) X — NOT IN 의 짝은 <> ALL(§3.3).
(다) O — EXISTS 는 행의 존재만 본다(§4). (라) X — 첫 번째 SELECT(§9.6).
8. 2행 (결제, 광고). 결제 참여 3건, 광고 2건, 신규 0건. 행마다 COUNT 를 다시 세는 상관 서브쿼리다.
한 줄 요약
| 주제 | 한 줄 |
|---|---|
| 위치 | SELECT 절 = 스칼라(1행 1열, 0행이면 NULL), FROM 절 = 인라인 뷰(임시 테이블), WHERE·HAVING = 조건값 |
| 반환 형태 | 단일행은 = > <, 다중행은 IN ANY ALL EXISTS, 다중열은 (a, b) IN |
| 단일행 연산자 + 다중행 | 오류 (SQLite 만 첫 행으로 진행 — 비표준) |
| ANY / ALL | > ANY = 최소보다 큼, > ALL = 최대보다 큼, = ANY = IN, <> ALL = NOT IN |
| 빈 집합 | ALL 은 참, ANY 는 거짓, MAX/MIN 비교는 NULL 이라 거짓 |
| NOT IN 함정 | 서브쿼리에 NULL 하나만 있어도 결과 0행 → IS NOT NULL 필터나 NOT EXISTS |
| EXISTS | 행이 있으면 참 — TRUE/FALSE 만 내므로 NULL 에 안전, SELECT 목록 무관 |
| 상관 서브쿼리 | 바깥 열을 참조, 논리적으로 바깥 행마다 재실행 → 그룹 내 비교·Top-N |
| CTE | WITH 이름 AS (…) — 이름 붙인 인라인 뷰, WITH RECURSIVE 로 계층 탐색 |
| 서브쿼리 → 조인 | IN 은 조인 시 중복 주의, NOT EXISTS = LEFT JOIN + IS NULL, 스칼라 = LEFT JOIN |
| 집합 연산 | UNION(중복 제거) / UNION ALL(유지) / INTERSECT / EXCEPT(=MINUS) |
| 집합 규칙 | 열 개수·타입 일치, 열 이름은 첫 SELECT, ORDER BY 는 맨 끝 한 번 |
| 집합과 NULL | 집합 연산은 NULL 끼리 같다고 본다 (WHERE 의 = 와 반대) |
| 우선순위 | 표준은 INTERSECT 먼저 — Oracle·SQLite 는 왼쪽부터, 괄호로 고정 |