OUTER JOIN
? LEFT/RIGHT JOIN
? 이미지 출처: dsin.wordpress.com
JOIN
의 여러 종류가 본격적으로 등장하기 시작했다.
미리 여러 가지 JOIN
의 종류를 알아두는 것이 좋을 것이다.
JOIN
JOIN
은 INNER JOIN
과 동일하다고 설명되어 있었다JOIN
은 사진의 가운데처럼 두 테이블의 값이 일치하는 부분을 반환한다실험을 여러 가지로 해보자
select * from dept join emp;
>>
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
| deptno | dname | loc | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
| 10 | ACCOUNTING | NEW YORK | 7369 | SMITH | CLERK | 7902 | 2005-12-17 | 800 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7369 | SMITH | CLERK | 7902 | 2005-12-17 | 800 | NULL | 20 |
| 30 | SALES | CHICAGO | 7369 | SMITH | CLERK | 7902 | 2005-12-17 | 800 | NULL | 20 |
| 40 | OPERATIONS | BOSTON | 7369 | SMITH | CLERK | 7902 | 2005-12-17 | 800 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7499 | ALLEN | SALESMAN | 7698 | 2006-02-20 | 1600 | 300 | 30 |
| 20 | RESEARCH | DALLAS | 7499 | ALLEN | SALESMAN | 7698 | 2006-02-20 | 1600 | 300 | 30 |
| 30 | SALES | CHICAGO | 7499 | ALLEN | SALESMAN | 7698 | 2006-02-20 | 1600 | 300 | 30 |
| 40 | OPERATIONS | BOSTON | 7499 | ALLEN | SALESMAN | 7698 | 2006-02-20 | 1600 | 300 | 30 |
| 10 | ACCOUNTING | NEW YORK | 7521 | WARD | SALESMAN | 7698 | 2006-02-22 | 1250 | 500 | 30 |
| 20 | RESEARCH | DALLAS | 7521 | WARD | SALESMAN | 7698 | 2006-02-22 | 1250 | 500 | 30 |
| 30 | SALES | CHICAGO | 7521 | WARD | SALESMAN | 7698 | 2006-02-22 | 1250 | 500 | 30 |
| 40 | OPERATIONS | BOSTON | 7521 | WARD | SALESMAN | 7698 | 2006-02-22 | 1250 | 500 | 30 |
| 10 | ACCOUNTING | NEW YORK | 7566 | JONES | MANAGER | 7839 | 2006-04-02 | 2975 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7566 | JONES | MANAGER | 7839 | 2006-04-02 | 2975 | NULL | 20 |
| 30 | SALES | CHICAGO | 7566 | JONES | MANAGER | 7839 | 2006-04-02 | 2975 | NULL | 20 |
| 40 | OPERATIONS | BOSTON | 7566 | JONES | MANAGER | 7839 | 2006-04-02 | 2975 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7654 | MARTIN | SALESMAN | 7698 | 2006-09-28 | 1250 | 1400 | 30 |
| 20 | RESEARCH | DALLAS | 7654 | MARTIN | SALESMAN | 7698 | 2006-09-28 | 1250 | 1400 | 30 |
| 30 | SALES | CHICAGO | 7654 | MARTIN | SALESMAN | 7698 | 2006-09-28 | 1250 | 1400 | 30 |
| 40 | OPERATIONS | BOSTON | 7654 | MARTIN | SALESMAN | 7698 | 2006-09-28 | 1250 | 1400 | 30 |
| 10 | ACCOUNTING | NEW YORK | 7698 | BLAKE | MANAGER | 7839 | 2006-05-01 | 2850 | NULL | 30 |
| 20 | RESEARCH | DALLAS | 7698 | BLAKE | MANAGER | 7839 | 2006-05-01 | 2850 | NULL | 30 |
| 30 | SALES | CHICAGO | 7698 | BLAKE | MANAGER | 7839 | 2006-05-01 | 2850 | NULL | 30 |
| 40 | OPERATIONS | BOSTON | 7698 | BLAKE | MANAGER | 7839 | 2006-05-01 | 2850 | NULL | 30 |
| 10 | ACCOUNTING | NEW YORK | 7777 | IREN | TESTER | 0 | 2022-07-03 | 9000 | 9000 | 10 |
| 20 | RESEARCH | DALLAS | 7777 | IREN | TESTER | 0 | 2022-07-03 | 9000 | 9000 | 10 |
| 30 | SALES | CHICAGO | 7777 | IREN | TESTER | 0 | 2022-07-03 | 9000 | 9000 | 10 |
| 40 | OPERATIONS | BOSTON | 7777 | IREN | TESTER | 0 | 2022-07-03 | 9000 | 9000 | 10 |
| 10 | ACCOUNTING | NEW YORK | 7782 | CLARK | MANAGER | 7698 | 2006-06-09 | 2450 | NULL | 10 |
| 20 | RESEARCH | DALLAS | 7782 | CLARK | MANAGER | 7698 | 2006-06-09 | 2450 | NULL | 10 |
| 30 | SALES | CHICAGO | 7782 | CLARK | MANAGER | 7698 | 2006-06-09 | 2450 | NULL | 10 |
| 40 | OPERATIONS | BOSTON | 7782 | CLARK | MANAGER | 7698 | 2006-06-09 | 2450 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 7788 | SCOTT | ANALYST | 7566 | 2007-12-09 | 3000 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7788 | SCOTT | ANALYST | 7566 | 2007-12-09 | 3000 | NULL | 20 |
| 30 | SALES | CHICAGO | 7788 | SCOTT | ANALYST | 7566 | 2007-12-09 | 3000 | NULL | 20 |
| 40 | OPERATIONS | BOSTON | 7788 | SCOTT | ANALYST | 7566 | 2007-12-09 | 3000 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7839 | KING | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL | 10 |
| 20 | RESEARCH | DALLAS | 7839 | KING | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL | 10 |
| 30 | SALES | CHICAGO | 7839 | KING | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL | 10 |
| 40 | OPERATIONS | BOSTON | 7839 | KING | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 7844 | TURNER | SALESMAN | 7698 | 2006-09-08 | 1500 | 0 | 30 |
| 20 | RESEARCH | DALLAS | 7844 | TURNER | SALESMAN | 7698 | 2006-09-08 | 1500 | 0 | 30 |
| 30 | SALES | CHICAGO | 7844 | TURNER | SALESMAN | 7698 | 2006-09-08 | 1500 | 0 | 30 |
| 40 | OPERATIONS | BOSTON | 7844 | TURNER | SALESMAN | 7698 | 2006-09-08 | 1500 | 0 | 30 |
| 10 | ACCOUNTING | NEW YORK | 7876 | ADAMS | CLERK | 7788 | 2008-01-12 | 1100 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7876 | ADAMS | CLERK | 7788 | 2008-01-12 | 1100 | NULL | 20 |
| 30 | SALES | CHICAGO | 7876 | ADAMS | CLERK | 7788 | 2008-01-12 | 1100 | NULL | 20 |
| 40 | OPERATIONS | BOSTON | 7876 | ADAMS | CLERK | 7788 | 2008-01-12 | 1100 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7900 | JAMES | CLERK | 7698 | 2006-12-03 | 950 | 0 | 30 |
| 20 | RESEARCH | DALLAS | 7900 | JAMES | CLERK | 7698 | 2006-12-03 | 950 | 0 | 30 |
| 30 | SALES | CHICAGO | 7900 | JAMES | CLERK | 7698 | 2006-12-03 | 950 | 0 | 30 |
| 40 | OPERATIONS | BOSTON | 7900 | JAMES | CLERK | 7698 | 2006-12-03 | 950 | 0 | 30 |
| 10 | ACCOUNTING | NEW YORK | 7902 | FORD | ANALYST | 7566 | 2006-12-03 | 3000 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7902 | FORD | ANALYST | 7566 | 2006-12-03 | 3000 | NULL | 20 |
| 30 | SALES | CHICAGO | 7902 | FORD | ANALYST | 7566 | 2006-12-03 | 3000 | NULL | 20 |
| 40 | OPERATIONS | BOSTON | 7902 | FORD | ANALYST | 7566 | 2006-12-03 | 3000 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7934 | MILLER | CLERK | 7782 | 2007-01-23 | 1300 | NULL | 10 |
| 20 | RESEARCH | DALLAS | 7934 | MILLER | CLERK | 7782 | 2007-01-23 | 1300 | NULL | 10 |
| 30 | SALES | CHICAGO | 7934 | MILLER | CLERK | 7782 | 2007-01-23 | 1300 | NULL | 10 |
| 40 | OPERATIONS | BOSTON | 7934 | MILLER | CLERK | 7782 | 2007-01-23 | 1300 | NULL | 10 |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
60 rows in set (0.002 sec)
왜 이렇게 나왔을까?
말그대로 그냥 합치라고 했기 때문이다.
그 어떠한 조건도 주지 않았기 때문에 '가능한 한 모든 조합'에 대한 조회 결과가 나왔다.
그렇다면 특정 칼럼에 대한 조건을 걸어주면 어떻게 나올까?
select * from dept a
join emp b
on a.deptno = b.deptno;
>>
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
| deptno | dname | loc | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
| 20 | RESEARCH | DALLAS | 7369 | SMITH | CLERK | 7902 | 2005-12-17 | 800 | 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 |
| 20 | RESEARCH | DALLAS | 7566 | JONES | MANAGER | 7839 | 2006-04-02 | 2975 | NULL | 20 |
| 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 |
| 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 |
| 20 | RESEARCH | DALLAS | 7788 | SCOTT | ANALYST | 7566 | 2007-12-09 | 3000 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7839 | KING | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL | 10 |
| 30 | SALES | CHICAGO | 7844 | TURNER | SALESMAN | 7698 | 2006-09-08 | 1500 | 0 | 30 |
| 20 | RESEARCH | DALLAS | 7876 | ADAMS | CLERK | 7788 | 2008-01-12 | 1100 | NULL | 20 |
| 30 | SALES | CHICAGO | 7900 | JAMES | CLERK | 7698 | 2006-12-03 | 950 | 0 | 30 |
| 20 | RESEARCH | DALLAS | 7902 | FORD | ANALYST | 7566 | 2006-12-03 | 3000 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7934 | MILLER | CLERK | 7782 | 2007-01-23 | 1300 | NULL | 10 |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
15 rows in set (0.001 sec)
'DEPT' 테이블의 'DEPTNO' 칼럼이 가진 값과 'EMP' 테이블의 'DEPTNO' 칼럼이 가진 값이 일치하는 행을 찾아서 붙여준 걸 볼 수 있다.
예를 들어, 'EMP' 테이블의 'DETPNO'의 값이 20인 행이 있다면, 여기에는 'DEPT' 테이블의 'DETPNO'의 값이 20인 행이 연결된다
나머지 값들도 그렇게 연결된다
'DEPT' 테이블은 4행짜리고 'EMP' 테이블은 15행짜리인데 어떻게 연결이 되느냐?
이어준다고 했다. 근데 1대1로 이어주겠다는 말이 아니다.
조건은 그저 해당 칼럼이 가진 값이 같을 때 같은 행을 잇겠다고 했다.
1대1로 대응시키는 게 아니라, 값이 같을 때 그 내용을 붙여주는 것이다.
이제 위 쿼리에 칼럼값까지 정확하게 조건을 부여해보겠다.
select * from dept a
join emp b
on a.deptno = b.deptno
where a.deptno = 20
>>
+--------+----------+--------+-------+-------+---------+------+------------+------+------+--------+
| deptno | dname | loc | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+----------+--------+-------+-------+---------+------+------------+------+------+--------+
| 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 |
+--------+----------+--------+-------+-------+---------+------+------------+------+------+--------+
5 rows in set (0.002 sec)
값이 20인 행만 모두 반환된 것을 볼 수 있다.
이렇듯 JOIN
(INNER JOIN
)은 공통된 값을 가진 데이터를 반환한다. 사진에서 보이는 교집합이라 생각할 수 있겠다.
OUTER JOIN
: LEFT/RIGHT JOIN
'OUTER JOIN'은 LEFT JOIN
또는 RIGHT JOIN
을 뜻한다
'OUTER'라는 키워드는 존재하지 않는다
LEFT(RIGHT) JOIN
은 LEFT(RIGHT) OUTER JOIN
으로 쓸 수 있을 뿐이다
INNER JOIN
이 JOIN
과 동일하고 'INNER'라는 키워드는 없는 것과 동일한 이치다
그렇다면 이 형태의 JOIN은 어떻게 다를까?
일반적인 INNER JOIN은 FROM
절에서 A테이블에 B테이블을 JOIN하는 것과 B테이블에 A테이블을 JOIN하는 것에 차이가 없다
공통된 부분만 조회하기 때문이다
하지만 LEFT JOIN
은 기준이 되는 테이블의 모든 행을 반환한다
RIGHT JOIN
은 JOIN하여 가져올 테이블의 모든 행을 반환한다
select * from dept a
left join emp b
on a.deptno = b.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 |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
16 rows in set (0.001 sec)
'DEPT' 테이블의 행은 겨우 네 줄이지만, 결과는 16줄이 나온 것을 볼 수 있다.
위의 LEFT/RIGHT
를 붙이지 않은 INNER JOIN에서는 15줄이 나왔다.
'EMP' 테이블에 'DETPNO' 칼럼의 값이 40인 자료는 없기 때문에, 공통 부분을 찾을 수 없었기 때문이다.
하지만 OUTTER JOIN에서는 기준 테이블 또는 JOIN을 위해 가져올 테이블 둘 중 하나를 반드시 모두 반환한다.
칼럼의 값이 비어 있다면 NULL
값으로 대체된다.
FULL JOIN은 아직 경험해본 적이 없다.
진행하다가 만날 때 다시 정리하기.