조인·디비전과 관계해석

“한 과목이라도 수강했다”와 “필수 과목을 모두 수강했다”는 다른 질문입니다. 첫 질문은 일치하는 행 하나를 찾으면 끝나지만, 두 번째는 빠진 필수 과목이 하나도 없어야 합니다. 이 차이를 중간 결과와 논리식으로 따라가 봅니다.

조인의 조건과 결과 열

세타 조인은 두 관계의 곱에서 비교 조건을 만족하는 쌍을 선택합니다. 비교는 =, <, > 등일 수 있습니다. 동등 조인은 그중 같음 조건을 쓰는 조인입니다. 자연 조인은 공통 이름의 속성을 자동으로 동등 비교하고 공통 속성을 결과에 한 번만 둡니다.

학생.이름과 교사.이름이 우연히 같은 열 이름이어도 같은 관계 키라는 뜻은 아닙니다. NATURAL JOIN은 새 공통 열이 추가되면 조건까지 달라지므로 서비스 SQL에서는 명시적인 ON 조건이 변경 의도를 드러내기 좋습니다. 자연 조인의 논리와 도구의 편의성을 구분합니다.

세미 조인은 일치 상대가 있는 왼쪽 튜플만 남깁니다. 상대가 세 개라고 왼쪽 회원을 세 번 복제하지 않습니다. SQL의 EXISTS가 이 의미를 표현합니다. OUTER JOIN의 NULL 보충은 순수 집합 조인의 정의에 없는 확장이며, COUNT(*)와 COUNT(오른쪽키)의 차이도 함께 봅니다.

독립 데이터와 일치 행

CREATE DATABASE IF NOT EXISTS cs_db_lab;
USE cs_db_lab;
DROP TABLE IF EXISTS cs_div_enroll;
DROP TABLE IF EXISTS cs_div_required;
DROP TABLE IF EXISTS cs_div_students;
CREATE TABLE cs_div_students (student_id INT PRIMARY KEY,name VARCHAR(20) NOT NULL);
CREATE TABLE cs_div_required (course VARCHAR(20) PRIMARY KEY);
CREATE TABLE cs_div_enroll (
  student_id INT NOT NULL,course VARCHAR(20) NOT NULL,
  PRIMARY KEY(student_id,course),
  FOREIGN KEY(student_id) REFERENCES cs_div_students(student_id)
);
INSERT INTO cs_div_students VALUES (1,'민'),(2,'준'),(3,'솔'),(4,'라');
INSERT INTO cs_div_required VALUES ('DB'),('OS');
INSERT INTO cs_div_enroll VALUES (1,'DB'),(1,'OS'),(2,'DB'),(3,'OS');
SELECT s.student_id,s.name,e.course
FROM cs_div_students AS s JOIN cs_div_enroll AS e ON e.student_id=s.student_id
ORDER BY s.student_id,e.course;
SELECT s.student_id,s.name FROM cs_div_students AS s
WHERE EXISTS(SELECT 1 FROM cs_div_enroll AS e WHERE e.student_id=s.student_id)
ORDER BY s.student_id;

일반 조인은 민 두 행, 준·솔 각 한 행의 4행입니다. EXISTS 결과는 민·준·솔의 3행입니다. 학생 라에게는 수강 행이 없으므로 제외됩니다. 중복을 무작정 DISTINCT로 지우기 전에 조회하려는 것이 수강 사실인지 학생 존재인지 정합니다.

디비전을 빠진 쌍으로 이해하기

수강 R(학생,과목)을 필수 과목 D(과목)로 나누면 D의 모든 과목과 짝이 있는 학생을 얻습니다. 전통적인 관계대수 디비전의 후보 C는 π학생(R)입니다.

1. 후보 C는 {1,2,3}입니다.
2. C×D는 6쌍입니다.
3. 그중 R에 없는 쌍은 (2,OS),(3,DB)입니다.
4. 빠진 쌍에 등장한 학생 {2,3}을 C에서 빼면 {1}입니다.

식으로 쓰면 C − π학생((C × D) − R)입니다. 왼쪽 수강에 필수 과목 외 과목이 있어도 필수 조건을 방해하지 않습니다.

SELECT DISTINCT c.student_id
FROM cs_div_enroll AS c
WHERE NOT EXISTS (
  SELECT 1 FROM cs_div_required AS r
  WHERE NOT EXISTS (
    SELECT 1 FROM cs_div_enroll AS e
    WHERE e.student_id=c.student_id AND e.course=r.course
  )
)
ORDER BY c.student_id;

안쪽 NOT EXISTS는 “이 학생이 수강하지 않은 필수 과목”을 찾습니다. 바깥 NOT EXISTS는 그런 과목이 하나도 없다는 뜻입니다. 민의 빠진 과목은 0개, 준·솔은 각각 1개여서 결과는 학생 1입니다.

관계해석과 빈 필수 집합

관계대수는 어떤 연산을 조합하는지 설명합니다. 관계해석은 결과가 만족해야 할 논리 조건을 기술합니다. 같은 질의를 “학생 s가 후보이고, 모든 필수 과목 r에 대해 수강(s,r)이 존재한다”라고 쓸 수 있습니다. 모든 항목을 만족해야 한다는 조건은 “빠진 항목이 존재하지 않는다”로 바꿀 수 있어 SQL과 연결됩니다.

실제 저장된 관계의 값과 정해진 후보 영역 안에서 결과를 구하는 안전한 조건을 사용합니다. 저장되지 않은 모든 정수 중 어떤 값을 제외한 집합처럼 무한 결과를 요구하지 않습니다.

필수 과목 집합이 비면 반례가 없으므로 모든 후보가 통과합니다. 위 조회의 후보는 수강 표에 있는 1·2·3이어서 라(4)는 돌아오지 않습니다. 모든 등록 학생을 후보로 삼는 업무 규칙이면 아래처럼 학생 표에서 시작합니다. 이것은 후보 영역을 확장한 업무 질의입니다.

SELECT s.student_id,s.name FROM cs_div_students AS s
WHERE NOT EXISTS (
  SELECT 1 FROM cs_div_required AS r
  WHERE NOT EXISTS (
    SELECT 1 FROM cs_div_enroll AS e
    WHERE e.student_id=s.student_id AND e.course=r.course
  )
)
ORDER BY s.student_id;

현재 결과는 여전히 민입니다. START TRANSACTION; DELETE FROM cs_div_required; 후 두 조회를 비교하면 각각 1·2·3과 1·2·3·4가 됩니다. 확인 후 ROLLBACK으로 필수 목록을 복구합니다. 필수 목록이 비면 아무도 통과시키지 않는 정책이라면 별도 EXISTS(SELECT 1 FROM cs_div_required) 조건이 필요합니다.

COUNT로 같은 모두 조건을 구성하기

먼저 등록 학생 전체를 후보로 정합니다. 후보를 LEFT JOIN으로 보존하고, 필수 과목에 일치하는 서로 다른 수강 과목만 셉니다. 추가 과목은 세지 않고 수강 없는 후보도 중간 표에서 사라지지 않습니다.

WITH matched AS (
  SELECT s.student_id,
         COUNT(DISTINCT r.course) AS matched_required
  FROM cs_div_students AS s
  LEFT JOIN cs_div_enroll AS e ON e.student_id=s.student_id
  LEFT JOIN cs_div_required AS r ON r.course=e.course
  GROUP BY s.student_id
)
SELECT student_id,matched_required,
       (SELECT COUNT(*) FROM cs_div_required) AS required_count
FROM matched ORDER BY student_id;

SELECT s.student_id,s.name
FROM cs_div_students AS s
LEFT JOIN cs_div_enroll AS e ON e.student_id=s.student_id
LEFT JOIN cs_div_required AS r ON r.course=e.course
GROUP BY s.student_id,s.name
HAVING COUNT(DISTINCT r.course)=(SELECT COUNT(*) FROM cs_div_required)
ORDER BY s.student_id;

수강에 (2,'NETWORK')를 추가해도 준의 필수 일치 수는 1입니다. 기본 fixture의 PRIMARY KEY는 동일 학생·과목 중복 자체를 막지만, 중복이 가능한 원시 수강 로그에 이 논리를 적용할 때도 COUNT(DISTINCT r.course)가 필요합니다. 다음 CTE는 실제 표를 바꾸지 않고 추가 과목과 중복 로그를 함께 확인합니다.

WITH raw_enroll AS (
  SELECT student_id,course FROM cs_div_enroll
  UNION ALL SELECT 1,'DB'
  UNION ALL SELECT 2,'NETWORK'
)
SELECT s.student_id,COUNT(DISTINCT r.course) AS matched_required
FROM cs_div_students AS s
LEFT JOIN raw_enroll AS e ON e.student_id=s.student_id
LEFT JOIN cs_div_required AS r ON r.course=e.course
GROUP BY s.student_id ORDER BY s.student_id;
-- 예상: 1→2, 2→1, 3→1, 4→0

빈 필수 목록은 START TRANSACTION; DELETE FROM cs_div_required; 후 위 집계와 결과를 다시 실행합니다. 중간 표는 네 후보 모두 matched_required=0,required_count=0이고 COUNT 결과도 1·2·3·4입니다. ROLLBACK;으로 복구합니다. INNER JOIN으로 필수 표에서 시작하면 빈 목록 때 후보가 사라져 이 의미를 표현하지 못합니다.

전통적인 디비전 후보만 쓰려면 학생 조회에 WHERE EXISTS(SELECT 1 FROM cs_div_enroll AS c WHERE c.student_id=s.student_id)를 GROUP BY 앞에 넣습니다. 그러면 빈 필수 목록에서 1·2·3만 통과합니다. 빈 필수 목록에서 아무도 통과시키지 않는 정책은 HAVING에 AND (SELECT COUNT(*) FROM cs_div_required)>0을 더합니다. SQL 기법보다 후보와 빈 집합 정책을 먼저 선택합니다.

독립 연습

필수 과목을 {DB,OS}로 유지하고 준에게 OS를 추가하면 두 조회는 누구를 돌려줄까요? 다음으로 필수 과목에 NETWORK를 추가하면 어떤 중간 집합이 바뀌나요? COUNT 방식으로 풀 경우 추가 수강 과목과 중복 수강을 어떻게 다뤄야 할지도 설명하세요.

조인은 맞는 쌍, 세미 조인은 상대의 존재, 디비전은 정해진 후보의 모든 조건을 표현합니다. 결과 행 수와 빈 집합 정책을 함께 확인합니다.