왜 조인·서브쿼리·집합 연산이 필요한가

정규화(자세한 원리는 15·16편)를 거친 관계형 데이터베이스는 하나의 정보를 여러 테이블에 나누어 저장합니다. 학생 정보는 학생 테이블에, 과목 정보는 과목 테이블에, “누가 어떤 과목을 들었는지”는 수강 테이블에 따로 있습니다. 그런데 실제로 필요한 답(“컴퓨터공학과 학생 중 데이터베이스 과목을 들은 사람은?”)은 이 테이블들을 다시 이어 붙여야 나옵니다. 이 “다시 이어 붙이는” 연산이 조인(join)입니다. 조인만으로 표현하기 번거로운 조건(예: “한 번도 수강하지 않은 학생”)은 서브쿼리(subquery, 중첩 질의)로 풀고, “두 조건을 모두 만족하는 것”이나 “한쪽에만 있는 것”은 집합 연산(set operation)으로 풉니다.

쉽게 말하면: 조인은 “테이블을 옆으로 이어 붙이기”, 서브쿼리는 “질의 안에 또 다른 질의를 넣어 조건으로 쓰기”, 집합 연산은 “두 질의 결과표를 위아래로 비교하기”입니다.

이 편의 SQL 예제는 이 사이트의 SQLD 과목과 문법을 맞추기 위해 Oracle 문법을 기준으로 씁니다. 표준 SQL과 이름이 다른 부분(대표적으로 MINUS)은 그때그때 짚어 드립니다.

예제로 쓸 세 테이블

이 편과 다음 편(14편)에서 계속 사용할 예제 스키마입니다. 학번·과목코드에는 일부러 정합성이 깨진 값(존재하지 않는 과목코드를 참조하는 수강 기록)을 하나 심어 두었는데, 이는 OUTER JOIN과 NOT IN의 함정을 실제 데이터로 보여주기 위한 의도적 장치입니다(실제 시험 문제에서도 “다음 데이터가 주어졌을 때”라는 형태로 이런 예외 행을 넣어 이해도를 시험합니다).

STUDENT(학번, 이름, 학과)

COURSE(과목코드, 과목명, 학점)

ENROLL(학번, 과목코드, 성적)

S6(강서준)은 어떤 과목도 신청한 적이 없고, S5의 마지막 행은 COURSE 테이블에 없는 C005를 참조합니다(과목이 폐강되었거나 데이터 오류로 남은 행이라고 가정합니다). S3의 C001 성적은 아직 채점 전이라 NULL입니다.

1. INNER JOIN — 양쪽 모두에 있는 것만

내부 조인(INNER JOIN)은 두 테이블에서 조인 조건을 만족하는 행, 즉 양쪽 모두에 대응하는 값이 있는 행만 결과에 남깁니다.

SELECT e.학번, e.과목코드, c.과목명
FROM ENROLL e
INNER JOIN COURSE c ON e.과목코드 = c.과목코드;

실행 과정을 손으로 따라가 봅니다. ENROLL의 각 행이 COURSE에서 같은 과목코드를 가진 행과 만나는지 하나씩 확인합니다.

결과는 6행이며, C004(인공지능)는 ENROLL에 신청한 사람이 없으므로 처음부터 등장하지 않습니다. INNER JOIN은 양쪽 모두 짝이 있는 행만 남기므로, 짝이 없는 쪽의 정보는 통째로 사라진다는 점이 핵심입니다.

2. OUTER JOIN — 한쪽(또는 양쪽) 정보를 보존

외부 조인(OUTER JOIN)은 짝이 없는 행도 버리지 않고, 짝이 없는 쪽 컬럼을 NULL로 채워 결과에 남깁니다. “어느 쪽을 보존하는가”에 따라 세 가지로 나뉩니다.

LEFT OUTER JOIN — 왼쪽 테이블 전부 보존

SELECT e.학번, e.과목코드, c.과목명
FROM ENROLL e
LEFT OUTER JOIN COURSE c ON e.과목코드 = c.과목코드;

ENROLL(왼쪽)의 모든 행을 남기고, 짝이 없는 S5, C005 행은 과목명을 NULL로 채웁니다.

INNER JOIN 결과(6행)에 S5, C005, NULL 한 행이 더 붙어 7행이 됩니다.

RIGHT OUTER JOIN — 오른쪽 테이블 전부 보존

SELECT e.학번, e.과목코드, c.과목명
FROM ENROLL e
RIGHT OUTER JOIN COURSE c ON e.과목코드 = c.과목코드;

이번에는 COURSE(오른쪽)의 모든 행을 남깁니다. 신청자가 없는 C004(인공지능)가 학번 NULL로 등장합니다.

S5, C005 행은 COURSE에 C005가 없으므로 이번에는 결과에서 빠집니다. RIGHT JOIN은 “오른쪽 기준”이라는 점을 거꾸로 외우기 쉬우므로, FROM A RIGHT JOIN B는 “B를 다 살린다”고 소리 내어 확인하는 습관이 유용합니다.

FULL OUTER JOIN — 양쪽 모두 보존

SELECT e.학번, e.과목코드, c.과목명
FROM ENROLL e
FULL OUTER JOIN COURSE c ON e.과목코드 = c.과목코드;

LEFT JOIN 결과와 RIGHT JOIN 결과를 합친 것과 같습니다. S5, C005, NULL(왼쪽만 있던 행)과 NULL, C004, 인공지능(오른쪽만 있던 행)이 모두 포함되어 총 8행이 됩니다.

3. CROSS JOIN — 조건 없는 카티전 곱

교차 조인(CROSS JOIN)은 조인 조건 없이 두 테이블의 모든 행 조합을 만드는 카티전 곱(Cartesian product)입니다. STUDENT(6행)와 COURSE(4행)를 CROSS JOIN하면 6 × 4 = 24행이 나옵니다.

SELECT s.이름, c.과목명
FROM STUDENT s
CROSS JOIN COURSE c;

이 24행에는 “김민준이 실제로 데이터베이스를 들었는가”와 무관하게 모든 학생-과목 조합이 다 들어갑니다. 실무에서 CROSS JOIN을 의도적으로 쓰는 경우는 드물지만, 시험에서는 다음 함정으로 자주 나옵니다.

JOIN 종류 비교

4. 서브쿼리 — 질의 안의 질의

서브쿼리(subquery, 하위 질의·중첩 질의)는 다른 SQL문 안에 괄호로 둘러싸여 들어가는 SELECT문입니다. 바깥 질의(outer query)의 조건절이나 SELECT 목록 안에서 사용됩니다.

스칼라 서브쿼리 — 값 하나를 반환

스칼라(scalar)는 “값 하나”라는 뜻입니다. 스칼라 서브쿼리는 정확히 한 행, 한 컬럼(즉 값 하나)을 반환해야 하며, 주로 SELECT 목록이나 비교 연산자(=, > 등) 오른쪽에 씁니다.

SELECT s.이름,
       (SELECT COUNT(*) FROM ENROLL e WHERE e.학번 = s.학번) AS 수강과목수
FROM STUDENT s;

학생마다 몇 과목을 신청했는지 세는 상관 서브쿼리(correlated subquery, 바깥 질의의 값 s.학번을 안쪽에서 참조하는 서브쿼리)입니다. 계산 결과는 다음과 같습니다.

IN 서브쿼리 — 값 목록에 포함되는지

SELECT 이름
FROM STUDENT
WHERE 학번 IN (SELECT 학번 FROM ENROLL WHERE 과목코드 = 'C001');

ENROLL에서 과목코드 = 'C001'인 행의 학번 목록은 {S1, S2, S3}입니다. 이 집합에 속하는 학생 이름을 뽑으면 김민준·이서연·박도윤 3명입니다.

EXISTS / NOT EXISTS — 존재 여부만 확인

EXISTS는 서브쿼리가 행을 하나라도 반환하면 참이 되는 연산자로, 값 자체가 아니라 “존재하는가”만 봅니다. 보통 상관 서브쿼리와 함께 씁니다.

SELECT 이름
FROM STUDENT s
WHERE EXISTS (
  SELECT 1 FROM ENROLL e WHERE e.학번 = s.학번 AND e.과목코드 = 'C001'
);

위 IN 서브쿼리와 동일하게 김민준·이서연·박도윤이 나옵니다. SELECT 1처럼 아무 값이나 넣는 이유는 EXISTS가 행의 존재 여부만 보고 실제 값은 보지 않기 때문입니다.

NOT IN / NOT EXISTS의 함정 — 한 번도 신청하지 않은 학생 찾기

“한 번도 수강 신청을 하지 않은 학생”을 찾는 문제로 NOT IN과 NOT EXISTS의 차이를 직접 비교해 보겠습니다.

-- 방법 A: NOT IN
SELECT 이름
FROM STUDENT
WHERE 학번 NOT IN (SELECT 학번 FROM ENROLL);

기대하는 답은 강서준(S6) 한 명입니다. 그런데 ENROLL.학번에 NULL이 하나라도 섞여 있으면 이 질의는 아무 행도 반환하지 않습니다. NOT IN은 내부적으로 학번 <> 값1 AND 학번 <> 값2 AND ...처럼 풀리는데, 목록에 NULL이 있으면 그 비교(학번 <> NULL)는 참도 거짓도 아닌 UNKNOWN이 되어 AND로 묶인 전체 조건이 참이 될 수 없기 때문입니다.

-- 방법 B: NOT EXISTS (안전)
SELECT 이름
FROM STUDENT s
WHERE NOT EXISTS (
  SELECT 1 FROM ENROLL e WHERE e.학번 = s.학번
);

NOT EXISTS는 값 목록을 만들어 비교하는 방식이 아니라 상관 서브쥐리로 “이 학생의 학번과 일치하는 행이 하나도 없는가”를 직접 확인하므로, ENROLL.학번에 NULL이 섞여 있어도 영향을 받지 않고 정확히 강서준 한 명을 반환합니다.

5. 집합 연산 — UNION / UNION ALL / INTERSECT / MINUS

집합 연산(set operation)은 두 SELECT문의 결과(각각 같은 개수·같은 순서의 호환되는 자료형 컬럼을 가져야 합니다)를 위아래로 비교합니다. 컴퓨터공학과 학생 목록과 C001(데이터베이스) 수강생 목록으로 예를 들어 보겠습니다.

• 컴퓨터공학과 학생: {S1, S2, S4} (김민준·이서연·최지우)
• C001 수강생: {S1, S2, S3} (김민준·이서연·박도윤)

SELECT 학번 FROM STUDENT WHERE 학과 = '컴퓨터공학'
UNION
SELECT 학번 FROM ENROLL WHERE 과목코드 = 'C001';

UNION은 두 결과를 합치고 중복을 제거합니다. 결과는 {S1, S2, S3, S4} 4행입니다. 중복을 제거하지 않고 그대로 다 합치려면 UNION ALL을 쓰며, 이 경우 S1, S2가 두 번씩 나와 총 6행이 됩니다. UNION은 중복 제거를 위해 내부적으로 정렬·비교 작업을 하므로 UNION ALL보다 비용이 크며, 중복이 문제되지 않는 상황이라면 UNION ALL이 더 효율적입니다.

SELECT 학번 FROM STUDENT WHERE 학과 = '컴퓨터공학'
INTERSECT
SELECT 학번 FROM ENROLL WHERE 과목코드 = 'C001';

INTERSECT(교집합)는 양쪽 모두에 있는 값만 남깁니다. {S1, S2, S4} ∩ {S1, S2, S3} = {S1, S2}이므로 결과는 2행입니다.

SELECT 학번 FROM STUDENT WHERE 학과 = '컴퓨터공학'
MINUS
SELECT 학번 FROM ENROLL WHERE 과목코드 = 'C001';

MINUS(차집합)는 첫 번째 결과에서 두 번째 결과에 있는 값을 뺍니다. {S1, S2, S4} − {S1, S2, S3} = {S4}이므로 최지우 한 명만 남습니다. 표준 SQL·다른 DBMS(MySQL, SQL Server 등)에서는 같은 연산을 EXCEPT라는 키워드로 씁니다.

집합 연산 비교

핵심 정리

• INNER JOIN은 양쪽 모두 짝이 있는 행만, OUTER JOIN은 짝이 없는 쪽을 NULL로 채워서라도 보존하며, LEFT·RIGHT·FULL은 어느 쪽을 보존하는지의 차이다.
• CROSS JOIN(카티전 곱)은 조건 없는 모든 조합이며, 콤마 조인에서 조인 조건을 빠뜨리면 의도치 않게 CROSS JOIN이 된다.
• LEFT/RIGHT OUTER JOIN 뒤에 오른쪽/왼쪽 컬럼에 대한 WHERE 조건을 걸면 NULL인 행이 걸러져 결과적으로 INNER JOIN처럼 동작하는 함정이 있다.
• 스칼라 서브쿼리는 값 하나, IN은 목록 포함 여부, EXISTS는 행의 존재 여부를 확인하며, 서브쿼리 결과에 NULL이 섞이면 NOT IN은 빈 결과가 될 수 있지만 NOT EXISTS는 안전하다.
• UNION(중복 제거)·UNION ALL(중복 유지)·INTERSECT(교집합)·MINUS(Oracle의 차집합, 표준 SQL은 EXCEPT)는 같은 컬럼 구조를 가진 두 질의 결과를 집합처럼 비교한다.

마무리 복습

참고 자료

• Oracle Database SQL Language Reference