3. 6장 다른 JOIN을 방해하지 않고 쿼리에 JOIN 추가하기
Q. 원하는 결과셋을 반환하는 쿼리가 있다. 추가 정보가 필요한 상황인데, 추가 정보를 가져오려고 할 때 원래의 결과셋이 손실된다.
예를 들어, 모든 사원명과 근무부서위치 및 보너스날짜를 반환하려고 한다.
일단 보너스에 관련 테이블을 하나 만들어야 한다.
CREATE TABLE emp_bonus (
empno INT NOT NULL,
received DATE NULL,
`type` INT NULL,
CONSTRAINT pk_emp_bonus PRIMARY KEY (empno),
CONSTRAINT emp_bonus_FK FOREIGN KEY (empno) REFERENCES emp(empno)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;DBeaver를 이용하여 GUI로 테이블, 칼럼, KEY를 생성했다.
스크립트를 보여주는 것이 정말 편하다...
보지 않고도 짤 수 있을 정도가 되도록 노력해야겠다.
테이블을 생성했으니 이제 데이터를 넣어줘야겠다.
insert into emp_bonus (empno, received, type)
values(7369, '2005-03-14', 1);
insert into emp_bonus (empno, received, type)
values(7900, '2005-03-14', 2);
insert into emp_bonus (empno, received, type)
values(7788, '2005-03-14', 3);
select * from emp_bonus;
>>
+-------+------------+------+
| empno | received | type |
+-------+------------+------+
| 7369 | 2005-03-14 | 1 |
| 7788 | 2005-03-14 | 3 |
| 7900 | 2005-03-14 | 2 |
+-------+------------+------+잘 생성되었다.
문제를 다시 본다.
Q. 모든 '사원명'과 '근무 부서 위치' 및 '보너스 날짜'를 반환하려고 한다.
select e.ename, d.loc, b.received
from
(
select e.ename, d.loc
from emp e join dept d
on e.deptno = d.deptno
) x
join emp_bonus b
on x.empno = b.empno이라고 해봤는데 어림 없지 바로 syntax error
얌전하게 풀이를 따라가 보겠다.
풀이의 시작은 아래와 같다.
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno
>>
+--------+----------+
| ename | loc |
+--------+----------+
| SMITH | DALLAS |
| ALLEN | CHICAGO |
| WARD | CHICAGO |
| JONES | DALLAS |
| MARTIN | CHICAGO |
| BLAKE | CHICAGO |
| IREN | NEW YORK |
| CLARK | NEW YORK |
| SCOTT | DALLAS |
| KING | NEW YORK |
| TURNER | CHICAGO |
| ADAMS | DALLAS |
| JAMES | CHICAGO |
| FORD | DALLAS |
| MILLER | NEW YORK |
+--------+----------+일단 '사원명'과 '근무 부서 위치'를 불러온 상태다.
여기에 '보너스 날짜'를 추가하려고 할 때, 'EMP_BONUS' 테이블을 JOIN하면, 모든 사원이 보너스를 받는 것은 아니므로 원하는 수보다 적은 행을 반환한다.
select e.ename, d.loc, eb.received
from emp e, dept d, emp_bonus eb
where e.deptno = d.deptno
and e.empno = eb.empno
>>
+-------+---------+------------+
| ename | loc | received |
+-------+---------+------------+
| SMITH | DALLAS | 2005-03-14 |
| SCOTT | DALLAS | 2005-03-14 |
| JAMES | CHICAGO | 2005-03-14 |
+-------+---------+------------+모든 사원명을 조회하고 싶은데, 보너스를 받는 친구들만 조회되어 버렸다.
A. OUTER JOIN을 사용한다.
먼저 'EMP' 테이블을 'DEPT' 테이블에 조인하여, 모든 '사원명'과 '근무 부서 위치'를 반환한 다음, 'EMP_BONUS' 테이블을 OUTER JOIN하여 보너스 날짜가 있으면 이를 반환한다.
CASE: MySQL
select e.ename, d.loc, eb.received
from emp e join dept d
on e.deptno = d.deptno
left join emp_bonus eb
on e.empno = eb.empno
order by 2
>>
+--------+----------+------------+
| ename | loc | received |
+--------+----------+------------+
| MARTIN | CHICAGO | NULL |
| BLAKE | CHICAGO | NULL |
| TURNER | CHICAGO | NULL |
| JAMES | CHICAGO | 2005-03-14 |
| ALLEN | CHICAGO | NULL |
| WARD | CHICAGO | NULL |
| JONES | DALLAS | NULL |
| SCOTT | DALLAS | 2005-03-14 |
| ADAMS | DALLAS | NULL |
| FORD | DALLAS | NULL |
| SMITH | DALLAS | 2005-03-14 |
| IREN | NEW YORK | NULL |
| CLARK | NEW YORK | NULL |
| KING | NEW YORK | NULL |
| MILLER | NEW YORK | NULL |
+--------+----------+------------+
15 rows in set (0.003 sec)마지막의 order by에 큰 의미는 없다. 책의 저자가 그렇게 정렬하고 싶었나보다.
- 일단 'EMP' 테이블과 'DEPT' 테이블을 JOIN하여 '사원명'과 '근무 부서 위치'를 조회한다
이 인라인 뷰를 기준으로LEFT JOIN를 실행하면, 기존 결과는 모두 남기고 새로 JOIN되는 테이블과 일치하는 부분이 있으면 이를 반환한다
스칼라 서브쿼리: SELECT 목록에 있는 서브쿼리
이를 사용하여 OUTER JOIN을 흉내낼 수도 있다고 한다.
select e.ename, d.loc,
(select eb.received from emp_bonus eb
where eb.empno=e.empno) as received
from emp e, dept d
where e.deptno = d.detpno
order by 2스칼라 서브쿼리는 모든 플랫폼에서 작동한다.
SELECT목록에 아예 쿼리의 반환값을 넣어버렸다.
- 매우 간편해보이지만 이 때, 스칼라 서브쿼리가 말 그대로 '단일 값'을 반환하는지 확인해야 한다
둘 이상의 행을 반환하면 오류가 발생한다
예제의 경우에 '2005-03-14'의 하나의 행만을 반환한다
정리
- OUTER JOIN은 한쪽 테이블의 모든 행과 다른 테이블의 일치하는 행을 반환한다
OUTER JOIN을 전혀 생각하지 못했다.
서브쿼리, 인라인 뷰에 너무 치중하다 보니 생각이 날 것도 나지 않을 때가 있는 것 같다.
게다가 아직 이해도도 낮은 느낌이다...
어떤 상황에 돌아가고 어떤 상황에 돌아가지 않는지 아직 파악이 되지 않았다.
이쯤에서 내용 복습을 해야겠다.
다음 포스팅은 3장 복습.
