오라클 조인, SQL 조인, 외부 조인(Outer Join)
일반적인 조인(내부조인)은 조인 조건을 만족시키는 레코드만 출력됩니다. 즉 두 테이블중 한쪽 테이블의 값이 일치하지 않는 다면 해당 레코드는 출력되지 않는데, 이 경우에도 결과로 출력하고 싶은 경우에는 외부조인(Outer Join)을 사용합니다. 외부조인은 LEFT OUTER JOIN, RIGTH OUTER JOIN, FULL OUTER JOIN 3가지 형태가 있습니다.
사원테이블(EMP), 부서테이블(DEPT)을 조인하여 사원명(ename)과 부서명(dname)을 출력한다고 했을 때 EMP 테이블의 “KING” 사원은 최고관리자로 부서코드가 NULL 값 이므로 emp.deptno = dept.deptno 로 조인을 한다면 “KING” 사원은 은 출력되지 않습니다.
내부 조인은 조인 조건을 만족하는 행들만 추출되는데, 앞의 Self Join 예문처럼 조인 조건을 만족하지 않는다면 해당 레코드는 출력되지 않습니다(“KING” 사원은 mgr 컬럼이 NULL 이므로 출력되지 않았습니다). Outer Join은 조인에서 한쪽 테이블의 행에 대하여 다른 쪽 테이블에 일치하는 행이 없더라도 다른 쪽 행을 NULL로 하여 행을 반환하게 하는 겁니다.
오라클은 (+) 기호로 외부조인을 지원하는데 어디에 “(+)”를 하여야 할까요? 사원 쪽이 다 출력 되어야 하는 경우인데, 관리자가 있든지 없든지 사원 리스트의 모든 사원은 나와야 하는 겁니다. 다 나와야 하는 반대쪽인 관리자 쪽에 (+) 표시를 하면 되는데 “KING” 사원이 출력됨을 확인 하세요. |
SELECT "사원".empno AS "사원ID" , "사원".ename AS "사원명" , nvl("관리자".ename, '관리자없음') AS "관리자명" FROM emp "사원", emp "관리자" WHERE "사원".mgr = "관리자".empno(+) |
<실행결과>
사원ID | 사원명 | 관리자명 | |
1 | 7788 | SCOTT | JONES |
2 | 7902 | FORD | JONES |
3 | 7499 | ALLEN | BLAKE |
4 | 7521 | WARD | BLAKE |
5 | 7654 | MARTIN | BLAKE |
6 | 7844 | TURNER | BLAKE |
7 | 7900 | JAMES | BLAKE |
8 | 7934 | MILLER | CLARK |
9 | 7876 | ADAMS | SCOTT |
10 | 7566 | JONES | KING |
11 | 7698 | BLAKE | KING |
12 | 7782 | CLARK | KING |
13 | 7369 | SMITH | FORD |
14 | 7839 | KING | 관리자없음 |
만약 이러한 기호의 위치 문제가 고민 된다면 표준 ANSI JOIN 형태의 SQL 구문을 작성하면 됩니다. 아래 두 쿼리는 위와 같은 결과를 만듭니다. |
SELECT "사원".empno "사원ID" , "사원".ename "사원명" , nvl("관리자".ename, '관리자없음') "관리자명" FROM emp "사원" LEFT OUTER JOIN emp "관리자" ❶ ON "사원".mgr = "관리자".empno |
❶ LEFT OUTER JOIN은 왼쪽 테이블인 “사원”의 조인 레코드는 mgr이 NULL이라도 다 출력하라는 의미 입니다. 그냥 간단히 왼쪽 레코드는 다출력된다고 생각을 하시면 되겠습니다.
SELECT "사원".empno "사원ID" , "사원".ename "사원명" , nvl("관리자".ename, '관리자없음') "관리자명" FROM emp "관리자" RIGHT OUTER JOIN emp "사원" ❶ ON "사원".mgr = "관리자".empno |
❶ RIGHT OUTER JOIN은 오른쪽 테이블인 “사원”의 조인 레코드는 mgr이 NULL이라도 다 출력하라는 의미 입니다. 그냥 간단히 오른쪽 레코드는 다출력된다고 생각을 하시면 되겠습니다.
다른 형태의 예문을 살펴 보겠습니다. 먼저 EMP 테이블 및 DEPT 테이블의 데이터를 확인해 보겠습니다. EMP 테이블에서 “KING”사원은 최고관리자로 부서코드 컬럼이 NULL 이며 40번 부서에 속한 사원은 없습니다. |
SELECT empno, ename, deptno FROM emp |
<실행결과>
empno | ename | deptno | |
1 | 7369 | SMITH | 20 |
2 | 7499 | ALLEN | 30 |
3 | 7521 | WARD | 30 |
4 | 7566 | JONES | 20 |
5 | 7654 | MARTIN | 30 |
6 | 7698 | BLAKE | 30 |
7 | 7782 | CLARK | 10 |
8 | 7788 | SCOTT | 20 |
9 | 7839 | KING | |
10 | 7844 | TURNER | 30 |
11 | 7876 | ADAMS | 20 |
12 | 7900 | JAMES | 30 |
13 | 7902 | FORD | 20 |
14 | 7934 | MILLER | 10 |
부서 테이블(DEPT)의 데이터 입니다. |
SELECT * FROM dept |
<실행결과>
deptno | dname | loc | |
1 | 10 | ACCOUNTING | NEW YORK |
2 | 20 | RESEARCH | DALLAS |
3 | 30 | SALES | CHICAGO |
4 | 40 | OPERATIONS | BOSTON |
부서별 사원수를 출력 하는 쿼리문 입니다. 사원이 한명도 없는 40번 “OPERATION” 부서는 출력되지 않습니다. |
SELECT dname , COUNT(E.empno) AS emp_cnt ❶ FROM emp E, dept D WHERE E.deptno = D.deptno GROUP BY dname ❷ |
<실행결과>
dname | empCnt | |
1 | RESEARCH | 5 |
2 | SALES | 6 |
3 | ACCOUNTING | 2 |
❶ COUNT 함수는 그룹 함수로 SELECT 절에서 dname 처럼 다른 컬럼하고 같이 쓰이면 부서별로 사번을 카운트한다는 의미로 반드시 ❷ GROUP BY dname 형태 구문이 와야 합니다. 만약 SELECT 절에 dname 컬럼이 출현하지 않았다면 전체 사원테이블의 empno를 카운트하는 의미로 ❷ GROUP BY dname은 기술할 필요 없습니다.
40번 “OPERATION” 부서를 외부조인을 이용하여 출력되도록 해보겠습니다. 오라클에서 다 출력되야 하는 반대쪽에 (+) 표시를 하므로 사원쪽(emp)에 (+) 표시를 했습니다. |
SELECT dname , COUNT(E.empno) AS emp_cnt FROM emp E, dept D WHERE E.deptno(+) = D.deptno GROUP BY dname |
<실행결과>
dname | empCnt | |
1 | RESEARCH | 5 |
2 | SALES | 6 |
3 | ACCOUNTING | 2 |
4 | OPERATIONS | 0 |
표준 ANSI SQL 구문인 RIGHT OUTER JOIN을 이용하여 다음과 같이 작성할 수 있습니다. |
SELECT dname , COUNT(E.empno) AS emp_cnt FROM emp E RIGHT OUTER JOIN dept D ❶ ON E.deptno = D.deptno ❷ GROUP BY dname |
❶ RIGHT OUTER JOIN을 사용하면서 조인조건을 ❷ ON절에 기술 했습니다.
SELECT dname , COUNT(E.empno) AS emp_cnt FROM emp E RIGHT OUTER JOIN dept D ❶ USING (deptno) ❷ GROUP BY dname |
EMP, DEPT 테이블에 deptno 라는 이름이 같은 컬럼이 있으므로 ❶ RIGHT OUTER JOIN을 쓰면서 조인 조건을 생략하고 ❷ USING문에 deptno 컬럼을 기술 했습니다.
EMP, DEPT 테이블을 조인하여 사원이름과 부서명을 출력하는데 아래 조건을 만족하도록 작성을 해보겠습니다.
즉 조인 조건이 맞지 않더라도 부서도 다 출력해야 하고 사원도 다 출력해야 하는 쿼리를 만들어야 하는데요, 이 경우에는 전체 외부조인(FULL OUTER JOIN)을 사용해야 합니다. |
SELECT ename , dname FROM emp FULL OUTER JOIN dept ON emp.deptno = dept.deptno |
<실행결과>
ename | dname | |
1 | SMITH | RESEARCH |
2 | ALLEN | SALES |
3 | WARD | SALES |
4 | JONES | RESEARCH |
5 | MARTIN | SALES |
6 | BLAKE | SALES |
7 | CLARK | ACCOUNTING |
8 | SCOTT | RESEARCH |
9 | KING | |
10 | TURNER | SALES |
11 | ADAMS | RESEARCH |
12 | JAMES | SALES |
13 | FORD | RESEARCH |
14 | MILLER | ACCOUNTING |
15 | OPERATIONS |
오라클에서 (+) 기호를 양쪽에 기술하는 것은 지원하지 않습니다. |
SELECT ename , dname FROM emp, dept WHERE emp.deptno(+) = dept.deptno(+) |
<실행결과>
ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다
4행, 21열에서 오류 발생
#SQL강좌, #SQL교육, #SQL조인, #외부조인, #OuterJoin, #ORACLE교육, #ORACLE강의, #오라클교육, #SQLJoin, #오라클
