3. 5 다른 테이블 행과 일치하지 않는 행 검색하기
Q. 공통 키가 있는 두 테이블('EMP', 'DEPT')에서 다른 테이블과 일치하지 않는, 어느 한 테이블에 있는 행을 찾으려고 한다.
예를 들어, 사원이 없는 부서를 찾고자 한다.
결과셋은 다음과 같아야 한다.
DEPTNO DNAME LOC
------ ---------- -------
40 OPERATIONS BOSTON'EMP'와 'DEPT'를 동등조건으로 JOIN한다면 사원이 없는 부서를 반환하지 않을 것이다.
JOIN 조건을 충족하지 않는 'DEPT'의 행만을 원한다.
앞선 레시피에서는 'EMP' 테이블에 존재하지 않는 부서번호만 가지고 왔다.
이번 레시피를 사용하면 'DEPT' 테이블에서 다른 열을 쉽게 반환할 수 있으며, 심지어 원하는 열만을 반환할 수도 있게 된다.
A. 한 테이블의 모든 행을 포함하여, 공통 열(KEY)와 일치하지 않거나, 일치하지 않을 수 있는 다른 행을 함께 반환한다. 그 후 일치하는 것이 없는 행만 남겨둔다.
CASE: MySQL
NULL에 대한 외부조인(OUNTER JOIN) 및 필터를 사용한다.- 단,
OUTER라는 키워드는 선택사항이다. (?)
- 단,
select d.*
from dept d left outer join emp e
on (d.deptno = e.deptno)
where e.deptno is null
>>
+--------+------------+--------+
| deptno | dname | loc |
+--------+------------+--------+
| 40 | OPERATIONS | BOSTON |
+--------+------------+--------+- 이 해법은
OUTER JOIN후 일치하지 않는 행만 유지하는 방식으로 작동한다.
where e.deptno is null이 처리 되지 않은 상태의 결과셋부터 봐야겠다.
select d.*
from dept d left outer join emp e
on (d.deptno = e.deptno)
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 10 | ACCOUNTING | NEW YORK |
| 10 | ACCOUNTING | NEW YORK |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 20 | RESEARCH | DALLAS |
| 20 | RESEARCH | DALLAS |
| 20 | RESEARCH | DALLAS |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 30 | SALES | CHICAGO |
| 30 | SALES | CHICAGO |
| 30 | SALES | CHICAGO |
| 30 | SALES | CHICAGO |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+- 'EMP' 테이블에는 'DEPTNO'가 40인 필드가 존재하지 않는다
따라서JOIN을 실행했을 때DEPTNO의 값이 40인 'DEPT' 테이블에JOIN이 되는 'EMP' 테이블의 칼럼 값들은NULL로 처리 된다
아래와 같이 나온단 뜻이다
# SELECT절만 바뀌었다(d.*에서 *으로)
select *
from dept d left outer join emp e
on (d.deptno = e.deptno)
>>
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
| deptno | dname | loc | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
| 10 | ACCOUNTING | NEW YORK | 7777 | IREN | TESTER | 0 | 2022-07-03 | 9000 | 9000 | 10 |
| 10 | ACCOUNTING | NEW YORK | 7782 | CLARK | MANAGER | 7698 | 2006-06-09 | 2450 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 7839 | KING | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 7934 | MILLER | CLERK | 7782 | 2007-01-23 | 1300 | NULL | 10 |
| 20 | RESEARCH | DALLAS | 7369 | SMITH | CLERK | 7902 | 2005-12-17 | 800 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7566 | JONES | MANAGER | 7839 | 2006-04-02 | 2975 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7788 | SCOTT | ANALYST | 7566 | 2007-12-09 | 3000 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7876 | ADAMS | CLERK | 7788 | 2008-01-12 | 1100 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7902 | FORD | ANALYST | 7566 | 2006-12-03 | 3000 | NULL | 20 |
| 30 | SALES | CHICAGO | 7499 | ALLEN | SALESMAN | 7698 | 2006-02-20 | 1600 | 300 | 30 |
| 30 | SALES | CHICAGO | 7521 | WARD | SALESMAN | 7698 | 2006-02-22 | 1250 | 500 | 30 |
| 30 | SALES | CHICAGO | 7654 | MARTIN | SALESMAN | 7698 | 2006-09-28 | 1250 | 1400 | 30 |
| 30 | SALES | CHICAGO | 7698 | BLAKE | MANAGER | 7839 | 2006-05-01 | 2850 | NULL | 30 |
| 30 | SALES | CHICAGO | 7844 | TURNER | SALESMAN | 7698 | 2006-09-08 | 1500 | 0 | 30 |
| 30 | SALES | CHICAGO | 7900 | JAMES | CLERK | 7698 | 2006-12-03 | 950 | 0 | 30 |
| 40 | OPERATIONS | BOSTON | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+맨 아랫줄
- 'DEPT' 테이블에 'EMP' 테이블을 붙이긴 했는데, 'EMP' 테이블에는 'DEPTNO'의 값이 40인 자료가 존재하지 않는다
그러므로NULL처리 되었다
따라서 이 상태에서 NULL처리된 자료만 남기면, 'DEPT' 테이블에는 존재하지만 'EMP' 테이블에는 존재하지 않는 행이 반환되게 된다.
정리
ON구문에서 KEY를 설정하고 이에 대한JOIN을 실행했을 때NULL값이 나오도록 유도한다- 이후
WHERE절에서NULL값만 남기면 두 테이블 사이에 다른 행을 찾을 수 있게 된다
매우 획기적이다...
JOIN을 이렇게도 쓸 수 있구나 싶다
이 다음 부분을 진행하기 전에, JOIN의 종류와 개념을 먼저 정리한 후 진행해야겠다.
Can do this all day