데이터베이스 정규화와 SQL 판단법

LEFT JOIN의 ON과 WHERE는 바꿔 쓸 수 있을까?

모든 부서를 남기되 급여가 100 이상인 직원만 연결하려 합니다. 직원이 없는 부서도 결과에 있어야 합니다.

SQL

SELECT d.id, e.name
FROM dept d
LEFT JOIN emp e
  ON e.dept_id = d.id AND e.salary >= 100
ORDER BY d.id, e.name;

넓은 표는 좌우로 밀어 나머지 열을 볼 수 있습니다.

풀이 순서

1. ON은 오른쪽 행이 조인에 성공하는 조건을 정합니다. 왼쪽 행에 매칭되는 직원이 없으면 LEFT JOIN이 NULL 확장 행을 만듭니다.
2. 급여 조건을 WHERE로 옮기면 조인 결과를 만든 다음 필터링합니다. WHERE는 TRUE인 행만 남기므로 FALSE뿐 아니라 UNKNOWN인 행도 제거합니다.

확인 결과

10 | 민
20 | NULL
30 | NULL

틀리기 쉬운 지점: LEFT JOIN을 썼다는 이유만으로 왼쪽의 모든 행이 최종 결과에 남는 것은 아닙니다. 뒤의 WHERE 조건까지 확인하세요.

COUNT(*)와 COUNT(오른쪽 열)의 차이

앞 예제의 ON 조건을 그대로 사용하여 부서별 적합한 직원 수를 구합니다. emp.name은 NULL이 없다고 가정합니다.

SQL

SELECT d.id, COUNT(*) AS joined_rows,
       COUNT(e.name) AS employees
FROM dept d
LEFT JOIN emp e
  ON e.dept_id = d.id AND e.salary >= 100
GROUP BY d.id
ORDER BY d.id;

넓은 표는 좌우로 밀어 나머지 열을 볼 수 있습니다.

풀이 순서

1. COUNT(*)는 행 자체를 셉니다. 직원이 연결되지 않은 부서에도 조인 결과 행은 하나 있으므로 1이 됩니다.
2. COUNT(e.name)은 해당 식이 NULL이 아닌 행만 셉니다. 이 예제에서는 적합한 직원 수를 세려는 목적에 맞습니다.

확인 결과

10 | 1 | 1
20 | 1 | 0
30 | 1 | 0

틀리기 쉬운 지점: 원래부터 NULL을 허용하는 직원 이름 열이라면 실제 직원도 빠질 수 있습니다. 실제 설계에서는 NULL이 아닌 직원 식별자 열을 세는 편이 명확합니다.

다시 조인하면 복원되는데, 예약 충돌은 왜 못 막을까?

Booking(room, slot, team)은 방·시간대·팀을 기록합니다. 한 방의 같은 시간대에는 한 팀만 예약하고, 한 팀은 정해진 방 하나만 사용합니다. 함수 종속은 {room, slot} → team과 team → room 및 이들로부터 유도되는 것뿐입니다. 모든 값은 원자값이며 NULL과 중복 행은 없습니다. 모든 후보키와 3NF·BCNF 충족 여부를 판단한 뒤 TeamRoom(team, room), TeamSlot(team, slot)으로 나누면 무손실 분해와 종속성 보존을 모두 얻는지 예상하세요. SQL은 비어 있는 연습용 DB에서 실행합니다.

SQL

CREATE TABLE Booking (
    room VARCHAR(1) NOT NULL,
    slot VARCHAR(2) NOT NULL,
    team VARCHAR(8) NOT NULL,
    PRIMARY KEY (room, slot)
);
INSERT INTO Booking VALUES ('A', '09', 'Red');
INSERT INTO Booking VALUES ('A', '10', 'Red');
INSERT INTO Booking VALUES ('B', '09', 'Blue');

CREATE TABLE TeamRoom AS
SELECT DISTINCT team, room FROM Booking;
CREATE TABLE TeamSlot AS
SELECT DISTINCT team, slot FROM Booking;

SELECT r.room, s.slot, r.team
FROM TeamRoom r JOIN TeamSlot s ON r.team = s.team
ORDER BY r.room, s.slot, r.team;

넓은 표는 좌우로 밀어 나머지 열을 볼 수 있습니다.

풀이 순서

1. slot은 어느 종속의 오른쪽에도 없으므로 모든 후보키에 필요합니다. 여기에 room 또는 team을 더하면 세 속성을 모두 구할 수 있고, 하나라도 빼면 안 됩니다. 후보키는 {room, slot}, {team, slot} 두 개뿐입니다. 표에 우연히 다른 유일한 열 조합이 보여도 업무 규칙에서 추가 종속을 추측하지 않습니다.
2. room·slot·team 모두 주요 속성입니다. 따라서 모든 비자명 종속의 오른쪽 속성이 주요 속성이라는 3NF 조건을 만족합니다. 그러나 team만으로 slot을 알 수 없으므로 team → room은 BCNF 조건을 어깁니다.
3. 두 분해 스키마의 공통 속성은 team이고 team → room이므로 공통 속성이 TeamRoom 전체를 결정합니다. 이것이 무손실 분해의 근거입니다. SQL의 원본 복원은 한 입력에 대한 실행 확인이며, 무손실 성질의 일반적 근거는 이 함수 종속입니다.
4. 독립 반례에서는 각 팀에 방이 하나이고 각 (team, slot)도 유일합니다. 두 표가 각자의 종속을 지켜도 같은 (room, slot)에 두 팀이 예약됩니다. {room, slot} → team은 두 표의 종속만으로 보존되지 않으므로 조인 검사나 별도의 교차 표 제약이 필요합니다. 이것은 유효한 원본을 분해한 결과가 아니라, 분해된 표를 따로 갱신할 때 허용될 수 있는 상태입니다.
5. 다시 풀기: 반례에서 Blue의 방만 B로 바꾸면 조인 결과는 (A, 09, Red), (B, 09, Blue)가 되어 이번 충돌은 사라집니다. 그렇다고 분해 자체가 종속성 보존으로 바뀌지는 않습니다. 종속성 보존은 한 데이터 상태가 아니라 스키마와 주어진 종속의 성질입니다.

확인 결과

A | 09 | Red
A | 10 | Red
B | 09 | Blue

틀리기 쉬운 지점: 무손실은 원래의 유효한 데이터를 복원하는 성질이고, 종속성 보존은 분해된 표의 종속 검사로 원래 종속도 보장하는 성질입니다. SQL의 Booking 기본키는 일부 제약만 표현하며 team → room까지 자동 강제하지는 않습니다. 이 예제는 주어진 종속을 만족하는 입력에서 출발합니다.

검증 방법과 참고 문서

표의 동일 데이터를 로컬 메모리 SQL 엔진에 넣어 ON/WHERE의 두 결과와 집계 결과를 실행 검증합니다. 정규화 예제는 공개 SQL의 복원 결과와 독립 반례를 실행하고, 속성 폐포·모든 후보키·종속성 보존을 별도 계산으로 대조합니다. 유한한 입력의 실행 결과와 함수 종속에 근거한 일반적 판단을 구분합니다.

예제·데이터·풀이표: 정처LAB 자체 설계 · AI 보조 작성 및 로컬 검증 · 예제 수정일 2026-10-16

• SQLite 공식 SELECT 설명: 외부 조인·WHERE·집계
• Database System Concepts 저자 자료: 후보키·정규형·무손실 분해·종속성 보존