조인 질의 쓰기 예제 사다리

사용 개념: FROM 절의 조인 (JOIN ON), 외부 조인. 표와 행은 COMPANY다(관계형 데이터베이스 스키마와 상태).

“여러 표에서 값을 모아 와라”는 문제는 같은 하위목표의 되풀이다¹.

① 필요한 열 찾기. 답에 내놓을 열과 조건에 쓰는 열이 어느 표에 있는지 적는다. 그 표들이 FROM에 들어간다.
② 잇는 열 짝 찾기. 표와 표를 잇는 것은 외래 키와 그것이 가리키는 기본 키다. 표가 \(k\)개면 짝이 적어도 \(k - 1\)개 있어야 한다.
③ 짝 없는 행을 남길지 정하기. 버려도 되면 JOIN, 남겨야 하면 남길 쪽을 왼쪽에 두고 LEFT OUTER JOIN.
④ 남은 조건을 쓰고 결과 내기.

문제 1 · 완전한 풀이

‘Research’ 부서에서 일하는 모든 직원의 이름과 주소를 구하라.

필요한 열 찾기: Fname, Lname, Address는 EMPLOYEE에 있다. 조건에 쓰는 ‘Research’는 DEPARTMENT의 Dname이다. 표는 둘이다.
잇는 열 짝 찾기: EMPLOYEE의 Dno가 DEPARTMENT의 Dnumber를 가리킨다. Dno = Dnumber.
짝 없는 행을 남길지 정하기: Research 직원만 원한다. 짝 없는 행은 필요 없다. JOIN.
남은 조건을 쓰고 결과 내기: Dname = 'Research'.

SELECT Fname, Lname, Address
FROM   (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber)
WHERE  Dname = 'Research';

Fname	Lname	Address
John	Smith	731 Fondren, Houston, TX
Franklin	Wong	638 Voss, Houston, TX
Ramesh	Narayan	975 Fire Oak, Humble, TX
Joyce	English	5631 Rice, Houston, TX

조인 조건을 WHERE에 섞어 쓴 FROM EMPLOYEE, DEPARTMENT WHERE Dname = 'Research' AND Dnumber = Dno와 답이 같다. JOIN … ON으로 쓰면 “잇는 조건”과 “고르는 조건”이 다른 자리에 놓여 읽기 쉽다.

검증 — 66_join-query-ladder_p4.py

문제 2 · 마지막 하위목표만 빈칸

프로젝트마다 프로젝트 이름과, 그 프로젝트를 맡은 부서의 부서장 성을 구하라.

필요한 열 찾기: Pname은 PROJECT, 부서장의 성 Lname은 EMPLOYEE에 있다. PROJECT에서 부서장으로 곧장 가는 열은 없다. 부서를 거쳐야 하니 DEPARTMENT도 필요하다. 표는 셋이다.
잇는 열 짝 찾기: PROJECT의 Dnum = DEPARTMENT의 Dnumber. DEPARTMENT의 Mgr_ssn = EMPLOYEE의 Ssn. 표가 셋이니 짝은 둘이다.
짝 없는 행을 남길지 정하기: 모든 프로젝트에 맡은 부서가 있고(Dnum은 NOT NULL), 모든 부서에 부서장이 있다(Mgr_ssn은 NOT NULL). 짝 없는 행이 생기지 않는다. JOIN.
남은 조건을 쓰고 결과 내기: __

답

남은 조건은 없다.

SELECT Pname, Lname
FROM   (PROJECT JOIN DEPARTMENT ON Dnum = Dnumber)
       JOIN EMPLOYEE ON Mgr_ssn = Ssn;

Pname	Lname
ProductX	Wong
ProductY	Wong
ProductZ	Wong
Computerization	Wallace
Reorganization	Borg
Newbenefits	Wallace

문제 3 · 하위목표 절반이 빈칸

모든 직원의 성과 그 직원의 부양가족 이름을 구하라. 부양가족이 없는 직원도 한 줄 나와야 한다.

필요한 열 찾기: Lname은 EMPLOYEE, Dependent_name은 DEPENDENT.
잇는 열 짝 찾기: DEPENDENT의 Essn = EMPLOYEE의 Ssn.
짝 없는 행을 남길지 정하기: __
남은 조건을 쓰고 결과 내기: __ (몇 줄이 나오는가?)

답

부양가족이 없는 직원은 DEPENDENT에 짝이 없다. 그래도 남겨야 한다. 남길 쪽인 EMPLOYEE를 왼쪽에 두고 LEFT OUTER JOIN을 쓴다.
4.
```
SELECT Lname, Dependent_name
FROM   EMPLOYEE LEFT OUTER JOIN DEPENDENT ON Ssn = Essn;
```
12줄이다. 짝이 있는 줄이 7개다(Wong 3, Smith 3, Wallace 1). 부양가족이 없는 Zelaya, Narayan, English, Jabbar, Borg는 Dependent_name이 NULL인 줄로 하나씩 나와 5개다. JOIN으로 쓰면 7줄뿐이다.

검증 — 66_join-query-ladder_p4.py

문제 4 · 독립 문제

부양가족이 한 명도 없는 직원의 성을, 중첩 질의를 쓰지 않고 조인으로 구하라.

답

SELECT Lname
FROM   EMPLOYEE LEFT OUTER JOIN DEPENDENT ON Ssn = Essn
WHERE  Essn IS NULL;

Zelaya, Narayan, English, Jabbar, Borg. 다섯 명이다.

문제 3의 12줄에서 DEPENDENT 쪽이 NULL로 채워진 줄만 남긴 것이다. Essn은 DEPENDENT의 기본 키에 드는 열이라 진짜 행에서는 NULL일 수 없다. 그러니 Essn IS NULL인 줄은 짝이 없어서 채워진 줄뿐이다. NOT EXISTS (SELECT * FROM DEPENDENT WHERE Ssn = Essn)으로 쓴 질의와 같은 답이다.

변형. 모든 부서에 대해 부서 이름과, 그 부서가 맡은 프로젝트 가운데 Houston에 있는 것의 이름을 구하라. Houston 프로젝트가 없는 부서도 한 줄 나와야 한다. Plocation = 'Houston'을 ON에 쓸 때와 WHERE에 쓸 때 결과가 어떻게 다른가?

답

SELECT Dname, Pname
FROM   DEPARTMENT LEFT OUTER JOIN PROJECT
       ON Dnum = Dnumber AND Plocation = 'Houston';

3줄이다: (Research, ProductZ), (Headquarters, Reorganization), (Administration, NULL).

조건을 WHERE Plocation = 'Houston'으로 옮기면 2줄이다. Administration이 사라진다. WHERE는 조인이 끝난 뒤에 줄을 거른다. Administration의 프로젝트는 둘 다 Stafford에 있다. 둘 다 WHERE에서 떨어지고, NULL로 채운 줄은 애초에 만들어지지 않는다. ON에 쓰면 “Houston 프로젝트와만 짝을 지어라”가 되어, 짝이 없는 Administration이 NULL로 채워져 남는다.

검증 — 66_join-query-ladder_p4.py

모든 답은 SQLite 3.50.4에서 실행해 확인했다. ↩